Re: BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having autocommit disabled.

On Tue, Jul 1, 2014 at 6:52 PM, <feikesteenbergen(at)gmail(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 10822
> Logged by: Feike Steenbergen
> Email address: feikesteenbergen(at)gmail(dot)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