Matt Newell <newellm@blur.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
difficultif 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
scenarioyou don't have any sort of lock on any of the other tables, only the one you are trying to insert into; and
soyou 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
