我有一个存储过程,它尝试写入两个通过外键约束关联的表。
稍微简化的表定义:
CREATE TABLE station_event
(
station_code VARCHAR(3) NOT NULL,
user_id INTEGER NOT NULL,
event_dtm TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT station_event_pk
PRIMARY KEY (station_code, user_id, event_dtm)
);
CREATE TABLE location_station_event
(
station_code VARCHAR(3) NOT NULL,
user_id INTEGER NOT NULL,
event_dtm TIMESTAMPTZ(0) NOT NULL DEFAULT now(),
location_code VARCHAR(8) NOT NULL,
location_no INTEGER NOT NULL,
CONSTRAINT location_station_event_pk
PRIMARY KEY (station_code, user_id, event_dtm),
CONSTRAINT location_station_event_station_event_fk
FOREIGN KEY (station_code, user_id, event_dtm)
REFERENCES station_event (station_code, user_id, event_dtm)
);
稍微简化的存储过程定义:
CREATE FUNCTION location_station_apply (
p_site_code VARCHAR,
p_location_no INTEGER,
p_station_code VARCHAR
)
RETURNS VOID AS $$
BEGIN
INSERT INTO station_event (
station_code,
user_id
)
VALUES (
p_station_code,
user_id()
);
INSERT INTO location_station_event (
station_code,
user_id,
location_no
)
VALUES (
p_station_code,
user_id(),
p_location_no
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
我收到的错误是:
错误:表“location_station_event”上的插入或更新违反了外键约束“location_station_event_station_event_fk”详细信息:键(station_code,user_id,event_dtm)=(CE,1,2024-05-24 10:21:56+01)不存在于表“station_event”。
尽管 DEV 运行的是 PostgreSQL v14 而 PROD 仍然运行 v11,但该功能在 DEV 和 PROD 环境之间没有变化。如上所述,它在 DEV 上失败,但在 PROD 上运行成功。
我是否遗漏了一些可能是 PostgreSQL 版本特定的内容?也许有一个新的配置参数(v11 之后引入)?
对表定义和功能进行了微小的更改。找到下面的代码:
使两个表中的 event_dtm 保持一致,但更改了 location_station_event 表中的默认值,因为这导致对 location_station_event 中的微秒进行四舍五入,并且值不匹配,并且违反了外键约束。
其次,对您的函数进行了微小的更改,以使其万无一失,添加了新变量,该变量在插入 station_event 时获取 event_dtm 值,并将该确切值插入 location_station_event
您的
primary key
第一个表是PRIMARY KEY (station_code, user_id, event_dtm)
,您通过第二个表引用它您从未指定它,因此它们都引用默认值。为了解决此问题,请确保将时间戳传递给过程,以便它将为两个插入强制执行相同的值,而不是允许它们出现分歧(这就是问题的原因):
问题的原因是
now()
执行第二次插入时的结果与执行第一次插入时的结果不同。此外,请确保
event_dtm
两者具有相同的类型。最后,请确保user_id()
在两次插入时返回相同的值。因为如果不同,那么您也需要传递一个 user_id 并将其用于两个插入。