Re: Some thoughts on table inheritance (which is uniquely awesome on PostgreSQL) - Mailing list pgsql-general

On Thu, Aug 23, 2012 at 12:36 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Aug 22, 2012 at 10:22 PM, Chris Travers <chris.travers@gmail.com> wrote:
>> I have now been working with table inheritance for a while and after
>> starting to grapple with many of the use cases it has have become
>> increasingly impressed with this feature.  I also think that some of
>> the apparent limitations fundamentally follow from the support for
>> multiple inheritance, and multiple inheritance itself is so useful I
>> would not want to see this go away.  Inheritance really starts to come
>> to its own once you start using table methods, and some features that
>> are useful in some sorts of inheritance modelling are useless in
>> others.
>
> The problem with postgres table inheritance is that it doesn't really
> solve the problem that people wanted solved: to be able to define an
> set of specific extra attributes for each row depending on some
> characteristic of that row.  The feature only tantalizingly
> masquerades as such.

This is true to some extent.  I may have found a solution there, which
is to build your inheritance solutions on top of the solutions used
for table partitioning.  It's one reason why I say you should start
working with it on table partitioning before you try to do set/subset
modelling elsewhere.  Interestingly one appealing solution doesn't
really work (which is to put a check constraint which checks the
tableoid column, presumably because this isn't set on insert until
after the check constraint fires).

One thing I have found looking through Oracle and DB2 docs is that
their table inheritance seems to have all the same problems as ours
and their solutions to these problems seem rather.... broken from a
pure relational perspective.

For example, Oracle and DB2 make extensive use of OID's here (which
must be recorded in some sort of system catalog somewhere given what
they do with them), and they have functions to take a "reference" to a
row and operators to "dereference" the row.  This sounds all good and
well until you come across the IS DANGLING operator, which returns
true when the reference no longer is there...

In other words, as far as I can see nobody else has come up with a
sane foreign key solution for inherited tables either.
>
> Until it found use in table partitioning, I found the inheritance
> feature to be basically useless.

I think one can actually borrow techniques from table partitioning to
solve the problems associated with inheritance.

However here's what turned me around on table inheritance:

1)  First, in LedgerSMB, we started using it to create consistent
interfaces to sets of storage tables.  The storage tables would behave
differently, but would inherit essentially interfaces from their
parents.  In this regard, you can think of an inheritance tree as a
partitioned table set, but where the partitioning is necessary because
foreign key fields reference different tables in different children.
We use this for example, to avoid having to have a global notes table
or global file attachments table and it gives us clear control over
where these can be attached along with central maintenance of data
structures.  In cases, like with file attachments, where foreign keys
to inheritance trees ended up being needed, we started out with a more
complex but workable solution but I think are going to a simpler one.
This is a good thing.

In essence what we did was use inheritance to give us variable target
tables for a foreign key column.  I would still like to see
inheritable foreign key constraints because that would make some
things a lot easier, but the idea that foreign keys are not, by
default, copied in, means that you can override the destination in the
child table.  It isn't the use documented but it actually works very
well.

2)  Secondly I re-read Stonebraker's "Object-Relational Database:  The
Next Wave" and I had a rather sudden epiphany.  Relational databases
are about modelling your data so you can ensure consistency and gain
as many answers as you can.  Object-relational modelling adds
interfaces (possibly written in arbitrary programming languages) to
derive additional information from stored information.  The example he
gives could be summarized in English to be "Give me all pictures of
sunsets taken within 20 miles of Sacramento" where whether a picture
is of a sunset is determined by analyzing the graphic itself.  Thus
you have to add features to allow you to plug into the query to answer
that question, and you have to have a planner capable of optimizing
such a query.

I also read some other papers which discussed table inheritance and
what sort of modelling problems it was designed to solve (the main one
is actually part/whole modelling where a row may be a whole in itself
and also a part of another whole--- for example we might sell timing
belts, but they might also come included in an engine assembly).

3)  I was talking with Matt Trout regarding object-oriented
programming in Perl, and he turned me on to Moose::Role as essentially
an interface class.  It cannot be instantiated and one would not
simply inherit it in order to instantiate it.  Rather it provides an
ability to assemble classes from re-usable pieces and thus provide
consistent interfaces across a project.    This has become a key to my
understanding of the use cases for multiple inheritance in PostgreSQL,
namely that you can define interfaces across column combinations and
combine those column combinations into a table.  The parent tables are
then largely uninteresting.

Viewed from this perspective, multiple inheritance gives you something
similar to what you get in the Informix examples I have seen regarding
embedding structured data type objects in columns in the table, but it
is superior to that because the individual columns can still be easily
queried using simple, relational queries.  No need to do something
like select customer.display_name() from invoices where .... just in
order to avoid getting something back in tuple notation.  Although we
could still do:  select i.customer_display_name from invoices i where
.... if we want to use derived values.

These things have turned me around from seeing inheritance as a
partially implemented, not particularly useful misfeature into
something I think is actually both extremely useful and can/should be
further developed in the future.  I also think that once solutions to
the harder modelling problems (like set/subset) are well understood
and documented, that more modest efforts can be used to smooth what
sharp corners remain.  However it is *very* hard to see value in a
feature when the examples in the documentation lead people down paths
which cause real pain for db professionals, especially when the docs
are, on the whole, of the quality they are in this project.
Inheritance can still solve the problems it was intended to solve, but
it must be used differently.  Given the existing solutions out there
(which all seem to be based on Informix's half-solution for this
problem), I think we may be in a position to do this right.

Best Wishes,
Chris Travers