Re: Multi-table-unique-constraint

Matt Newell <newellm(at)blur(dot)com> writes:
> Would it be possible to make another routine that locates and aquires
> a write lock on the page where the key would be inserted in each
> index(for each table in the inheritance), and holds all these locks
> until the key is inserted into the correct index. It seems this would
> solve the unique problem without changing much else.

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.

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.

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.

'Tis a hard problem :-(

regards, tom lane