Table Interfaces (Inheritance again. I know. I'm sorry.) - Mailing list pgsql-general

Searching through the lists for "inheritance" yields a War and Peace level of content back. "inheritance harmful" and "inheritance interface" didn't answer my question.

What about cross-cutting concerns like what might find with interfaces in many OO languages? For example:

  CREATE TABLE auditable (

    id uuid NOT NULL,

    actor varchar NOT NULL, -- username

    created timestamptz NOT NULL

  );

  CREATE TABLE authable (

    id uuid NOT NULL,

    actor varchar NOT NULL, -- username

    access_tags varchar[]

  );

  CREATE TABLE a (  ...  ) INHERITS (auditable);

Most of the discussion I've seen regarding inheritance has centered on cases where a base table with foreign key relationships for specialization would suffice. In the case above, it serves ONLY to enforce consistent style and allow for utility functions to share logic due to the aforementioned consistent naming. Uniqueness is not necessary (but UUIDs handle that anyway), the tableoid is present for reverse lookups when needed, defaults, check constraints, et al. are not a concern. But as an "interface", the hierarchy isn't like one would find in traditional OOP extends, more like Java's interfaces.

There are also avenues for using event triggers to detect the creation of tables that conform to certain "interfaces" to automatically add certain logic. For example:

  CREATE TABLE track_modified (

    last_modified timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP

  );

  CREATE TABLE x ( ... ) INHERITS (track_modified);

  CREATE TABLE z ( ... ) INHERITS (track_modified);

Where an event trigger would fire, see that each table has a last_modified column, and an expectation to update the last modification a la MySQL's ON UPDATE CURRENT_TIMESTAMP.

An empty parent table could even be useful for an event trigger to add a regular set of policies without repeating yourself (and keeping them all in sync after the fact).

That said, I've also seen messages dating all the way back to 2005 claiming that INHERITS is a red-headed stepchild that would be phased out shortly after table partitioning landed and matured. Is this still the intention? Is inheritance just considered such an old feature that no one dare remove due to breaking users? Or have folks come to find good use cases for it even though it's used for far too many use cases?