Inheritance vs. LIKE - need advice - Mailing list pgsql-general

Dear pgsql-general moderators,

I sent this post out over the weekend.

Is there a reason why it is not getting posted to the list?

Many thanks for your assistance.  I really need some advice on this
issue from people with experience using both inheritance mechanisms
in PostgreSQL.

Cheers,
Bill Bug

>
> Hi All,
>
> Sorry to bring up the topic of PostgreSQL inheritance again, but
> after going through the archives and Google results, I still don't
> have a clear sense of whether my plan to implement a schema I'm
> working on is the preferred way to go.
>
> First, I'd like to find out if the way I'm thinking about
> Inheritance vs. the SQL DDL CREATE TABLE modifier LIKE is correct.
>
> The simplest analogy I can think of from OO techniques is PGSQL
> Inheritance corresponds to Class Inheritance, while LIKE is more
> like an inheritable Interface (Java) or Mixin (Ruby).  Inheritance
> maintains strict hierarchical relationships propagating the "Class"
> identity down through to all progeny. LIKE on the other hand simply
> provides a means to re-use a set of fields in an unlimited number
> tables without having to redefine those fields for each table you
> use them in.
>
> This view is incomplete and far from a perfect fit to the way PGSQL
> Inheritance & LIKE work, but I think it's a helpful way of thinking
> of these 2 related mechanisms, when trying to decide how and when
> to use them in their current form.  As has been mentioned many
> times in posts here, as well as in the PGSQL docs, PGSQL
> Inheritance is only partial. Table fields are propagated as well as
> the group identity, but no other RDBMS objects created on the
> parent ( INDEXES, CONSTRAINTS & SEQUENCES primarily) are
> inherited.  As has been endlessly stated in posts here and
> elsewhere, this is a significant short-coming for the PGSQL
> Inheritance mechanism which those of us desirous of using
> Inheritance would love to see fixed (I understand it has been on
> the TODO list for many years, as this mechanism has been in the
> PGSQL code base for over 15 years).
>
> I don't agree this makes PGSQL Inheritance unusable.  There are
> situations where I think it can still be useful, and I describe one
> below.  I'd welcome feedback on that opinion, however, as I'd hate
> to have my relative ignorance doom the data schema I'm about to
> fill with a few million rows of data to serious problems later.
>
> The following is an example of using both Inheritance and LIKE in
> the context described above.
>
> CREATE TABLE curation_info (
>    created_by       TEXT    NOT NULL,
>    create_date     TIMESTAMP WITH TIME ZONE,
>    modified_by     TEXT    NOT NULL,
>    mod_date          TIMESTAMP WITH TIME ZONE
> );
>
> CREATE TABLE book (
>     id_pk                    SERIAL    PRIMARY KEY,
>     title                        TEXT        NOT NULL,
>     author_id_fk        INT            NOT NULL,
>    publisher_id_fk    INT            NOT NULL,
>     pub_year              DATE        NOT NULL,
>     total_pages         INT            NOT NULL
>     LIKE curation_info
> );
>
> CREATE TABLE novel (
>     id_pk                    SERIAL    PRIMARY KEY,
>     genre_id_fk        INT            NOT NULL
> ) INHERITS (book);
>
> CREATE TABLE textbook (
>     id_pk                    SERIAL    PRIMARY KEY,
>     subject_id_fk    INT    NOT NULL
> ) INHERITS (book);
>
>
> CREATE TABLE publisher (
>     id_pk                    SERIAL    PRIMARY KEY,
>     name                        TEXT        NOT NULL,
>     address_id_fk        INT            NOT NULL,
>     LIKE curation_info
> );
>
> CREATE TABLE author (
>     id_pk                    SERIAL    PRIMARY KEY,
>     last_name           TEXT        NOT NULL,
>     first_name             TEXT        NOT NULL,
>     middle_name         TEXT        NOT NULL,
>     address_id_fk        INT            NOT NULL,
>     LIKE curation_info
> );
>
> This is not the best way to model book info (for instance, books
> are only allowed to have 1 author in this schema), but it will help
> me to make my point.
>
> Books, novels and textbooks will be considered equivalent in the
> context of many queries.  At the same time, there will be other
> queries where it will be important to consider novels & textbooks
> as distinct entities.  The PGSQL Inheritance mechanism easily
> supports both of these situations.
>
> The curation fields listed in the 'curation_info' table are found
> ubiquitously in tables throughout many data schema.  However, it is
> not likely there would be a circumstance where you would want to
> consider all tables containing these fields "curatable entities" to
> be queried as a group.  That simply makes no sense.  In this case,
> LIKE seems to be the best way to propagate these fields, since it
> doesn't couple all tables containing them to the parent
> 'curation_info' table.
>
> As I see it, there are at least 3 major problems with adopting such
> a schema - despite the obvious efficiencies it offers (most of
> which have been reported elsewhere):
>     1) none of the parent table ('book') CONSTRAINTS or INDEXES are
> propagated to the children.  This means if you want the children to
> have the same CONSTRAINTS - as you probably will - you need to
> build them yourself for each child table.
>     2) the primary keys generated across the book, novel & textbook
> tables are completely uncouple and will definitely collide.  In
> other words, due to the fact that neither the SEQUENCE behind the
> 'book.id_pk' SERIAL field, not the PK CONSTRAINTS & INDEX that
> comes with that field will automatically propagate to the child
> tables.  That is why the SQL DDL given above has an 'id_pk' SERIAL
> field in all 3 tables.  There may be some conditions where you want
> those PKs to be independent from one another, but those will be
> much less frequent than the times when you will require they all
> derive from the same SEQUENCE.
>     3) The fields inherited from the 'curation_info' table via the
> LIKE modifier are in no way linked back to the table from which
> they originated, unlike a an Interface (in Java) or Mixin (in Ruby)
> would be.  If the 'mod_date' field is remove from 'curation_info'
> it will still remain in all the tables created using
> 'curation_info' prior to making that change.  Same is true if a new
> field is added to 'curation_info'.  If you want that field to be
> represented in all those tables that had previously been created
> using the LIKE 'curation_info' modifier, you will have to re-CREATE
> those tables from scratch.
>
> As I see it, '1' & '3' above are significant drawbacks with no
> obvious work-around, but they are not deal breakers.  I would still
> have reason to want to use both Inheritance and LIKE because of the
> efficiencies they provide.
>
> '2' above has a simple and obvious work around which I've been
> surprised I've not been able to find posted anywhere (leading me to
> believe I must be missing something).  PGSQL automatically builds a
> SEQUENCE object on SERIAL fields.  The name of the SEQUENCE is
> simply the concatenated name of the table + SERIAL field with
> '_seq' appended to the end - e.g., for the 'book' table, it would
> be 'book_id_pk_seq'.  In order to guarantee the child tables use
> that same sequence, you simply declare them as follows instead of
> using the SERIAL type:
>
> CREATE TABLE textbook (
>     id_pk                    INT8     DEFAULT nextval
> ('book_id_pk_seq')         NOT NULL,
>     subject_id_fk      INT        NOT NULL
> ) INHERITS (book);
> ALTER TABLE textbook ADD CONSTRAINT textbook_pk_c PRIMARY KEY(id_pk);
>
> Is it a pain to have to write - and maintain - this extra SQL DDL?
> Yes.  Having said that, will it provide the desired behavior?  Most
> definitely yes - again, unless I'm missing something.
>
> So this is how I plan to use INHERIT & LIKE.
>
> My main reason for posting this here, is to get a chance to draw on
> the breadth of PostgreSQL experience out there to bring the major
> pitfalls in taking this approach to my attention.  I think whatever
> feedback folks have to offer will be very helpful to me and to
> others in search of guidance on this issue.
>
> I also thought it would be helpful to present this for archive
> purposes, since, despite the fact much of what I say here is
> mentioned elsewhere, I had to search far and wide to find it all
> and consolidate my thinking on the topic, so it might save others
> some time to see it all in one place.
>
> It might also be worth adding some of this "advice" - if the
> consensus is this view is reasonable given the current state of the
> Inheritance mechanism in PostgreSQL - to one of the official
> PostgreSQL docs - e.g., FAQ, etc..
>
> Many thanks ahead of time for your feedback and patience in reading
> this through to the end.
>
> Cheers,
> Bill Bug
>
>
> P.S.: Should this end up double posted, I apologize.  There was a
> problem with the list accepting my aliased email address.
>
>
> Bill Bug
> Senior Analyst/Ontological Engineer
>
> Laboratory for Bioimaging  & Anatomical Informatics
> www.neuroterrain.org
> Department of Neurobiology & Anatomy
> Drexel University College of Medicine
> 2900 Queen Lane
> Philadelphia, PA    19129
> 215 991 8430 (ph)
>
>
>