Re: simplifying foreign key/RI checks

I wasn't able to make much inroads into how we might be able to get
rid of the DETACH-related partition descriptor hacks, the item (3),
though I made some progress on items (1) and (2).

For (1), the attached 0001 patch adds a new isolation suite
fk-snapshot.spec to exercise snapshot behaviors in the cases where we
no longer go through SPI.  It helped find some problems with the
snapshot handling in the earlier versions of the patch, mainly with
partitioned PK tables.  It also contains a test along the lines of the
example you showed upthread, which shows that the partition descriptor
hack requiring ActiveSnapshot to be set results in wrong results.
Patch includes the buggy output for that test case and marked as such
in a comment above the test.

In updated 0002, I fixed things such that the snapshot-setting
required by the partition descriptor hack is independent of
snapshot-setting of the RI query such that it no longer causes the PK
index scan to return rows that the RI query mustn't see.  That fixes
the visibility bug illustrated in your example, and as mentioned, also
exercised in the new test suite.

I also moved find_leaf_pk_rel() into execPartition.c with a new name
and a new set of parameters.

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