Re: simplifying foreign key/RI checks

On Fri, Nov 12, 2021 at 10:58 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I 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).
>
> I wrote that thinking that point (2), ie fix the choice of snapshots for
> these RI queries, would solve the brokenness in partitioned tables,
> so that (3) would potentially only require hacking up v14.
>
> However after thinking more I realize that (2) will break the desired
> behavior for concurrent partition detaches, because that's being driven
> off ActiveSnapshot. So we really need a solution that decouples the
> partition detachment logic from ActiveSnapshot, in both branches.

ISTM that the latest snapshot would still have to be passed to the
find_inheritance_children_extended() *somehow* by ri_trigger.c. IIUC
the problem with using the ActiveSnapshot mechanism to do that is that
it causes the SPI query to see even user table rows that it shouldn't
be able to, so that is why you say it is too global a mechanism for
this hack.

Whatever mechanism we will use would still need to involve setting a
global Snapshot variable though, right?

--
Amit Langote
EDB: http://www.enterprisedb.com