ALTER TYPE ... ADD VALUE issue

[Victor Yegorov]

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?

[Adrian Klaver]

Post by Victor Yegorov
Greetings.
\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.

Post by Victor Yegorov
--
Victor Y. Yegorov

[Victor Yegorov]

Post by 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.

[Tom Lane]

Post by Victor Yegorov

Post by 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.

regards, tom lane

[Adrian Klaver]

Post by Tom Lane

Post by Victor Yegorov

Post by 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.

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.

Post by Tom Lane
regards, tom lane

[David G Johnston]

Post by Adrian Klaver

Victor Yegorov <

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

Post by 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/***@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.

[Adrian Klaver]

Post by Tom Lane

Post by Victor Yegorov

Post by 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.

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

Post by Tom Lane
regards, tom lane