Re: 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(dot)klaver(at)aklaver(dot)com