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

On Friday 11 November 2005 11:07, you wrote:

> It's an idea, but you are now staring directly into the hornet's nest:
>
> 1. How do you avoid deadlock among multiple processes all doing the
>    above for similar (same page anyway) keys?  It's difficult if not
>    impossible to ensure that they'll try to take the page locks in
>    the same order.
>
Isn't all that is required is that they iterate through the indexes in the 
same order.  This shouldn't be hard to do, because the set of indexes is the 
same no matter what table you are inserting into, because the unique 
constraint will apply to all tables both up and down the inheritance tree.  
That order just needs to be stored somewhere.

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. 

When aquiring the locks on the index pages, you must a) have a ROW SHARE lock on the pg_indexset row for this set,
thisensures that the schema won't change from under us.
 
b) do so in the order that the index oids are in.

This solves the problem  below also, because you would hold a row exclusive 
lock on the row in this table whenever adding or removing indexes from the 
set.

Now that i think about it some more, i realize that you only need to hold read 
locks on the index pages that you don't plan on actually inserting a new key 
into, which shouldn't cause near as much lock contention as holding write 
locks on multiple indexes' pages.

> 2. What happens when another process is adding/dropping indexes that
>    should be in the index set?  In the normal scenario you don't have
>    any sort of lock on any of the other tables, only the one you are
>    trying to insert into; and so you have no defense against somebody
>    changing their schemas, up to and including dropping the index you
>    are fooling with.  Adding such locks would increase the deadlock
>    hazard.

> 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?  I mean you can already do 
Select from parent where key=X; and the planner takes care of scanning 
multiple indexes(or sequence scans).

If it is required though, it should be no more difficult that doing what i 
described above, right?

> 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).  This
> fixes the lookup and entry problems, but it's not any help for the
> lock-against-schema-mods problem, and it leaves you with a real headache
> if you want to drop just one of the tables.
>
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. 

> 'Tis a hard problem :-(
I think that's why i'm interested:)  I hope that I can succeed so as to not 
have wasted your valuable time. 

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

Matt