Re: Integrity and Inheritance

On Sun, 10 Jun 2001, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > One problem is that for update isn't supported across inheritance trees
> > AFAICS and the triggers use for update for the appropriate locking.
>
> It's possible (maybe even likely) that this could be fixed with minimal
> work. I punted on it for 7.1 due to lack of time. However, is that
> really the major obstacle in the way of inherited foreign keys? Seems
> like there are a bunch of issues that'd require cross-table unique
> indexes, as well.

No, it's not the major constraint. But he wanted to know if he should
make his own triggers (presumably with ours as a starting point). The
first major problem he'd see is that as soon as he took the ONLY off
the constraint would suddenly fail because of the for update. In the
past (before noticing the comment on for update and inheritance), I'd
suggested to people that they might try making a trigger that way. The
uniqueness thing really plays into the ref actions more than the
check, and he'd possibly have to write those from scratch anyway.
And I think there are some places where we grab a lock on the pk
table which might need to be extended to the children. Actually, the
uniqueness thing may also become less problematic once I finish making the
constraints check to see if another matching row has been inserted in
the time between the creation of the trigger and its running.

Some of what's necessary depends on things that we might want to do to
inherited tables (column renaming for example and which way unique
should work), so I'm not all that motivated to look at it until a concrete
plan was put down for how inheritance is going to change. I wouldn't
mind doing the legwork on the FK constraints at that point.