向两个关联表插入数据的存储过程触发外键约束失败

我有一个存储过程，尝试向两个通过外键约束关联的表写入数据。

表定义 #

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;

错误信息 #

ERROR: insert or update on table "location_station_event" violates foreign key constraint "location_station_event_station_event_fk"
DETAIL: Key (station_code, user_id, event_dtm)=(CE, 1, 2024-05-24 10:21:56+01) is not present in table "station_event".

该存储过程在DEV（PostgreSQL 14）中失败，在PROD（PostgreSQL 11）中可正常运行，询问是否存在PostgreSQL版本相关的因素。

核心原因 #

问题出在两个表event_dtm字段的精度不一致，以及PostgreSQL 12+版本对类型匹配的严格性变更：

• station_event的event_dtm是TIMESTAMPTZ（默认6位精度，微秒级），而location_station_event的event_dtm是TIMESTAMPTZ(0)（秒级精度，自动截断微秒）。
• PostgreSQL 11及更早版本会隐式忽略TIMESTAMPTZ的精度差异，允许外键匹配；但PostgreSQL 12+（含14）对外键字段的类型匹配要求更严格，精度不同的TIMESTAMPTZ会被视为不兼容，导致插入从表时，截断后的秒级时间无法匹配主表中存储的微秒级时间，触发约束错误。
• 另外，存储过程中两次调用now()可能返回不同的时间值（微秒级差异），也会加剧这个问题。

解决方案 #

方案1：统一字段精度 #

将两个表的event_dtm字段精度调整为一致，比如把主表的字段改为秒级精度：

ALTER TABLE station_event ALTER COLUMN event_dtm TYPE TIMESTAMPTZ(0);

或者将从表的字段改为默认的微秒级精度：

ALTER TABLE location_station_event ALTER COLUMN event_dtm TYPE TIMESTAMPTZ;

方案2：复用生成的时间值（推荐） #

在插入主表时获取自动生成的event_dtm，再复用该值插入从表，既避免精度差异，也解决两次now()调用的时间不一致问题：

CREATE FUNCTION location_station_apply (
    p_site_code         VARCHAR,
    p_location_no       INTEGER,
    p_station_code      VARCHAR
)
RETURNS VOID AS $$
DECLARE
    v_event_dtm TIMESTAMPTZ;
BEGIN
    -- 插入主表并获取生成的event_dtm
    INSERT INTO station_event (station_code, user_id)
    VALUES (p_station_code, user_id())
    RETURNING event_dtm INTO v_event_dtm;

    -- 复用时间值插入从表
    INSERT INTO location_station_event (station_code, user_id, event_dtm, location_no)
    VALUES (p_station_code, user_id(), v_event_dtm, p_location_no);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

方案3：调整兼容配置（不推荐） #

如果无法修改表结构或存储过程，可尝试设置integer_datetimes等兼容参数，但这种方式可能引入其他兼容性问题，仅作为临时应急方案。

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