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

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