Is postgres caching my query?

Question

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?

Expunge

You should probably close your cursor just before the sleep. – Scatology

I tried that with no success which is consistent with the documentation: "Close the cursor now (rather than whenever del is executed). The cursor will be unusable from this point forward; an InterfaceError will be raised if any operation is attempted with the cursor" – Expunge

Answer 1

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()

Preciousprecipice