Re: WIP: Deferrable unique constraints

Greg Stark <gsstark(at)mit(dot)edu> writes:
> For foreign keys I was picturing some way to issue an SQL statement
> like "SELECT from tabletocheck where ctid in (<magic parameter>) and
> not exists (select 1 from referenced_table where pk =
> tabletocheck.fk)" and then somehow pass the list of ctids from the
> deferred list.

I have no problem with having some "magic" in there --- FK checks
already have to do some things that aren't expressible in standard SQL,
because of snapshotting issues. However, the above still presumes that
we can afford to store all the CTIDs involved. Which is more or less
exactly what the trigger event queue is doing now. We need a different
view about that bit, I think.

Perhaps we could remember the CTIDs the transaction has touched in a
bitmap (which could become lossy under memory pressure)? On a lossy
page you'd need to check all the tuples to see which ones have xmins
matching your transaction; but that's not too terrible and at least
you're not visiting pages you don't need to.

regards, tom lane