Postgresql "no transaction in progress"

Question

Why am I getting a warning about "no transaction in progress" after a COMMIT even when I have explicitly started a transaction with START TRANSACTION (works the same as BEGIN)? This will occur 90% of the time for this specific transaction, but other times the commit will succeed. I'm using postgres 9.3. Here's the postgres log:

2014-07-04 21:47:19 EST LOG:  statement: START TRANSACTION;
2014-07-04 21:47:19 EST LOG:  statement: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2014-07-04 21:47:19 EST LOG:  statement: SET autocommit = 1;
2014-07-04 21:47:19 EST LOG:  statement: SET TIME ZONE 'UTC'
2014-07-04 21:47:19 EST LOG:  statement: INSERT INTO "Users" (...) VALUES (...) RETURNING *;
2014-07-04 21:47:19 EST LOG:  statement: INSERT INTO "Profiles" (...) RETURNING *;
2014-07-04 21:47:19 EST LOG:  statement: SET TIME ZONE 'UTC'
2014-07-04 21:47:19 EST LOG:  statement: INSERT INTO "Sessions" (...) VALUES (...) RETURNING *;
2014-07-04 21:47:20 EST LOG:  statement: COMMIT;
2014-07-04 21:47:20 EST WARNING:  there is no transaction in progress

Sure they're the same session? Add %p to log_line_prefix and retry. Also, what client library, client app? — Craig Ringer
– Craig Ringer, Commented Jul 4, 2014 at 12:19
The session IDs are different for the START TRANSACTION-COMMIT pair that fails, and they are the same for the times when it works. Could that be the problem? Do the sessions need to be the same? Sorry if that's a dumb question; I don't know how transactions work behind-the-scenes. I'm using a beta version 2.0.0 sequelizejs ORM with node, which could be the problem, but I have been using it for some time without any issues and then this started happening randomly. — Decade Moon
– Decade Moon, Commented Jul 4, 2014 at 12:35

charriu · Accepted Answer · 2014-07-04 13:47:01Z

1

You are using autocommit. When using autocommit, explicitly starting a transaction is meaningless.

Remove the line "set autocommit = 1" or change the 1 to "off" or an equivalent value and this should work.

answered Jul 4, 2014 at 13:47

charriu

512 bronze badges

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

4 Comments

user330315 Over a year ago

"When using autocommit, explicitly starting a transaction is meaningless." - that is not true.

Craig Ringer Over a year ago

@a_horse_with_no_name Depends on the autocommit implementation in the driver. In many of them, it'd be the same as BEGIN; COMMIT; - or BEGIN; BEGIN; COMMIT;.

charriu Over a year ago

a_horse_with_no_name: Quote postgres documentation on autocommit: "This command can change the session to autocommit mode, where each individual statement is committed implicitly." This means, that each statement ist basically the same as BEGIN; statement; COMMIT; Thus, adding extra BEGIN; COMMIT; commands doesn't change anything.

Decade Moon Over a year ago

The postgres docs says: '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)'. If I'm using BEGIN then autocommit doesn't mean anything since I'm explicitly telling postgres the begin and end of the transaction, right?

Collectives™ on Stack Overflow

Postgresql "no transaction in progress"

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related