Обсуждение: ALTER TYPE ... ADD VALUE issue

On 10/20/2014 11:30 AM, Victor Yegorov wrote:
> Greetings.
>
>
> I'm observing the following on 9.3.5 and also on 9.4beta3:
>
> \set AUTOCOMMIT on
> CREATE TYPE enum_type AS ENUM ('bad', 'good');
> CREATE TYPE
> ALTER TYPE enum_type ADD VALUE 'so-so' AFTER 'bad';
> ALTER TYPE
> DROP TYPE enum_type;
> DROP TYPE;
> \set AUTOCOMMIT off
> CREATE TYPE enum_type AS ENUM ('bad', 'good');
> CREATE TYPE
> COMMIT;
> COMMIT
> ALTER TYPE enum_type ADD VALUE 'so-so' AFTER 'bad';
> ERROR:  ALTER TYPE ... ADD cannot run inside a transaction block
>
>
> What is wrong here?

http://www.postgresql.org/docs/9.3/interactive/sql-altertype.html

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum
type) cannot be executed inside a transaction block.

With \set AUTOCOMMIT off the COMMIT ended one transaction block and
started another. You then ran the ALTER TYPE .. ADD in the new block
which is not allowed.

>
>
> --
> Victor Y. Yegorov


--
Adrian Klaver
adrian.klaver@aklaver.com

Victor Yegorov <vyegorov@gmail.com> writes:
> 2014-10-20 21:43 GMT+03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>> With \set AUTOCOMMIT off the COMMIT ended one transaction block and
>> started another.

> I don't think `COMMIT` starts a new transaction block here,
> as I can run `VACUUM` after it, and vacuum also cannot be run inside
> transaction block.

psql knows not to issue BEGIN before a VACUUM command.  It doesn't
know that about ALTER TYPE ... ADD VALUE.

            regards, tom lane

On 10/20/2014 12:03 PM, Tom Lane wrote:
> Victor Yegorov <vyegorov@gmail.com> writes:
>> 2014-10-20 21:43 GMT+03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>>> With \set AUTOCOMMIT off the COMMIT ended one transaction block and
>>> started another.
>
>> I don't think `COMMIT` starts a new transaction block here,
>> as I can run `VACUUM` after it, and vacuum also cannot be run inside
>> transaction block.
>
> psql knows not to issue BEGIN before a VACUUM command.  It doesn't
> know that about ALTER TYPE ... ADD VALUE.


I did some testing with Victors examples and I came away confused(:


Tested on:


test=> select version();
                                                            version


-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit


test=> \set AUTOCOMMIT on;
unrecognized Boolean value; assuming "on"
test=> \set
AUTOCOMMIT = 'on;'

test=> \set AUTOCOMMIT off;
unrecognized Boolean value; assuming "on"
test=> \set
AUTOCOMMIT = 'off;'

Not sure how assuming on becomes off?
This is the same if I quote the values.


http://www.postgresql.org/docs/9.3/interactive/app-psql.html#APP-PSQL-VARIABLES

AUTOCOMMIT

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).

The above would seem to imply it should work, in contrast to what I
originally said.


>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/20/2014 12:03 PM, Tom Lane wrote:
> Victor Yegorov <vyegorov@gmail.com> writes:
>> 2014-10-20 21:43 GMT+03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>>> With \set AUTOCOMMIT off the COMMIT ended one transaction block and
>>> started another.
>
>> I don't think `COMMIT` starts a new transaction block here,
>> as I can run `VACUUM` after it, and vacuum also cannot be run inside
>> transaction block.
>
> psql knows not to issue BEGIN before a VACUUM command.  It doesn't
> know that about ALTER TYPE ... ADD VALUE.

Forget about the \set confusion, just realized I was ending the \set
with an ; which was causing the message.

>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver-4 wrote
> On 10/20/2014 12:03 PM, Tom Lane wrote:
>> Victor Yegorov <

> vyegorov@

> > writes:
>>> 2014-10-20 21:43 GMT+03:00 Adrian Klaver <

> adrian.klaver@

> >:
>>>> With \set AUTOCOMMIT off the COMMIT ended one transaction block and
>>>> started another.
>>
>>> I don't think `COMMIT` starts a new transaction block here,
>>> as I can run `VACUUM` after it, and vacuum also cannot be run inside
>>> transaction block.
>>
>> psql knows not to issue BEGIN before a VACUUM command.  It doesn't
>> know that about ALTER TYPE ... ADD VALUE.
>
> http://www.postgresql.org/docs/9.3/interactive/app-psql.html#APP-PSQL-VARIABLES
>
> AUTOCOMMIT
>
> 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).
>
> The above would seem to imply it should work, in contrast to what I
> originally said.

This complaint already exists as a bug report:

http://www.postgresql.org/message-id/20140930104330.7639.71922@wrigleys.postgresql.org

I would concur that this is an oversight worth correcting going forward; and
I cannot see how it would hurt to back-patch if the fix is reasonably
non-invasive.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/ALTER-TYPE-ADD-VALUE-issue-tp5823696p5823725.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.