Re: WIP: Deferrable unique constraints

On Tue, Jul 28, 2009 at 12:04 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

It wasn't clear in the SQL example but I described storing them in a
tuplestore. The tuplestore would get spilled to disk automatically but
the SQL query could (semi)join against it using whatever form of join
is most efficient.

Now that I look at that query though it's pretty clear that we don't
actually have a good join type to handle this. We would need some kind
of merge-join which knew that ctids from a sequential scan were in
order (and could ensure that they were in fact in order).

There might be a better way to write the query above in a way that
didn't need anything special like that. The need to check that the
inserted tuple is still live is a big part of the headache. If we
could check for violations first and then go back and check any
violations if there are any to see if they come from live tuples that
would save a lot of work.

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