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

is autocommit off?..

Vao Tsun
– Vao Tsun

2016-12-01 09:07:38 +00:00
Commented Dec 1, 2016 at 9:07 — Vao Tsun
– Vao Tsun, Commented Dec 1, 2016 at 9:07

Craig Ringer · Accepted Answer · 2016-12-01 09:30:02Z

8

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.

edited Dec 1, 2016 at 9:30

answered Dec 1, 2016 at 9:07

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why psycopg2 returns same result for repeated SELECT?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related