On Sat, 23 Mar 2019 at 12:01, Hadi Moshayedi <h...@moshayedi.net> wrote: > 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.
Hi Hadi, I reproduced the problem and tested your fix. It looks simple and correct to me. I was a bit curious about the need for "set role" in the reproduction, but I see that it's because RI_Initial_Check does some checks to see if a simple SELECT can be used, and one of the checks is for basic table permissions. I wonder if the macro RELKIND_HAS_STORAGE should be used instead of checking for each relkind? This would apply to the check on line 4405 too. Edmund
