Thread: BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having autocommit disabled.
BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having autocommit disabled.
From
feikesteenbergen@gmail.com
Date:
The following bug has been logged on the website:
Bug reference: 10822
Logged by: Feike Steenbergen
Email address: feikesteenbergen@gmail.com
PostgreSQL version: 9.4beta1
Operating system: Debian 3.2.57-3+deb7u2 i686 GNU/Linux
Description:
When having AUTOCOMMIT disabled, issuing an ALTER SYSTEM reports an error.
Enabling AUTOCOMMIT makes the issue disappear.
$ psql feike feikesuper -h localhost -p 5433 --no-psqlrc
psql (9.4beta1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256)
Type "help" for help.
feike=# \set AUTOCOMMIT off
feike=# rollback;
WARNING: there is no transaction in progress
ROLLBACK
feike=# ALTER SYSTEM SET log_min_duration_statement = '5s';
ERROR: ALTER SYSTEM cannot run inside a transaction block
feike=# rollback;
ROLLBACK
feike=# \set AUTOCOMMIT on
feike=# ALTER SYSTEM SET log_min_duration_statement = '5s';
ALTER SYSTEM
The documentation states:
"This command is not allowed inside transaction block or function."
in my understanding, i am not *yet* inside a transaction block when issuing
the ALTER SYSTEM, so I assume it would work when having AUTOCOMMIT enabled,
but then after a comleted transaction.
To me the current behaviour is odd, as VACUUM, which also mentions "VACUUM
cannot be executed inside a transaction block." is able to be executed when
having AUTOCOMMIT disabled:
$ psql feike feikesuper -h localhost -p 5433 --no-psqlrc
psql (9.4beta1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256)
Type "help" for help.
feike=# \set AUTOCOMMIT off
feike=# SELECT 1;
?column?
----------
1
(1 row)
feike=# VACUUM;
ERROR: VACUUM cannot run inside a transaction block
feike=# rollback;
ROLLBACK
feike=# \set AUTOCOMMIT on
feike=# VACUUM;
VACUUM
Re: BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having autocommit disabled.
From
Fujii Masao
Date:
On Tue, Jul 1, 2014 at 6:52 PM, <feikesteenbergen@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 10822 > Logged by: Feike Steenbergen > Email address: feikesteenbergen@gmail.com > PostgreSQL version: 9.4beta1 > Operating system: Debian 3.2.57-3+deb7u2 i686 GNU/Linux > Description: > > When having AUTOCOMMIT disabled, issuing an ALTER SYSTEM reports an error. > Enabling AUTOCOMMIT makes the issue disappear. > > $ psql feike feikesuper -h localhost -p 5433 --no-psqlrc > psql (9.4beta1) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256) > Type "help" for help. > > feike=# \set AUTOCOMMIT off > feike=# rollback; > WARNING: there is no transaction in progress > ROLLBACK > feike=# ALTER SYSTEM SET log_min_duration_statement = '5s'; > ERROR: ALTER SYSTEM cannot run inside a transaction block > feike=# rollback; > ROLLBACK > feike=# \set AUTOCOMMIT on > feike=# ALTER SYSTEM SET log_min_duration_statement = '5s'; > ALTER SYSTEM > > > > The documentation states: > > "This command is not allowed inside transaction block or function." > > in my understanding, i am not *yet* inside a transaction block when issuing > the ALTER SYSTEM, so I assume it would work when having AUTOCOMMIT enabled, > but then after a comleted transaction. > > > > To me the current behaviour is odd, as VACUUM, which also mentions "VACUUM > cannot be executed inside a transaction block." is able to be executed when > having AUTOCOMMIT disabled: > > > $ psql feike feikesuper -h localhost -p 5433 --no-psqlrc > psql (9.4beta1) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256) > Type "help" for help. > > feike=# \set AUTOCOMMIT off > feike=# SELECT 1; > ?column? > ---------- > 1 > (1 row) > > feike=# VACUUM; > ERROR: VACUUM cannot run inside a transaction block > feike=# rollback; > ROLLBACK > feike=# \set AUTOCOMMIT on > feike=# VACUUM; > VACUUM Thanks for the bug report! This problem happens because psql implicitly issues BEGIN command before issuing ALTER SYSTEM command when AUTOCOMMIT is disabled. But as the document about AUTOCOMMIT says as follows, psql should not issue BEGIN in that case. So I think this is the oversight of ALTER SYSTEM feature and we should have changed psql so that it doesn't issue BEGIN when it issues ALTER SYSTEM. Attached patch does this. ---------------------- The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). ---------------------- Regards, -- Fujii Masao
Attachment
Re: BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having autocommit disabled.
From
Fujii Masao
Date:
On Tue, Jul 1, 2014 at 8:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote: > On Tue, Jul 1, 2014 at 6:52 PM, <feikesteenbergen@gmail.com> wrote: >> The following bug has been logged on the website: >> >> Bug reference: 10822 >> Logged by: Feike Steenbergen >> Email address: feikesteenbergen@gmail.com >> PostgreSQL version: 9.4beta1 >> Operating system: Debian 3.2.57-3+deb7u2 i686 GNU/Linux >> Description: >> >> When having AUTOCOMMIT disabled, issuing an ALTER SYSTEM reports an error. >> Enabling AUTOCOMMIT makes the issue disappear. >> >> $ psql feike feikesuper -h localhost -p 5433 --no-psqlrc >> psql (9.4beta1) >> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, >> bits: 256) >> Type "help" for help. >> >> feike=# \set AUTOCOMMIT off >> feike=# rollback; >> WARNING: there is no transaction in progress >> ROLLBACK >> feike=# ALTER SYSTEM SET log_min_duration_statement = '5s'; >> ERROR: ALTER SYSTEM cannot run inside a transaction block >> feike=# rollback; >> ROLLBACK >> feike=# \set AUTOCOMMIT on >> feike=# ALTER SYSTEM SET log_min_duration_statement = '5s'; >> ALTER SYSTEM >> >> >> >> The documentation states: >> >> "This command is not allowed inside transaction block or function." >> >> in my understanding, i am not *yet* inside a transaction block when issuing >> the ALTER SYSTEM, so I assume it would work when having AUTOCOMMIT enabled, >> but then after a comleted transaction. >> >> >> >> To me the current behaviour is odd, as VACUUM, which also mentions "VACUUM >> cannot be executed inside a transaction block." is able to be executed when >> having AUTOCOMMIT disabled: >> >> >> $ psql feike feikesuper -h localhost -p 5433 --no-psqlrc >> psql (9.4beta1) >> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, >> bits: 256) >> Type "help" for help. >> >> feike=# \set AUTOCOMMIT off >> feike=# SELECT 1; >> ?column? >> ---------- >> 1 >> (1 row) >> >> feike=# VACUUM; >> ERROR: VACUUM cannot run inside a transaction block >> feike=# rollback; >> ROLLBACK >> feike=# \set AUTOCOMMIT on >> feike=# VACUUM; >> VACUUM > > Thanks for the bug report! This problem happens because psql implicitly issues > BEGIN command before issuing ALTER SYSTEM command when AUTOCOMMIT > is disabled. But as the document about AUTOCOMMIT says as follows, > psql should not issue BEGIN in that case. So I think this is the oversight of > ALTER SYSTEM feature and we should have changed psql so that it doesn't > issue BEGIN when it issues ALTER SYSTEM. Attached patch does this. Committed! Regards, -- Fujii Masao