cannot combine declarative partitioning with inheritance in postgresql

Question

We several tables which must inherit another table. The plan is to be able to perform queries between different tables using common attributes, but also perform queries on the parent table for analytical purposes. On top of this it might be necessary to edit the data in parent and child tables. The child tables already have all the necessary columns, and we have successfully inherited several of them.

Now we have an issue with a huge table partitioned by years and quarters (declarative partitioning):

[42809]: ERROR: cannot change inheritance of a partition.

The other option would be to use union, but without inheritance we would need to create editable views or something like that and change them each time we'll need another partitioned table... Or we might switch to inheritance only, just for compatibility reasons.

I already tried to inherit the partitions - doesn't work as well.

Any ideas?

"*which must inherit another table*" - why "must" they inherit? — , Oct 07 '21 at 05:35
@a_horse_with_no_name its not a real must, its about keeping things simple for data analysts and developers. We are managing similar data across different business lines and business units, stored in different source systems. The data has often common "denominators", like geography or address, or registrar numbers. By using inheritance we can manage all the tables at same time (e.x. add a column to parent table and it is added to child tables), and the data can be queried per business object (child) and per data object (parents) table. — Mihail Gershkovich, Oct 07 '21 at 15:02
We'll investigate the usage of TimescaleDB hypertables. Looks like it uses simple inheritance and not declarative partitioning. If everything works fine, the issue will be solved. However this will not take place before February next year... — Mihail Gershkovich, Oct 07 '21 at 15:04

score 1 · Answer 1 · answered Oct 07 '21 at 05:40

You cannot have a partition or a partitioned table in an inheritance tree, because partitioning uses inheritance “under the hood”.

“A” partitioned table sounds like it is a single table.

In that case, you can go with a view that combines your inheritance parent and the partitioned table:

CREATE VIEW world AS
      SELECT * FROM parent_table
   UNION ALL
      SELECT * FROM partitioned_table;

Yes, you'd have to adapt that table whenever you want another partitioned table, but does that happen all the time, so that modifying the view is too cumbersome?

If yes, you could write an event trigger that modifies the view whenever a partitioned table is created.

cannot combine declarative partitioning with inheritance in postgresql

1 Answers1