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?
PostgreSQL doesn't have a query cache.
However, if you're using SERIALIZABLE isolation, you might be seeing the same snapshot of the data, since you appear to do all your queries within a single transaction.
You should really commit (or rollback) the transaction after each query in your loop. conn.rollback()