6

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:

  • install a fresh database in any desired version
  • upgrade database from any version to any other version
  • check the difference between versions

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:

  • current files structure
  • easy installation of fresh databases
  • single-transaction installations (psql -1) - this way I never confuse correct installation with a failed one
jbet
  • 452
  • 4
  • 12
  • Yes, please elaborate on your use case, as that will allow us to suggest the most sensible workaround. – Bergi Dec 03 '20 at 17:42
  • @Bergi I just did it – jbet Dec 03 '20 at 18:15
  • 1
    So `psql -1f v_0.01.sql && psql -1f v_0.02.sql` is not an option for you? – Bergi Dec 03 '20 at 18:23
  • 1
    This has some downsides - e.g. at some stages it is more convenient to have a single file e.g. `db_upgrade_from_7.01_to_7.43` containing all 43 files - but I will certainly consider this if there's no better way. – jbet Dec 03 '20 at 18:46

3 Answers3

9

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
6

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.

Orachigami
  • 416
  • 3
  • 4
2

Adding a COMMIT; after the ALTER TYPE worked to me.

ALTER TYPE type_name ADD VALUE value; 
COMMIT;
Ramon Seugling
  • 21
  • 2