Re: Query plan and Inheritance. Weird behavior

> On 22 Jan 2003, John Lange wrote:
>> In this way the parent table would not need to know, nor would it care
>> about child tables in any way (just like inheritance in most programming
>> languages). If done this way a select on a parent table would only
>> require the retrieval of a single row and a select on a child table
>> would only require the retrieval of two rows (one in the child table and
>> one in the parent table).

No, it'd require the retrieval of N rows: you're failing to think about
multiple levels of inheritance or multi-parent inheritance, both of
which are supported reasonably effectively by the current model.
My guess is that this scheme would crash and burn just on locking
considerations.  (When you want to update a child row, what locks do you
have to get in what order?  With pieces of the row scattered through
many tables, it'd be pretty messy.)

You may care to look in the pghackers archives for prior discussions.
The variant scheme that's sounded most interesting to me so far is to
store *all* rows of an inheritance hierarchy in a single physical table.
This'd require giving up multiple inheritance, but few people seem to
use that, and the other benefits (like being able to enforce uniqueness
constraints over the whole hierarchy with just a standard unique index)
seem worth it.  No one's stepped up to bat to do the legwork on the idea
yet, though.  One bit that looks pretty tricky is ALTER TABLE ADD
COLUMN.

            regards, tom lane