在transaction中执行drop和add constraint操作报重名错误求助

• PostgreSQL的系统表对约束名的唯一性校验，是基于当前事务的元组快照做判断的：你在事务中删除actor_check约束后，数据库只会给系统表pg_constraint中对应的旧约束记录打上删除标记，不会实际移除这条记录，只有事务提交后才会清理掉标记删除的记录。
• 当你在同一个事务内执行新增同名约束操作时，数据库做约束名校验时，仍然可以查询到这条标记为删除的旧约束记录，因此判定为约束名重复，抛出42710错误。
• 日志中输出的DELETE 0是数据库新增CHECK约束前自动扫描全表校验现有数据是否符合新约束的结果，0代表没有不符合规则的数据，校验本身成功，失败点出现在后续的约束名唯一性校验环节。

根据你的业务场景和数据库版本，可以选择以下任意一种方案：

方案1：拆分为两个独立事务执行 #

适合可以接受短时间内无对应约束校验的场景，先提交删除旧约束的事务，再执行新增约束的操作：

-- 事务1：删除旧约束
BEGIN;
ALTER TABLE movies DROP CONSTRAINT actor_check;
COMMIT;

-- 事务2：新增同名约束
BEGIN;
ALTER TABLE movies ADD CONSTRAINT actor_check CHECK ( "actor" NOT LIKE '%Harpo Marx%'
    AND "actor" NOT LIKE '%Chico Marx%'
    AND "actor" NOT LIKE '%Groucho Marx%'
    AND "actor" NOT LIKE '%Zeppo Marx%');
COMMIT;

注意：两个事务的间隔期如果有写入操作，写入的数据不符合新约束规则的话，第二个事务新增约束时会失败。

方案2：同一个事务内使用不同的约束名 #

如果需要保证事务原子性，全程不中断约束校验，可以直接给新约束设置不同的名称：

BEGIN;
ALTER TABLE movies DROP CONSTRAINT actor_check;
ALTER TABLE movies ADD CONSTRAINT actor_check_v2 CHECK ( "actor" NOT LIKE '%Harpo Marx%'
    AND "actor" NOT LIKE '%Chico Marx%'
    AND "actor" NOT LIKE '%Groucho Marx%'
    AND "actor" NOT LIKE '%Zeppo Marx%');
COMMIT;

如果后续必须使用actor_check作为约束名，可在上述事务提交后，单独执行重命名操作：

ALTER TABLE movies RENAME CONSTRAINT actor_check_v2 TO actor_check;

方案3：直接修改现有约束规则（推荐，仅PostgreSQL 12及以上版本支持） #

PostgreSQL 12及以上版本支持直接修改CHECK约束的表达式，不需要删除后重建，完全规避重名问题，且全程保证约束生效：

BEGIN;
ALTER TABLE movies ALTER CONSTRAINT actor_check CHECK ( "actor" NOT LIKE '%Harpo Marx%'
    AND "actor" NOT LIKE '%Chico Marx%'
    AND "actor" NOT LIKE '%Groucho Marx%'
    AND "actor" NOT LIKE '%Zeppo Marx%');
COMMIT;

内容的提问来源于stack exchange，提问作者Saqib Ali