Re: Multi-table-unique-constraint - Mailing list pgsql-hackers

Matt Newell <newellm@blur.com> writes:
> On Friday 11 November 2005 11:07, you wrote:
>> 1. How do you avoid deadlock among multiple processes all doing the
>> above for similar (same page anyway) keys?

> Isn't all that is required is that they iterate through the indexes in the 
> same order.

Yeah, I was thinking along the same lines.  As long as any one index is
a member of at most one index set, this'd probably work.  (Maybe you
wouldn't even need that restriction if you used a globally defined
ordering, such as always processing the indexes in order by their
pg_class OIDs.)  Some concept of shared and exclusive locks on index
sets (extending only to the membership of the set, not to operations on
the individual member indexes) might fix the schema-change problem, too,
although you still need to think about whether there's a risk of
deadlocks for that.  In the past we've figured that exclusively locking
a table is necessary and sufficient for schema alterations on that
table, but I'm not sure what to do for cross-table index sets.

> What if there was a new system relation(pg_indexset) that stores an array of 
> index oids.  Each index that is part of an index set has an fkey into this 
> table. 

I'd be inclined to think about using pg_inherits instead, ie, pretend
that the child table indexes are inheritance children of the parent
table index.  If this is too inefficient, it suggests that we need to
fix pg_inherits anyway.

>> Also, for many scenarios (including FKs) it's important to be able to
>> *look up* a particular key, not only to prevent insertion of duplicates.
>> The above approach would require searching multiple indexes.
>> 
> Why would this be required, if it currently isn't?

Well, because we're trying to do something that currently isn't possible?
It might not matter that we don't have a single instant at which we can
swear that the key is not present anywhere in the hierarchy, but I'm not
convinced that this is obviously true.

Your thought about leaving read locks on index pages while searching
other indexes might fix that, though, if it needs fixed at all.

>> Most of the people who have thought about this have figured that the
>> right solution involves a single index spanning multiple tables (hence,
>> adding a table ID to the index entry headers in such indexes).

> It seems that the above solution would be less work, and would keep the data 
> separate, which seems to be one of the biggest advantages of the current 
> inheritance design. 

Yeah, I'm getting more attracted to the idea as I think about it.  Not
so much because it keeps the data separate, as that it avoids needing to
store a table OID in index headers, which has been a principal objection
to cross-table indexes all along because of the space cost.

Probably the next thing to think about is how this would impact the
index AM API.  I'm disinclined to want to put all of this logic inside
the index AMs, so somehow the "find and leave page write locked" behavior
would need to be exposed in the AM API.  That ties into a larger goal of
not wanting the unique-index behavior to be totally the AM's
responsibility as it is right now --- I dislike the fact that nbtree is
responsible for reaching into the heap to test rows for liveness, for
instance.  If we could separate that out a bit, it might make it easier
to support unique-index behavior in the other AMs.

> BTW, i'm on the list now, so no need to cc me.

Common practice around here is to cc people anyway --- this has grown
out of a history of occasionally-slow list mail delivery.  If you don't
want it, best to fix it in your mail filters rather than expecting
people to change habits for you.
        regards, tom lane