CODEWITHSUNDEEP
sqlpostgresqlpsycopg2
brendanpic
brendanpic

Reputation: 13

How to refresh postgresql query without reconnecting?

I have the following script for retrieving the number of connections to a postgresql database:

import psycopg2, time

db_ip = "192.168.1.137"
db_port = "5432"
db_name = "postgres"
db_username = "postgres"
db_pw = "pass"

db = psycopg2.connect("host=%s port=%s dbname=%s user=%s password=%s" % (db_ip, db_port, db_name, db_username, db_pw))
cur = db.cursor()

while True:
    #~ db = psycopg2.connect("host=%s port=%s dbname=%s user=%s password=%s" % (db_ip, db_port, db_name, db_username, db_pw))
    #~ cur = db.cursor()
    cur.execute("SELECT count(*) FROM pg_stat_activity;")
    current_connections = cur.fetchall()
    print current_connections
    #~ cur.close()
    #~ db.close()
    time.sleep(1)

cur.close()
db.close()

The number of actual connections is fluctuating, but the script will only return the initial value repeatedly, unless I uncomment the lines which reestablish the connection on on every iteration of the while loop.

Is this by design, or am I missing something? Ideally I'd prefer to make the connection once, and then simply run queries.

Thanks in advance for any guidance.

Upvotes: 1

Views: 2428

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156238

yes. this is by design and you are missing something. postgres tries to present the illusion that queries are handled sequentially; as though they were all happening one at a time. this is so that concurrent applications done need to worry about seeing partially updated data from other processes. you don't want to see the account balance after the money has been taken out by before its been applied to the payee. this is known as transaction isolation.

the part that you are missing is that you need to tell postgres that you are done with one transaction and would like to start a new one. you can do this by issuing a commit on your cursor, or closing the cursor and fetching a new one from the existing connection

Upvotes: 1

Related Questions