Inheritance vs. LIKE - need advice

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)
>
>
>