On Thursday 10 November 2005 15:58, you wrote:
> >> The multi-table-unique-constraint problem has to
> >> be solved before we can even think much about multi-table FKs :-(
> >
> > Do you have ideas about how this should be solved?
>
> There's some discussions in the pghackers archives --- look for
> "multi-table index" and similar phrases. But if anyone had had
> a really decent plan, it would have got done by now :-(
>
Are multi-table indexes really required? After reading the code some more, I
came across this comment in nbtinsert.c:_bt_doinsert
* NOTE: obviously, _bt_check_unique can only detect keys that are already in * the index; so it cannot defend against
concurrentinsertions of the * same key. We protect against that by means of holding a write lock on * the target page.
Any other would-be inserter of the same key must * acquire a write lock on the same target page, so only one would-be *
insertercan be making the check at one time. Furthermore, once we are * past the check we hold write locks
continuouslyuntil we have performed * our insertion, so no later inserter can fail to see our insertion. * (This
requiressome care in _bt_insertonpg.)
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.
Matt