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

Alvaro Herrera <alvherre@2ndquadrant.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