What does autocommit mean in postgresql and psycopg2?

6
votes

Does autocommit mean the same in PostgreSQL and Psycopg2?

From PostgreSQL manual

By default (without BEGIN ), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Does it mean that autocommit will create a transaction for each command?

From Psycopg2 driver's manual

It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible. A few commands (e.g. CREATE DATABASE, VACUUM…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

and

psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT

No transaction is started when commands are executed and no commit() or rollback() is required. Some PostgreSQL command such as CREATE DATABASE or VACUUM can’t run into a transaction: to run such command use:

>>> conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

Does "all the commands executed will be immediately committed" mean that autocommit in Psycopg2 creates a transaction for each command?

Does "No transaction is started when commands are executed and no commit() or rollback() is required" mean that autocommit in Psycopg2 will prevent a transaction created for each command?

Does "Some PostgreSQL command such as CREATE DATABASE or VACUUM can’t run into a transaction: to run such command, enable autocommit mode" mean that autocommit in Psycopg2 will prevent a transaction created only for some commands (CREATE DATABASE or VACUUM)?

Thanks.

1
postgresqlpsycopg2

1 Answers

7
votes

Every PostgreSQL statement is running in a transaction.

PostgreSQL itself only knows autocommit mode, which means that each statement will run in its own transaction if you don't start a transaction explicitly.

Statements like VACUUM cannot run in the same transaction with other statements.

If you are not using autocommit in psycopg2, the driver has to simulate non-autocommit mode by explicitly starting a transaction when the first statement is run.