Re: WIP: Deferrable unique constraints

On Mon, Jul 27, 2009 at 7:51 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> (In fact, in a real sense these ARE join problems ... maybe we should
> stop thinking of them as fire-a-bunch-of-triggers and instead think of
> executing a single check query with appropriate WHERE clause ...)

A while back I suggested keeping the deferred trigger list in
tuplestore format and executing the trigger check as a query between
the materialized tuplestore of and the tuples on disk.

I love the idea of doing a full SQL query but the problem is that
there's no particular reason to assume that a deferred trigger list
large enough to warrant a wholesale check is actually a significant
percentage of the table. It might only take a few hundred or few
thousand checks to warrant a bitmap index scan instead of repeated
index probes but a plain SQL query with no reference back to the
deferred list would have to check all the millions of rows in the
table for no purpose.

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.

--
greg
http://mit.edu/~gsstark/resume.pdf