CODEWITHSUNDEEP
postgresqlpsqlautocommit
Kjetil S.
Kjetil S.

Reputation: 3795

Autocommit off, BEGIN and warning about transaction in progress

I like to have this in my ~/.psqlrc:

\set autocommit off

I also like this at the top of my filename.sql scripts:

BEGIN;

...in case those scripts are run by others who uses autocommit.

However, this emits warnings:

WARNING:  there is already a transaction in progress

How can I turn off those annoying warnings? Is there some sort of BEGIN IF NOT ALREADY BEGUN command?

Upvotes: 2

Views: 3193

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248305

Your two settings contradict each other.

If you enable autocommit mode in psql, your scripts should look like this:

-- statement 1
-- statement 2
...
COMMIT;
-- statement 3
...
COMMIT;

That is because autocommit mode will automatically add a BEGIN before the first statement and before eache statement after a COMMIT or ROLLBACK. That is because PostgreSQL does not have an autocommit mode, so psql is “faking it”.

Now if your script starts with a BEGIN, that will result in two BEGINs right after each other, which will result in the observed warning.

Maybe it would be best to start your scripts with

\set autocommit on

to make sure everything is consistent.

Upvotes: 6

Related Questions