如何在不锁定表的情况下向Postgres的ENUM添加新值？

Question

我尝试了两种方法。

第一种方法：创建一个新的枚举并添加新值，然后直接在原位切换数据类型：

-- Rename existing enum
ALTER TYPE animal_species RENAME TO animal_species_old;

-- Create new enum with new value
CREATE TYPE animal_species AS ENUM (
  'dog',
  'cat',
  'elephant'
);

-- Update the column of Animals to use the new enum
ALTER TABLE "Animals" ALTER COLUMN species SET DATA TYPE animal_species USING species::text::animal_species;

DROP TYPE animal_species_old;

方法二：使用一个临时列

-- Create new enum type with a new name (this will be the name of the enum from now on)
CREATE TYPE animal_type_enum AS ENUM (
  'dog',
  'cat',
  'elephant'
);

-- Create a temporary column
ALTER TABLE "Animals" ADD COLUMN species_new animal_species_enum;

-- Copy existing species into new column
UPDATE "Animals" SET species_new = species::text::animal_species_enum;

-- Drop old species column
ALTER TABLE "Animals" DROP COLUMN species;

-- Rename new column
ALTER TABLE "Animals" RENAME COLUMN species_new TO species;

-- Drop old enum
DROP TYPE animal_species;

两种情况下，锁定(s)被创建并使我的应用程序崩溃。我认为第二种方法比第一种表现更好，但停机时间仍然无法接受。该表有数百万行。

请注意，我非常愿意使用除ENUM之外的其他东西--我正在考虑创建一个"物种(Species)"表，并在"动物(Animals)"中添加一个外键"species_id"，但据我所知，这将创建相同的锁定问题（并且由于引入了新的外键约束，可能会更糟）。

感谢任何帮助！

- Jeff

Answer 1

第三种方法，只需将新值添加到枚举中：

ALTER TYPE animal_type_enum ADD VALUE 'snake';

如果您经常添加或删除新的查找值，单独使用一个查找表是更好的选择。

添加新值是一个简单的INSERT操作，不会锁定任何内容（特别是不会锁定引用查找表的表）。

尽管外键检查会增加一些开销，但它们不应该很重要（假设FK列已经正确索引），除非您非常频繁地进行批量INSERT或DELETE操作。

对于单行INSERT或DELETE（或仅有“数百”行），您甚至可能不会注意到FK查找的开销——特别是如果查找表很小且只包含少量行。

- user330315

---

太好了，谢谢——看起来这是在PG 9.1中添加的。我根本没有想到他们会添加这样的功能！仍然没有办法从ENUM中删除条目（我可以看出这会导致问题），所以我的回滚迁移仍然是临时的枚举荒谬，但我认为这没关系。 - Jeff

Answer 2

如果您担心出现重复值的风险，上面的示例可以使用IF NOT EXISTS来提供一些保护。

ALTER TYPE animal_type_enum ADD VALUE IF NOT EXISTS 'snake';

你将会得到一个通知而不是错误。

- Tel