必须先提交新的枚举值，然后才能使用它们

Question

我正在将PostgreSQL从9.6升级到13。 以下脚本适用于 9.6：

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

但在 13 上以：

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.

谷歌搜索表明这早于 13 个，但我不知道确切是哪个版本。

我很确定我有充分的理由创建枚举，增加价值并在同一笔交易中使用此值。问题末尾的详细信息。

是否有任何已知的干净解决方法？

[编辑] - 为什么我想这样做

我有一组 SQL 文件

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

是增量的，即"数据库版本 X"包含在"X 以下的所有 SQL 文件"中，例如 0.02 版安装了

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

其中一些文件包含CREATE TYPEs，其他一些ALTER TYPEs。我可能会在将来的文件中添加更多这些内容。

这很方便，因为在任何给定的时刻，我都可以轻松地：

• 安装任何所需版本的全新数据库
• 将数据库从任何版本升级到任何其他版本
• 检查版本之间的差异

在 9.6 上，第二个点需要更多的努力 - 即在事务之外执行任何ALTER TYPE。在 13 上，第一次操作也需要这样做，这很不方便，因为我更频繁地这样做，而且有更多的总 SQL，所以更多的ALTER TYPEs。

我想保留：

• 当前文件结构
• 轻松安装全新数据库
• 单事务安装 (psql -1) - 这样我就不会将正确的安装与失败的安装混淆

Answer 1

已经有关于这种状态的 Postgres 9.6 文档

ALTER TYPE ... ADD VALUE(向枚举类型添加新值的形式)不能在事务块内执行。

在同一事务中创建枚举时，此规则似乎存在未记录的例外。这永远不应该奏效。

从 Postgres 12 开始，现在支持在事务期间添加新值，但不支持它们在事务中的用法：

• 允许更灵活地添加枚举值(Andrew Dunstan、Tom Lane、Thomas Munro)

  以前ALTER TYPE ... ADD VALUE，无法在 事务块，除非它是同一事务的一部分 创建了枚举类型。现在可以在以后调用它 事务，只要不引用新的枚举值 直到提交之后。

^{(Postgres 12.0 发行说明)}

似乎对规则的这种更改修复了您使用的漏洞。

至于解决方法，您可能希望使用临时表来存储枚举，并仅在事务的最后创建枚举类型。查看向现有 ENUM 类型(记录ADD VALUE前解决方案)添加新值以获取灵感。

Answer 2

只需在ALTER TYPE行后添加COMMIT;即可在数据库中提交更改。

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

如果您在使用飞行路线时遇到此问题 - 将更改和选择语句拆分为不同的文件。

Answer 3

在ALTER TYPE后添加一个COMMIT;对我有用。

ALTER TYPE type_name ADD VALUE value; 
COMMIT;