How psycopg2 autocommit works ?

[johntay]

Hi,

I'm working on a "simple" script that makes use of psycopg2.
The script takes some time to finish (not the problem), while I was trying to do some optimizations, I discovered that my queries were marked as "idle" in the pg_stat_activity table.

SELECT
    query,state, state_change
   FROM pg_stat_activity;

+------------------------------+---------+
| query                        | state   |
|------------------------------+---------+
| select * from test           | idle    | 

After a quick search, I found the following on the psycopg website:

If you are writing a long-living program, you should probably make sure to call one of the transaction closing methods before leaving the connection unused for a long time (which may also be a few seconds, depending on the concurrency level in your database). Alternatively you can use a connection in autocommit mode to avoid a new transaction to be started at the first command.

From this, I understand that I can use connection.commit() after each call of the execute() method (that's works fine BTW) or set the connection in autocommit mode. I'm having hard time with the last option.

I wrote a small script to reproduce my problem. It connects to a db, make a simple query and wait for
10s, so I can check the state of the query in pg_stat_activity.

import psycopg2
from time import sleep

con = psycopg2.connect('dbname=test user=test password=test')
#con.autocommit = True
cur = con.cursor()
cur.execute('select * from test')
con.commit()

sleep(10)
print('DONE')

When running with autocommit=False and manual commit, the logs look like this:

LOG:  statement: BEGIN
LOG:  statement: select * from test
LOG:  statement: COMMIT

The query is executed inside a transaction as it should be and the query does not show an idle state.

import psycopg2
from time import sleep

con = psycopg2.connect('dbname=test user=test password=test')
con.autocommit = True
cur = con.cursor()
cur.execute('select * from test')
#con.commit()

sleep(10)
print('DONE')

When running with autocommit=True, , the logs look like this:

LOG:  statement: select * from test

No transaction as expected, but now the query is marked as idle.

To me, this does not match with the psycopg website. Is this how autocommit is supposed to work ? Am I missing something here ?

I know I can force commit or use a context manager, but I'm trying to learn about the autocommit mode.


Regards

[Larz60+]

selects only require commit in very rare cases, like joining database links, where a transaction will be created.
Almost never need a transaction on select, and I would expect that auto-commit will not.

[johntay]

selects only require commit in very rare cases, like joining database links, where a transaction will be created.
Almost never need a transaction on select, and I would expect that auto-commit will not.

Thank you for your answer.

I'm not sure to understand what you mean by "joining database links". A query using joins ?
If so, I made a quick test with joins and also with INSERT/UPDATE/DELETE.
And the results are the same : no DB transaction (which is OK) but queries marked idle.

[Larz60+]

Hmm,

then I wonder if it's because of explicit locking which PostgreSQL allows, doc: https://www.postgresql.org/docs/9.1/expl...cking.html
Perhaps ask the authors, whose email address can be found here: https://pypi.org/project/psycopg2/