Thread: ALTER TYPE ... ADD VALUE issue
Greetings.
--
Victor Y. Yegorov
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
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
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
Victor Y. Yegorov
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
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
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
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: http://www.postgresql.org/message-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.