时间戳

首页 > 解决方案 > 通过 Flyway SQL 脚本为 PostgreSQL 中新创建的列设置不为空

sql - 通过 Flyway SQL 脚本为 PostgreSQL 中新创建的列设置不为空

问题描述

我有一个 Flyway 迁移脚本（在我们的 Spring Boot 应用程序中）：

alter table bar
    add column foo_id int,
    add constraint fk_bar_foo_id foreign key (foo_id)
        references foo (id);

update bar
set foo_id = foo.id
from foo
where bar.foo_pid = foo.pid;

alter table bar
    alter column foo_id set not null;

它基本上是：添加一个新列，使其成为另一个表的 FK，使用来自另一个表的现有数据填充它，并设置非空约束。当我启动由开发数据库支持的应用程序时，一切都按预期工作。但是，应用程序在测试期间无法启动：

Migration V210111__foo_id.sql failed
----------------------------------------------------------
SQL State  : 23502
Error Code : 0
Message    : ERROR: column "foo_id" contains null values
Location   : db/migration/V210111__foo_id.sql
Line       : 11
Statement  : alter table bar
    alter column foo_id set not null

当我评论第 11 行（和第 12 行）时，脚本成功，当我查看测试数据库（嵌入式 postgres）时，我看到记录确实有foo_id = null：

# foo table
id  pid
0   00000000-0000-0000-0000-000000000000

# bar table
id  foo_id  foo_pid
0   <null>  0000000-0000-0000-0000-000000000000

笔记：

  1. 迁移脚本适用于某些数据库，但不适用于其他数据库。分不清哪个是哪个！
  2. 没有丢失的数据。我可以在测试暂停时自己运行脚本的更新部分，一切正常。只是 Flyway 无法运行迁移。
  3. 同样，任何bar行都不可能以 null 结尾foo_id，因为，foo.id是表的 PK foo，foo.pid是表的 PK foo（并且仍然是not null）。也是foo_pid和not null一个 FKfoo表。
  4. 在此之前有很多迁移，添加新列，删除现有列，更改 PK 等。

我不确定可能是什么问题。也许它与 Flyway 运行迁移脚本的方式有关？原因是当我对开发数据库运行迁移时，只有一个迁移脚本可以执行（这意味着数据库已经拥有一切，包括foo.idand foo.pid）。但是对于测试，Flyway 必须运行所有脚本（因为它是一个新的空数据库）。但据我所知，Flyway 在一个事务中运行每个脚本，因此在执行下一个脚本时数据应该是最终的。

有谁知道问题可能是什么？有没有更多的解决方法？

更新 1

同样，任何bar行都不可能以 null 结尾...

相信 PG 而不是我的话是公平的（即使我多次查看了架构）。为了消除对 null 值可能性的任何疑问，我将脚本的更新部分更改为：

--update bar
--set foo_id = foo.id
--from foo
--where bar.foo_pid = foo.pid;

update bar
set foo_id = 0;

它仍然失败并出现同样的错误！

更新 2

有一个我认为不相关的触发器：

CREATE OR REPLACE FUNCTION public.update_modified_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    if row (NEW.*) is distinct from row (OLD.*) then
        NEW.modified_at = now();
        return NEW;
    else
        return OLD;
    end if;
end;
$function$
;

每个表都有一modified_at列：

create trigger tgr_update_modified_at before
update on bar for each row execute function update_modified_at();

标签： sqlpostgresqlspring-bootflyway

解决方案

这部分可能不会做任何事情：

update bar
set foo_id = foo.id
from foo
where bar.foo_pid = foo.pid;

因为您刚刚在表格栏中添加了 foo_pid 列。该条款

where bar.foo_pid = foo.pid;

将 null 与 foo.pid 进行比较总是失败，并且该语句不会更新任何内容。

推荐阅读