What does Postgres do when BEGIN is run on a connection in autocommit mode?

Question

I'm trying to better understand the concept of 'autocommit' when working with a Postgres (psycopg) connection. Let's say I have a fresh connection, set its isolation level to ISOLATION_LEVEL_AUTOCOMMIT, then run this SQL directly, without using the cursor begin/rollback methods (as an exercise; not saying I actually want to do this):

INSERT A
INSERT B
BEGIN
    INSERT C
    INSERT D
ROLLBACK

What happens to INSERTs C & D?

Is autocommit is purely an internal setting in psycopg that affects how it issues BEGINs? In that case, the above SQL is unafected; INSERTs A & B are committed as soon as they're done, while C & D are run in a transaction and rolled back. What isolation level is that transaction run under?

Or is autocommit a real setting on the connection itself? In that case, how does it affect the handling of BEGIN? Is it ignored, or does it override the autocommit setting to actually start a transaction? What isolation level is that transaction run under?

Or am I completely off-target?

http://www.postgresql.org/docs/8.4/static/sql-begin.html – a'r Mar 19 '10 at 16:06 — a'r, Mar 19 '10 at 16:06

Quassnoi · Accepted Answer · 2010-03-19T17:05:04.377

3

Autocommit mode means that each statement implicitly begins and ends the transaction.

In your case, if autocommit is off:

The client will implicitly start the transaction for the first statement
The BEGIN will issue a warning saying that the transaction is already started
The ROLLBACK will roll back all four statements

When autocommit is on, only the c and d are rolled back.

Note that PostgreSQL has no internal AUTOCOMMIT behavior since 8.0: all autocommit features are relied upon the clients.

edited Mar 19 '10 at 17:05

answered Mar 19 '10 at 15:43

Quassnoi

413,100
91
616
614

I'm asking about the behavior when autocommit is on. – DNS Mar 19 '10 at 16:31
@DNS: Take a look at the Postgres documentation (linked to a comment on the question above). It very clearly states the behavior when autocommit is on. – a'r Mar 19 '10 at 17:19
1

@ar: It doesn't; it explains what happens when you execute, i.e. an INSERT all by itself, *without* BEGIN. That's straightforward. My question asks about the behavior of a psycopg connection, set to autocommit mode, when executing something *within* a BEGIN + ROLLBACK/COMMIT block. – DNS Mar 19 '10 at 17:47
@Quassnoi: just noticed your edit regarding autocommit being a client feature since 8.0. I'll leave this open a bit in case other answers can add more, but that's what I was looking for. – DNS Mar 19 '10 at 17:50

score 1 · Answer 2 · answered Mar 20 '10 at 22:12

By default, PostgreSQL has autocommit on, meaning that each statement is handled as a transaction. If you explicitly tell it to start a transaction, as in your example, those items are in a new transaction.

In your example, A and B would be committed, C and D would be rolled back.

score 1 · Answer 3 · answered Dec 15 '10 at 17:00

When autocommit is on psycopg just sends everything to the PostgreSQL backend without trying to manage the transaction for you. If you don't use BEGIN/COMMIT/ROLLBACK then every .execute() call is immediately executed and committed. You can do your own transaction management by issuing BEGIN/COMMIT/ROLLBACK commands. Obviously in autocommit mode you can't call conn.commit() or conn.rollback() because psycopg is not keeping track of the transactions but just sending anything you .execute() straight to the backend.

In your example A and B would be committed, C and D would be rolled back.

What does Postgres do when BEGIN is run on a connection in autocommit mode?

3 Answers3