Re: [PATCH] Automatic HASH and LIST partition creation

Hi,
I found that thread (and the patch), but it seems to be pretty dead.
Patch didn't apply, due to gen_node_support.pl
Can I hope for a rebirth ?

I've made a rebased patch,in case of no response...
It's just the patch from
https://www.postgresql.org/message-id/CALT9ZEG9oKz9-dv9YYZaeeXNpZp0+teLFSz7QST28AcmERVpiw@mail.gmail.com
rebased on 17dev

Perhaps it's too early for a commit ; automatic range partitioning is still
missing and, according to
https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements,
syntax is arguable.

If 'USING' it out of option (already a keyword for CREATE TABLE) and
'CONFIGURATION()' is not what we want, we should reach for a final decision
first.
I suggest OVER that is a keyword but unused in CREATE TABLE (nor ALTER
TABLE). Whatever...

For RANGE partitioning I think of four syntaxes (inspired by pg_partman)
PARTITION BY RANGE(stamp) CONFIGURATION (SPAN interval CENTER datetime BACK
integer AHEAD integer [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(stamp) CONFIGURATION (SPAN interval
START firstfrombound END lasttobound [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(region_id) CONFIGURATION (STEP integer START integer END
integer [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(name) CONFIGURATION (BOUNDS (boundlist) [START
firstfrombound] [END lasttobound] [DEFAULT [PARTITION] [defname]])

Last one should solve the addition operator problem with non numeric non
timedate range.
Plus, it allows non uniform range (thinking about an "encyclopedia volume"
partitioning, you know 'A', 'B-CL', 'CL-D'...)

CREATE table (LIKE other INCLUDING PARTITIONS) should create 'table'
partitioned the same as 'other'
and
CREATE table (LIKE other INCLUDING PARTITIONS) PARTITION BY partspec
CONFIGURATION(), should create 'table' partitioned by partspec and sub
partitioned as 'other'.

Then CREATE could accept multiple PARTITION BY CONFIGURATION().

For ALTER TABLE (and automatic maintenance) to be usable, we will need
SPLIT and MERGE CONCURRENTLY (pg_pathman ?) enhanced by CREATE TABLE LIKE
to handle subpartitioning. But that's another story.

Stéphane.

Le jeu. 2 déc. 2021 à 12:20, Daniel Gustafsson <daniel(at)yesql(dot)se> a écrit :

> This thread has stalled since July with review comments unanswered, I'm
> marking
> the patch Returned with Feedback. Please feel free to resubmit when/if a
> new
> patch is available.
>
> --
> Daniel Gustafsson https://vmware.com/
>
>
>
>

--
"Où se posaient les hirondelles avant l'invention du téléphone ?"
-- Grégoire Lacroix