
PostgreSQL inheritance - craigkerstiens
https://swth.ch/2016/12/03/postgresql-inheritance/
======
ars
Just be aware that this feature is not totally complete.

You can't make a foreign key to the parent table and have it to match all the
sub-tables. I needed this, to I used a trigger to enforce referential
integrity instead.

See: [https://www.postgresql.org/docs/9.6/static/ddl-
inherit.html#...](https://www.postgresql.org/docs/9.6/static/ddl-
inherit.html#DDL-INHERIT-CAVEATS)

Another issue with it is inconsistent output for pg_dump -s (dump the DDL that
makes the tables). Depending on how you made the inherited tables the
[apparently] same structure can output different DDL.

I suppose that doesn't really matter, but it makes you wonder what's going on
behind the scenes. (It also makes it hard to verify that multiple machines all
have identical copies of the database structure.)

Anyway, it feels like a nice feature that hasn't been fully polished yet.

~~~
merb
> dump the DDL that makes the tables

is this a feasable approach? I always tought that DDL and Data should be saved
differently? i.e. one should stay in a repository, the other should be done
with a backup system like barman or pg_dump -Fca? (P.S.: we use pg_dump -Fca
and save the ddl with flyway in a repo)

~~~
sbuttgereit
We need to clarify what reasons you might have to dump a database. One is for
disaster recovery, the other is for supporting ongoing development.

For many, many PostgreSQL users in need of basic disaster recovery, a simple
pg_dump of the entire database is sufficient and reliable for backup. That's
not to say that these are sophisticated users, some are small manufacturing
companies, some are small retail business, etc, etc. Anything more than a
simple nightly dump is more (sometimes) than their staffs can handle. I think
anyone on a more sophisticated basis would be using archive logs which are
also a mix of DDL and data by their nature... moving into replication which
isn't so different).

In a disaster recovery scenario, definition of the database (DDL) can and
really should be treated the same as data. If I try to recover DDL from one
safe-keeping repository and the data from another, the likelihood that I'll
encounter a mismatch and not be able to recover is greater, one missed commit
in an important DDL change could mean I can't restore data aware of the
uncommitted code. (Most) DDL and data are statefully co-dependent and putting
them on separate a separate footing is unnecessary risk.

Now managing DDL and, say, test data during software development is a
different matter. I want to version control my DDL (there are a number of good
techniques for this), while not fully retaining data, and certainly not doing
so in source control. In this case I do want that separation so that I don't
pollute my DDL files with data (which would overwhelm them), with the
exception, perhaps, of certain kinds of seed data needed for bootstrapping the
database (if applicable). Mind you, I wouldn't use pg_dump for getting the DDL
but would rather use one of the purpose build database versioning tools to
manage code as I want to organize it and before putting it in the database.

