1

I am trying to update postgresSQL table with psycopg2 (python package) sometimes it is failing with below error.

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Here is the code

from psycopg2 import pool

now  = datetime.now()
logoff_time = datetime(now.year, now.month, now.day, 15, 0, 0)                      
while True:            
    time.sleep(1)
    try:
        status = 'EXECUTED'
        exec_type1 = 'CANCELLED'
        exec_type2 = 'COMPLETED'
        
        try:
            postgreSQL_pool = pool.SimpleConnectionPool(1, 20, host = db_host,
                                        database = db_name,
                                        port = db_port,
                                        user = db_user,
                                        password = db_pwd)
            if postgreSQL_pool:
                print("Connection pool created successfully")
                conn = postgreSQL_pool.getconn()
        except (Exception, psycopg2.DatabaseError) as error:
            print(error)
        sql = """ UPDATE orders SET status = %s, executed_type = %s WHERE order_id = %s"""
        updated_rows = 0
        try:
            cur = conn.cursor()
            cur.execute(sql, (status, exec_type1, order_id,))
            conn.commit()
            updated_rows = cur.rowcount
            cur.close()
            break
        except (Exception, psycopg2.DatabaseError) as error:
            print(error)
        print(updated_rows)
    except Exception as e:
        print(e)

psycopg2 version: '2.8.6 (dt dec pq3 ext lo64)' Postgres: PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

it is pretty much simple task but facing challenges. suggestions please

anilraj
  • 53
  • 7
  • 2
    Check the postgres logs (they are likely to contain info on what went wrong). Moving the creation of the connection pool outside of the loop is likely to help as is calling [putconn](https://www.psycopg.org/docs/pool.html#psycopg2.pool.AbstractConnectionPool.putconn) (you may also want to add a delay between updates). – Brits Oct 08 '21 at 04:28
  • `cur.execute(sql, (status, exec_type, order_id,))` , except there is no `exec_type` variable declared. One of the `try` blocks has no `except`, nor am I seeing what it is protecting against? I see nothing that breaks out of the `While`. You have taken the simple and made it needlessly complex. – Adrian Klaver Oct 08 '21 at 15:27

1 Answers1

0

The server is crashing for some reason that you might be able to read in the server's logs.

piro
  • 13,378
  • 5
  • 34
  • 38