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

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

This bug reminds me of:
BUG #10822: "ALTER SYSTEM cannot run inside a transaction block" when having
autocommit disabled.

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

The documentation states about 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)."

This command cannot be executed with a transaction block, and should
therefore not implicitly start a transaction.
I would expect the same behaviour as for VACUUM, or ALTER SYSTEM. If
AUTOCOMMIT is disabled, these statements can be executed if no transaction
has been started yet.

There seems to be a provision in ./src/bin/psql/common.c for these
statements. The function is command_no_begin.

regards,

Feike Steenbergen

On Tue, Sep 30, 2014 at 9:05 PM, Feike Steenbergen <
feikesteenbergen@gmail.com> wrote:
> There seems to be a provision in ./src/bin/psql/common.c for these
> statements. The function is command_no_begin.
This function skips BEGIN statements for queries that cannot run within a
transaction block by scanning keywords at the beginning of the query
string, and it is true that CREATE TYPE ... ADD VALUE is not added. Now,
skip_white_space is able to skip whitespaces and comments but it is harder
to analyze the type name itself as it could be a combination of the
keywords you are analyzing, for example let's imagine this valid custom
type:
=# CREATE TYPE "create type foo add value" AS (a INT);
CREATE TYPE
=# \dT
                 List of data types
 Schema |            Name             | Description
--------+-----------------------------+-------------
 public | "create type foo add value" |
(1 row)
It is true that this behavior could be improved by having an additional
function able to skip an object name safely for a query string, but I
simply imagine that such additional logic has not been added in psql until
now because of the lack of complains about this behavior on a feature that
has been released 3 years ago.
Regards,
--
Michael