I have a pretty simple snippet of Python code to run a Postgres query then send the results to a dashboard. I'm using psycopg2
to periodically run the same query. Let's not worry about the looping mechanism for now.
conn = psycopg2.connect(<connection info>)
while True:
# Run query and update dashboard
cur = conn.cursor()
cur.execute(q_tcc)
query_results = cur.fetchall()
update_dashboard(query_results)
time.sleep(5)
For reference, the actual query is :
q_tcc = """SELECT client_addr, application_name, count(*) cnt FROM pg_stat_activity
GROUP BY client_addr, application_name ORDER BY cnt DESC;"""
When I run this, I keep getting the same results even though they should be changing. If i move the psycopg2.connect()
line into the loop with a conn.close()
, everything works fine. According to the connection and cursor docs, however, I should be able to keep using the same cursor (and, therefore, connection) the whole time.
Does this mean Postgres is caching my query on a per-client-connection basis?
close
your cursor just before thesleep
. – Scatology