sql: implement table inheritance ( CREATE / ALTER TABLE ... INHERITS )

[trcarden]

Feature request: Postgresql Table Inheritance

Official Documentation

Table inheritance is typically established when the child table is created, using the INHERITS clause of the CREATE TABLE statement. Alternatively, a table which is already defined in a compatible way can have a new parent relationship added, using the INHERIT variant of ALTER TABLE. To do this the new child table must already include columns with the same names and types as the columns of the parent. It must also include check constraints with the same names and check expressions as those of the parent. Similarly an inheritance link can be removed from a child using the NO INHERIT variant of ALTER TABLE. Dynamically adding and removing inheritance links like this can be useful when the inheritance relationship is being used for table partitioning (see Section 5.9).

https://www.postgresql.org/docs/9.1/static/ddl-inherit.html

Use Case

Through inheritance referencing either all rows of a table or all rows of a table plus all of its descendant tables. This feature is used within Odoo which is an open source accounting and enterprise resource planning tool specifically here and there

Importance

Blocker: if we are to try cockroach with systems that rely on inheritance.

Workaround

None known at this time. That said if anyone has any ideas on how to make this work please share.

Note

Generally Speaking Enterprise resource (ERPs) planning tools where global workloads, consistency (for manufacturing and accounting) seem to be an ideal target for Cockroach Db. Opening up support for open source ERPs could potentially be a good adoption angle for enterprises.

Epic CRDB-8258

Jira issue: CRDB-5856

[jordanlewis]

Hi @trcarden,

Thanks for the feature request. We don't have plans to implement table inheritance in the near future. However, I believe there is a relatively simple workaround.

As far as I understand, table inheritance is meant to reduce duplication in schema definition - and that's it. To get around not being able to use INHERITS in a child table, just duplicate the column definitions from the parent table to the child table.

In your first example, the ir_actions table just has a single id column - so you'd duplicate that column definition to all of the tables that inherit it.

It's a little more complicated in the second example, which dynamically creates inherited tables, but you could still do it by introspecting on the parent table and copying the columns to the child.

[jordanlewis]

@vivekmenezes moving to your team.

[SevereOverfl0w]

I think this is more complicated than schema inheritance.

My understanding of https://www.postgresql.org/docs/9.1/ddl-inherit.html Using inheritance allows you to query cities and that will include results from capitals.

In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendant tables. The latter behavior is the default. For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

[jordanlewis]

Oh, neat. So it's like a table with an implicit join on another table. Thanks for clarifying! I think that it's unlikely that we'll get to implementing this any time soon.