PostgreSQL迁移serial主键为UUID时新增UUID列全为NULL问题
异常根因
- 新增UUID列全为NULL的核心触发点是操作顺序搭配PostgreSQL 11的快速默认值(Fast Default)机制的已知兼容问题:你在给customer表加新UUID列之前,先将原主键id列重命名为oldId,这个操作修改了系统目录中表的列元数据关联关系,导致后续加带默认值的列时,PostgreSQL 11没有正确触发“重写表逐行填充默认值”的逻辑——原本针对volatile函数(比如
uuid_generate_v4(),每次调用结果不同)的加列操作,是必须逐行计算填充的,但因为列重命名的前置操作,填充逻辑被跳过了,现有行的新列没有实际写入值。同时元数据层面已经标记not null、unique约束生效,加列阶段没有触发表扫描校验,最终出现约束显示正常、实际列值全为NULL的异常。 - 关于你误以为事务中
uuid_generate_v4()会返回相同值的误解可以直接澄清:uuid_generate_v4()是标准VOLATILE级别的函数,PostgreSQL优化器绝不会对这类函数做一次计算、全表复用的缓存优化,在执行UPDATE语句时会对每一行单独调用函数,返回完全独立的UUID,不存在全表值重复的问题,完全可以用来补全列值。
带ON DELETE SET NULL外键场景的正确迁移实现
调整操作顺序规避前置重命名列触发的元数据bug,全程在单个事务中执行保证原子性,步骤如下:
- 启用uuid扩展,开启事务
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";BEGIN;
- 先给customer表新增临时UUID列,这一步没有前置列重命名操作,会正常逐行填充唯一UUID值
ALTER TABLE "customer" ADD COLUMN "id_temp" UUID NOT NULL DEFAULT uuid_generate_v4();- 执行完可以简单校验:
SELECT count(*) AS total, count(DISTINCT "id_temp") AS unique_count FROM "customer";两个值相等说明填充正常。
- 将customer表原integer类型的主键id列重命名为oldId,避免和新UUID列命名冲突
ALTER TABLE "customer" RENAME COLUMN "id" TO "oldId";
- 移除queueCustomer表上绑定旧integer id的原有外键约束(后续会删除旧关联列,必须先拆约束)
- 先查询确认原有外键名:
SELECT conname FROM pg_constraint WHERE conrelid = '"queueCustomer"'::regclass AND contype = 'f'; - 执行删除外键:
ALTER TABLE "queueCustomer" DROP CONSTRAINT "<查询到的外键约束名>";
- 先查询确认原有外键名:
- 将queueCustomer表原integer类型的customerId列重命名为oldCustomerId,为新UUID列腾出命名空间
ALTER TABLE "queueCustomer" RENAME COLUMN "customerId" TO "oldCustomerId";
- 给queueCustomer表新增UUID类型的customerId列
ALTER TABLE "queueCustomer" ADD COLUMN "customerId" UUID;
- 通过旧列的关联关系,将customer表的新UUID值同步到queueCustomer表
UPDATE "queueCustomer" q SET "customerId" = c."id_temp" FROM "customer" c WHERE c."oldId" = q."oldCustomerId";
- 切换customer表的主键:删除原旧列上的主键约束,将临时UUID列改为正式id列,绑定主键
ALTER TABLE "customer" DROP CONSTRAINT "customer_pkey";ALTER TABLE "customer" RENAME COLUMN "id_temp" TO "id";ALTER TABLE "customer" ADD PRIMARY KEY ("id");
- 给queueCustomer表的新UUID customerId列补回外键约束,完全保留原有的
ON DELETE SET NULL语义ALTER TABLE "queueCustomer" ADD CONSTRAINT "<新外键约束名>" FOREIGN KEY ("customerId") REFERENCES "customer"("id") ON DELETE SET NULL;
- 清理两张表上的旧临时列
ALTER TABLE "customer" DROP COLUMN "oldId";ALTER TABLE "queueCustomer" DROP COLUMN "oldCustomerId";
- 确认所有校验通过后提交事务
COMMIT;
注意事项:迁移过程中如果表数据量较大,加临时列的步骤因为要逐行填值,会持有表级锁,尽量在业务低峰期执行。
内容的提问来源于stack exchange,提问作者Ivan Rubinson
相关产品推荐
相关产品推荐

