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
