ALTER TYPE ... ADD VALUE issue

Started by Victor Yegorovover 11 years ago7 messagesgeneral

vyegorov@gmail.com

over 11 years ago

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?

--
Victor Y. Yegorov

Adrian Klaver

adrian.klaver@aklaver.com

over 11 years ago

In reply to: Victor Yegorov (#1)

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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Victor Yegorov

vyegorov@gmail.com

over 11 years ago

In reply to: Adrian Klaver (#2)

Re: ALTER TYPE ... ADD VALUE issue

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.

--
Victor Y. Yegorov

Tom Lane

tgl@sss.pgh.pa.us

over 11 years ago

In reply to: Victor Yegorov (#3)

Re: ALTER TYPE ... ADD VALUE issue

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

over 11 years ago

In reply to: Tom Lane (#4)

Re: ALTER TYPE ... ADD VALUE issue

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

over 11 years ago

In reply to: Tom Lane (#4)

Re: ALTER TYPE ... ADD VALUE issue

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G. Johnston

david.g.johnston@gmail.com

over 11 years ago

In reply to: Adrian Klaver (#5)

Re: ALTER TYPE ... ADD VALUE issue

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:

/messages/by-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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general