Re: simplifying foreign key/RI checks

Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> On Fri, Nov 12, 2021 at 8:19 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Anyway, I think that (1) we should write some more test cases around
>> this behavior, (2) you need to establish the snapshot to use in two
>> different ways for the RI_FKey_check and ri_Check_Pk_Match cases,
>> and (3) something's going to have to be done to repair the behavior
>> in v14 (unless we want to back-patch this into v14, which seems a
>> bit scary).

> Okay, I'll look into getting 1 and 2 done for this patch and I guess
> work with Alvaro on 3.

Actually, it seems that DETACH PARTITION is broken for concurrent
serializable/repeatable-read transactions quite independently of
whether they attempt to make any FK checks [1]. If we do what
I speculated about there, namely wait out all such xacts before
detaching, it might be possible to fix (3) just by reverting the
problematic change in ri_triggers.c. I'm thinking the wait would
render it unnecessary to get FK checks to do anything weird about
partition lookup. But I might well be missing something.

regards, tom lane

[1] https://www.postgresql.org/message-id/1849918.1636748862%40sss.pgh.pa.us