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(dot)fujii(at)gmail(dot)com> wrote:
> 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.

Committed!

Regards,

--
Fujii Masao