Re: partitioned tables referenced by FKs

Hi,

On 2019/03/15 2:31, Alvaro Herrera wrote:
> Once I was finished, fixed bugs and tested it, I realized that that was
> a stupid thing to have done -- because THOSE ARE DIFFERENT CONSTRAINTS.
> When you say "fk (a) references pk1" you're saying that all the values
> in fk(a) must appear in pk1. OTOH when you say "fk references pk" you
> mean that the values might appear anywhere in pk, not just pk1.

Sure, then just drop the check trigger that queries only pk1, in favor of
one that checks pk, that's already in place due to the parent constraint.
Actually, if one doesn't drop it (that is, by way of dropping the
constraint that created it), they won't be able to insert into fk anything
but the subset of rows that pk1 allows as a partition; see this:

create table pk1 (a int primary key);
insert into pk1 values (1);
create table pk (a int primary key) partition by list (a);
create table fk (a int, foreign key (a) references pk1, foreign key (a)
references pk);

insert into fk values (1);
ERROR: insert or update on table "fk" violates foreign key constraint
"fk_a_fkey1"
DETAIL: Key (a)=(1) is not present in table "pk".

alter table pk attach partition pk1 for values in (1);
insert into fk values (1);

create table pk2 partition of pk for values in (2);
insert into pk values (2);

insert into fk values (2);
ERROR: insert or update on table "fk" violates foreign key constraint
"fk_a_fkey"
DETAIL: Key (a)=(2) is not present in table "pk1".

You can no longer insert (2) into pk1 though.

Now it's true that a user can drop the constraint directly referencing pk1
themselves to make the above error go away, but it would've been nice if
they didn't have to do it. That would be if it was internally converted
into a child constraint when attaching pk1 to pk, as I'm suggesting.

> Have a
> look at the triggers in pg_trigger that appear when you do "references
> pk1" vs. when you do "references pk". The constraint itself might
> appear identical, but the former adds check triggers that are not
> present for the latter.

Let's consider both triggers.

1. The action trigger on the referenced table does the same thing no
matter whether it's a partition or not, which is this:

select 1 from fk x where $1 = a for key share of x;

Note that the trigger is invoked both when the referenced table is
directly mentioned in the query (delete from pk1) and when it's indirectly
mentioned via its parent table (delete from pk). Duplication of triggers
in the latter case causes the same set of rows to be added twice for
checking in the respective triggers' contexts, which seems pointless.

2. The check trigger on the referencing table checks exactly the primary
key table that's mentioned in the foreign key constraint. So, when the
foreign key constraint is "referencing pk1", the check query is:

select 1 from pk1 x where a = $1 for key share of x;

whereas when the foreign key constraint is "referencing pk", it's:

select 1 from pk x where a = $1 for key share of x;

The latter scans appropriate partition of pk, which may be pk1, so we can
say the first check trigger is redundant, and in fact, even annoying as
shown above.

So, when attaching pk1 to pk (where both are referenced by fk using
exactly the same columns and same other constraint attributes), we *can*
reuse the foreign key on fk referencing pk1, as the child constraint of
foreign key on fk referencing pk, whereby, we:

1. don't need to create a new action trigger on pk1, because the existing
one is enough,

2. can drop the check trigger on fk that checks pk1, because the one that
checks pk will be enough

As you know, we've done similar stuff in 123cc697a8eb + cb90de1aac18 for
the case where the partition is on the referencing side. In that case, we
can reuse the check trigger as it checks the same table in all partitions
and drop the redundant action trigger.

Thanks,
Amit