PostgreSQL复合外键插入违反约束问题排查求助

问题描述 #

向mdata表插入数据时触发外键约束报错：

ERROR:  insert or update on table "mdata" violates foreign key constraint "FK_mdata"
DETAIL:  Key (time, measurement_id)=(2022-07-18 12:35:03.31052, 1) is not present in table "measurement".
SQL state: 23503 

确认要插入的measurement_id=1和time='2022-07-18 12:35:03.31052'在measurement表中存在，但插入操作仍失败。

表结构 #

mdata表：

CREATE TABLE IF NOT EXISTS public.mdata
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    value jsonb NOT NULL,
    measurement_id bigint NOT NULL,
    "time" timestamp without time zone NOT NULL,
    CONSTRAINT "PK_medata" PRIMARY KEY (id),
    CONSTRAINT "FK_mdata" FOREIGN KEY ("time", measurement_id)
        REFERENCES public.measurement ("time", id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

measurement表：

CREATE TABLE IF NOT EXISTS public.measurement
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "time" timestamp without time zone NOT NULL,
    value real NOT NULL,
    CONSTRAINT "PK_measurement" PRIMARY KEY ("time", id)
)

插入语句 #

INSERT INTO public.mdata(
id, value, measurement_id, "time")
VALUES (8, '{}',1 , '2022-07-18 12:35:03.31052');

排查提示 #

• 检查时间精度匹配：PostgreSQL的timestamp without time zone会保留微秒级精度，确认measurement表中对应记录的time字段是否和插入值的精度完全一致。比如插入值是2022-07-18 12:35:03.31052（5位小数），如果表中存储的是2022-07-18 12:35:03.310520（6位小数），会被判定为不匹配。可以用以下语句精确查询：

  SELECT * FROM public.measurement WHERE id = 1 AND "time" = '2022-07-18 12:35:03.31052'::timestamp without time zone;
  

  如果无结果，尝试用EXTRACT(microsecond FROM "time")查看具体微秒值。
• 验证复合主键的存在性：直接查询measurement表中是否存在目标组合：

  SELECT COUNT(*) FROM public.measurement WHERE "time" = '2022-07-18 12:35:03.31052' AND id = 1;
  

  如果返回0，说明该组合确实不存在，可能是之前的插入操作未提交，或者记录被删除。
• 检查事务状态：如果当前插入操作在未提交事务中，而measurement表的对应记录是在另一个未提交事务中插入的，默认的READ COMMITTED隔离级别会导致当前事务无法读取到未提交记录，从而触发约束。尝试提交所有未完成事务后再执行插入。
• 确认schema一致性：检查两张表是否都在public schema下，避免因schema不同导致外键关联的表并非预期的目标表。

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