CODEWITHSUNDEEP
postgresqlenums
jbet
jbet

Reputation: 492

New enum values must be committed before they can be used

I'm upgrading PostgreSQL from 9.6 to 13. Following script works on 9.6:

BEGIN

CREATE TYPE x AS ENUM ('foo', 'bar');
ALTER TYPE x ADD VALUE 'baz';
SELECT 'baz'::x;

END;

But on 13 ends up with:

ERROR:  unsafe use of new value "baz" of enum type x
LINE 1: SELECT 'baz'::x;
               ^
HINT:  New enum values must be committed before they can be used.

Googling suggests this is earlier than 13, but I don't know which version exactly.

I'm pretty sure I have a good reason to create enum, add value and use this value in the same transaction. Details in the end of the question.

Is there any known clean workaround?

[EDIT] - why I want to do this

I have a set of SQL files

v_0.01.sql
v_0.02.sql
v_0.03.sql
...

that are incremental, i.e. "database version X" is contained in "all SQL files up to X", e.g. version 0.02 is installed with

cat v_0.01.sql v_0.02.sql | psql -1

Some of those files contain CREATE TYPEs, some other ALTER TYPEs. I'll probably add more of those in the future files.

This is convenient, because in any given moment I can easily:

On 9.6, the second dot required some more effort - namely performing any ALTER TYPEs outside a transaction. On 13 this is required also for the first operation and this is inconvenient, because I do this much more often and also there is more-total-SQL, so more ALTER TYPEs.

I'd like to keep:

Upvotes: 10

Views: 9325

Answers (2)

Orachigami
Orachigami

Reputation: 706

Just add COMMIT; after ALTER TYPE line to commit changes in db.

CREATE TYPE x AS ENUM ('foo', 'bar');
ALTER TYPE x ADD VALUE 'baz';
COMMIT;
SELECT 'baz'::x;

If you face this issue using flyway - split alter and select statements to different files.

Upvotes: 11

Bergi
Bergi

Reputation: 665296

Already the Postgres 9.6 docs on this state

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.

It seems there was an undocumented exception to this rule when the enum was created within the same transaction. This never should have worked.

Since Postgres 12, adding new values during a transaction is now supported, but not their usage within the same:

  • Allow enumerated values to be added more flexibly (Andrew Dunstan, Tom Lane, Thomas Munro)

    Previously, ALTER TYPE ... ADD VALUE could not be called in a transaction block, unless it was part of the same transaction that created the enumerated type. Now it can be called in a later transaction, so long as the new enumerated value is not referenced until after it is committed.

(Postgres 12.0 release notes)

It seems that this change to the rules fixed the loophole you were using.

As for workarounds, you might want to use a temporary table to store the enums and create them type only in the very end of your transaction. Have a look at Adding a new value to an existing ENUM Type (which documents pre-ADD VALUE solutions) for inspiration.

Upvotes: 11

Related Questions