jobinau / pg_partmaint

Super Simple partition maintenance automation for PostgreSQL
8 stars 3 forks source link

First range is repeating for the generated partition. #3

Open jobinau opened 1 year ago

jobinau commented 1 year ago

Thanks to @gianmh for reporting the problem.

Problem Reproduction step

Create the first partition

CREATE TABLE public.tab1_p0 PARTITION OF public.tab1 FOR VALUES FROM ('0') TO ('500000');

Then generate the new partitions.

./pg_partmaint.py -c "" -t public.tab1 -i 5000000 -p 40 --display

The output generated for newly created partition will contain the same partition again

 CREATE TABLE public.tab1_p0 PARTITION OF public.tab1 FOR VALUES FROM ('0') TO ('5000000')
 CREATE TABLE public.tab1_p5000000 PARTITION OF public.tab1 FOR VALUES FROM ('5000000') TO ('10000000')
 CREATE TABLE public.tab1_p10000000 PARTITION OF public.tab1 FOR VALUES FROM ('10000000') TO ('15000000')
 CREATE TABLE public.tab1_p15000000 PARTITION OF public.tab1 FOR VALUES FROM ('15000000') TO ('20000000')
jobinau commented 1 year ago

Analysis reveals that the cause of the problem is that the DDL returned by pg_catalog.pg_get_expr() will be unquoted values like

FOR VALUES FROM (0) TO (500000)

Instead of what was expected by the existing logic, like

FOR VALUES FROM ('0') TO ('500000')