Re: FK's to refer to rows in inheritance child

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Wed, Dec 1, 2010 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Perhaps I should have said "possibly workable proposal". What you wrote
>> doesn't even begin to cover the interesting part of the problem, namely
>> how to ensure uniqueness is preserved in the face of concurrent
>> insertions.

> I think it wouldn't be very hard to implement "global indexes" which
> are just regular indexes that index records from multiple tables. The
> index tuple would have to have a relid as well as a ctid. Of course it
> would be a lot of work to touch up all the places in the source where
> indexes are assumed to reference only one table, and there may be some
> tricky parts, but the index itself wouldn't be too hard to implement.

That's been proposed before, and shot down before, though I don't recall
all the reasons offhand. One obvious problem is VACUUM, which assumes
that you can't have two processes trying to vacuum the same index
concurrently. Another is what happens when you drop one of the tables
involved in the index. Even the locking involved to make a uniqueness
check against a different table would be not-nice (locking a table after
you already have lock on its index risks deadlock against operations
going the other way).

regards, tom lane