3 ms·

PostgreSQL inheritance

89 points by craigkerstiens 10y ago

ars 10y ago
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#DDL-INHERIT-CAVEATS https://www.postgresql.org/docs/9.6/static/ddl-inherit.html#... 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.
- sbuttgereit 10y ago
  I think I'd make more use of the feature if it worked better with more standard features like referential integrity constraints; but I need the traditional RDBMS features more than I need a novel way to handle inheritance between related data structures.... so I don't often go there. I suspect that over the years demand for really filling this functional area out has probably not been priority. I agree that inheritance doesn't feel fully polished as compared to other feature sets, but I know this inheritance mechanism has been there for many years (may go back to the original design, not sure about that though): the earliest documentation I see for it is 2002. My guess is that it's not going to get that polish very soon, especially given that it's likely outside that average database developer's wheelhouse. Partitioning is probably the real driver behind it seeing much love at all.
- merb 10y ago
  > 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)
  - ars 10y ago
    Sorry, I don't understand the question. I dump the DDL in order to save in source control. So when the same apparent structure has different DDL it causes confusion, especially when you have other devs, and each dumps the current DDL to save into source control. I do pg_dump scheme -C -s > database.sql and store database.sql in source control. > pg_dump -Fca I would not use -a here, you should save the data and DDL together - if there is a disaster it's a lot easier to restore that way, and it doesn't cost much extra. i.e. save DDL in source control, together with code that uses that structure. Save both DDL, and data, in backup system.
  - sbuttgereit 10y ago
    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.

ars 10y ago

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#DDL-INHERIT-CAVEATS https://www.postgresql.org/docs/9.6/static/ddl-inherit.html#... 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.

sbuttgereit 10y ago
I think I'd make more use of the feature if it worked better with more standard features like referential integrity constraints; but I need the traditional RDBMS features more than I need a novel way to handle inheritance between related data structures.... so I don't often go there. I suspect that over the years demand for really filling this functional area out has probably not been priority. I agree that inheritance doesn't feel fully polished as compared to other feature sets, but I know this inheritance mechanism has been there for many years (may go back to the original design, not sure about that though): the earliest documentation I see for it is 2002. My guess is that it's not going to get that polish very soon, especially given that it's likely outside that average database developer's wheelhouse. Partitioning is probably the real driver behind it seeing much love at all.
merb 10y ago
> 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)
- ars 10y ago
  Sorry, I don't understand the question. I dump the DDL in order to save in source control. So when the same apparent structure has different DDL it causes confusion, especially when you have other devs, and each dumps the current DDL to save into source control. I do pg_dump scheme -C -s > database.sql and store database.sql in source control. > pg_dump -Fca I would not use -a here, you should save the data and DDL together - if there is a disaster it's a lot easier to restore that way, and it doesn't cost much extra. i.e. save DDL in source control, together with code that uses that structure. Save both DDL, and data, in backup system.
- sbuttgereit 10y ago
  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.