Add support for PostgreSQL partition management

[tomdcc]

This is mentioned briefly in #7518, but worth fleshing out what useful support would look like.

This would be support for the various statements described here.

Create a partitioned table:

CREATE TABLE foo (..) PARTITION BY RANGE (x, y);
CREATE TABLE foo (..) PARTITION BY LIST (x, y);
CREATE TABLE foo (..) PARTITION BY HASH (x, y); -- Postgres 11 only

Create a partition:

CREATE TABLE foo_part PARTITION OF foo FOR VALUES FROM (1) TO (100); -- range
CREATE TABLE foo_part PARTITION OF foo FOR VALUES IN (1, 2, 3); -- list
CREATE TABLE foo_part PARTITION OF foo FOR VALUES WITH (MODULUS 4, REMAINDER 0); -- hash, PG11 only
CREATE TABLE foo_part PARTITION OF foo DEFAULT; -- list or range, PG11 only

Attach partition:

ALTER TABLE foo ATTACH PARTITION foo_part FOR VALUES FROM (1) TO (100); -- range
ALTER TABLE foo ATTACH PARTITION foo_part FOR VALUES IN (1, 2, 3); -- range
ALTER TABLE foo ATTACH PARTITION foo_part FOR VALUES WITH (MODULUS 4, REMAINDER 0); -- hash, PG11 only
ALTER TABLE foo ATTACH PARTITION foo_part DEFAULT; -- list or range, PG11 only

Detach partition:

ALTER TABLE foo DETACH PARTITION foo_part;

For our purposes CREATE TABLE .. PARTITION OF .., ALTER TABLE .. ATTACH PARTITION .. and ALTER TABLE .. DETACH PARTITION .. would be enough as we're unlikely to do the creation of the partitioned table in the app at runtime, but maybe others would find it useful.

Versions:

• Database: PostgreSQL 10 / 11, all vendors
• PostgreSQL 10 added support for range and list partitioning
• PostgreSQL 11 (when released) adds support for hash partitioning and default partitions.

[lukaseder]

Thank you very much for your report. Indeed, supporting table partitions has been on the roadmap for a while, for Oracle. See e.g. #2774, #2775. An implementation in jOOQ would need to cover both databases' feature sets and also extend to DML usage of partitioned tables, and the code generator, of course.

[petekmet]

Hi there, please, is there any suggested workaround to programatically create partition to execute "CREATE TABLE table_part1 PARTITION OF table FOR VALUES IN(1,2,3,...)". I am newbee in jOOQ and I am leaning toward using DSL.execute("CREATE TABLE ...") which works but checking if there is more type safe way of workaround. Thank you.

[lukaseder]

Here are some options, @petekmet :

• You can patch generated SQL of a table without PARTITION clause, either by calling Query.getSQL() or by implementing an ExecuteListener.
• You can use template variables in your plain SQL template, e.g. for your table names, etc.