Re: BUG #11524: Unable to add value to ENUM when having AUTOCOMMIT disabled in psql

Hi,

Dne 30 Září 2014, 12:43, feikesteenbergen@gmail.com napsal(a):
> The following bug has been logged on the website:
>
> Bug reference:      11524
> Logged by:          Feike Steenbergen
> Email address:      feikesteenbergen@gmail.com
> PostgreSQL version: 9.4beta2
> Operating system:   Debian
> Description:
>
> If I want to add a value to an ENUM type, I cannot do so when having
> AUTOCOMMIT enabled.
>
> To reproduce:
> \set ECHO queries
> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
> ALTER TYPE mood ADD VALUE 'autocommit enabled';
> \set AUTOCOMMIT OFF
> ALTER TYPE mood ADD VALUE 'autocommit disabled';
>
>
> Output:
> CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
> CREATE TYPE
> ALTER TYPE mood ADD VALUE 'autocommit enabled';
> ALTER TYPE
> ALTER TYPE mood ADD VALUE 'autocommit disabled';
> psql:autocommit_enum.sql:5: ERROR:  ALTER TYPE ... ADD cannot run inside a
> transaction block
>
> I have this issue on 9.1 - 9.5. (This issue does not occur on 9.0 as
> adding
> "ADD VALUE" was added in 9.1).

I don't understand why you think this is a bug? By disabling autocommit,
psql essentially adds a BEGIN before the ALTER TABLE, wrapping it in a
transaction block [1]. And ADD VALUE can't run in a transaction block,
which is a known limitation as explained in [2].

While this is unfortunate and maybe annoying, it's not a bug.

regards
Tomas

[1] http://www.postgresql.org/docs/9.1/static/app-psql.html
[2] http://www.postgresql.org/docs/9.1/static/sql-altertype.html