Re: Another FK violation when referencing a multi-level partitioned table

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> On 2020-Feb-06, Jehan-Guillaume de Rorthais wrote:
>> Please, find in attachment a proposal patch to fix this FK violation.

> You fix looks correct to me, so pushed. I took a minute to apply some
> minor corrections to the comments, too.

FWIW, I think the test query is too smart for its own good:

SELECT fk.fk_a, pk.a
FROM fkpart9.fk
LEFT JOIN fkpart9.pk ON fk.fk_a = pk.a
WHERE fk.fk_a=35;

The trouble with this is that the planner would be fully within its
rights to conclude that the FK constraint means a pk row must exist
for every fk row, and hence strength-reduce the left join to a
plain join. That would mask this bug, if it ever reappeared.
There's no such optimization today, but people have proposed patches
that do that or very similar things, so I have no confidence in the
long-term safety of this test case. I'd replace the above SELECT
with just

SELECT * FROM fkpart9.fk;

and maybe also similarly select all of fkpart9.pk, if you wanted
to verify that the right thing had happened on that side.

regards, tom lane