Why psycopg2 returns same result for repeated SELECT?

Question

When I execute ordinary Select correct results are returned, but when I execute this select for DB uptime it returns same first result all the time. I did check Postgres logs and I see that select is executed.


    #!/usr/bin/python3

    import psycopg2
    from time import sleep

    conn = psycopg2.connect("dbname='MyDB' user='root' host='127.0.0.1' password='********'")
    cur = conn.cursor()

    def test():
        e = 0
        while e != 100:
            cur.execute("SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;")
            uptv = cur.fetchone()
            print(uptv)
            e += 1
            sleep(0.1)

    test()

Craig Ringer Craig Ringer · Accepted Answer · 2016-12-01T09:07:46

Per the documentation, current_timestamp returns the timestamp at the start of the transaction. This behaviour is required by the SQL standard.

psycopg2 begins a transaction when you run a query. It does not autocommit. So unless you conn.commit(), the same xact is running for the first query and your later iterations.

You should:

conn.commit() after each query (or if you like, conn.rollback() if it's read only and makes no changes); or
Use clock_timestamp() instead of current_timestamp, since the former changes throughout the transaction.

It's best to avoid leaving transactions running anyway, as they can tie up resources the server needs.

Why psycopg2 returns same result for repeated SELECT?

1 Answers