From: | Hadi Moshayedi <hadi(at)moshayedi(dot)net> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Fix foreign key constraint check for partitioned tables |
Date: | 2019-03-22 23:00:42 |
Message-ID: | CAK=1=WrnNmBbe5D9sm3t0a6dnAq3cdbF1vXY816j1wsMqzC8bw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Yesterday while doing some tests, I noticed that the following doesn't work
properly:
create role test_role with login;
create table ref(a int primary key);
grant references on ref to test_role;
set role test_role;
create table t1(a int, b int) partition by list (a);
alter table t1 add constraint t1_b_key foreign key (b) references ref(a);
In postgres 11.2, this results in the following error:
ERROR: could not open file "base/12537/16390": No such file or directory
and in the master branch it simply crashes.
It seems that validateForeignKeyConstraint() in tablecmds.c cannot
use RI_Initial_Check() to check the foreign key constraint, so it tries to
open the relation and scan it and verify each row by a call
to RI_FKey_check_ins(). Opening and scanning the relation fails, because it
is a partitioned table and has no storage.
The attached patch fixes the problem by skipping foreign key constraint
check for relations with no storage. In partitioned table case, it will be
verified by scanning the partitions, so we are safe to skip the parent
table.
-- Hadi
Attachment | Content-Type | Size |
---|---|---|
fix-foreign-key-check.patch | application/octet-stream | 677 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Fabrízio de Royes Mello | 2019-03-22 23:03:20 | Re: Error message inconsistency |
Previous Message | legrand legrand | 2019-03-22 22:46:36 | RE: Planning counters in pg_stat_statements (using pgss_store) |