Surprising behaviour of \set AUTOCOMMIT ON

From: Rahila Syed <rahilasyed90(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Surprising behaviour of \set AUTOCOMMIT ON
Date: 2016-08-03 10:16:28
Message-ID: CAH2L28sTP-9dio3X1AaZRyWb0-ANAx6BDBi37TGmvw1hBiu0oA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

Need community's opinion on following behaviour of \set AUTOCOMMIT

If \set AUTOCOMMIT ON is issued after \set AUTOCOMMIT OFF the commands
which follow after AUTOCOMMIT is set ON are not committed until an explicit
COMMIT is issued.
Its can be surprising to the user to not see results of the commands fired
after AUTOCOMMIT is set to ON.

bash-4.2$ psql -d postgres -U rahila
psql (9.6beta3)
Type "help" for help.

postgres=# \set AUTOCOMMIT OFF
postgres=# create table test1(i int);
CREATE TABLE
postgres=# \set AUTOCOMMIT ON
postgres=# create table test2(j int);
CREATE TABLE
postgres=# \c postgres rahila
You are now connected to database "postgres" as user "rahila".
postgres=# \dt;
No relations found.

The ongoing transaction is left running when there is this change in mode
from AUTOCOMMIT OFF to AUTOCOMMIT ON.
This happens because \set AUTOCOMMIT ON is fired within a transaction block
started when first command after \set AUTOCOMMIT OFF is executed. Hence it
requires an explicit COMMIT to be effective.

Should changing the value from OFF to ON automatically either commit or
rollback transaction in progress?
FWIW, running set autocommit through ecpg commits the ongoing transaction
when autocommit is set to ON from OFF. Should such behaviour be implemented
for \set AUTOCOMMIT ON as well?

Thank you,
Rahila Syed

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Stark 2016-08-03 10:29:39 Re: Why we lost Uber as a user
Previous Message Ashutosh Sharma 2016-08-03 09:52:18 OldSnapshotTimemapLock information is missing in monitoring.sgml file