run_maintenance is not working

[Sam2002]

id-Static partitioning is not working probably, when using the extension on a table with data inserted, all pre-partition data does not go to child tables and after running run_maintenance, no child tables are being created, how is it possible to overcome this problem?

[keithf4]

Can you give me the schema of your parent table and the exact options you used with create_parent()? Also the version of Postgres and the version of pg_partman (and pg_jobmon if it is installed?
Need to be able to recreate your issue before I can debug it since it's working for most common cases right now.

[keithf4]

Actually, if just prepartitioned data is your issue, pg_partman does not automatically move the data when create_parent is run. Otherwise it would be locked and unusable until it finished. Use the python script (partition_data.py) included to partition the data out and automatically create the child partitions.
Also, by default, run_maintenance() is not required for id partitioning. New partitions will automatically be made when it reaches 50% of the max for the current partition. When create_parent() is run, you should have a few partitions made that will likely be empty except for any new data inserted afterwards.

All of this is explained in the documentation file.

[Sam2002]

this is the whole schema:

CREATE TABLE partition.details
(
id serial NOT NULL,
name text,
type integer NOT NULL,
CONSTRAINT details_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE partition.details
OWNER TO postgres;

-- Trigger: details_part_trig on partition.details

-- DROP TRIGGER details_part_trig ON partition.details;

CREATE TRIGGER details_part_trig
BEFORE INSERT
ON test.details
FOR EACH ROW
EXECUTE PROCEDURE partition.details_part_trig_func();

And this is what I used for the partitioning:

SELECT partman.create_parent('partition.details', 'type', 'id-static', '2');

I read the documentation thoroughly but it didn't work in real but lets say I have a table where column "type" has 0,1,2,3,4,5,6. When I used the extension created child tables from 2 to 16 and none of the prepartitioned data were partitioned, the other issue is when new numbers are inserted no new child table will be created (18,20 etc...) no matter how many data I insert.

Last issue is why rows where type is 0 are not being inserted to child table p2?

Thank you in advance :)

[keithf4]

This seems to be an issue only when setting the partition interval to "2" for serial partitioning. Had similar issues with someone trying to set it to "1" and had resolved that it's not possible to handle that cleanly and a partitioning interval that small was unrealistic anyway. I'll see if I can fix this for "2", but again, this is a very unrealistic partitioning interval. I'm assuming you were just doing this to test it out?
Upping it to 3 allows it to work properly

keith@keith=# SELECT partman.create_parent('partition.details', 'type', 'id-static', '3');
 create_parent 
---------------

(1 row)

Time: 728.237 ms
keith@keith=# \d+ partition.details
                                                 Table "partition.details"
 Column |  Type   |                           Modifiers                            | Storage  | Stats target | Description 
--------+---------+----------------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('partition.details_id_seq'::regclass) | plain    |              | 
 name   | text    |                                                                | extended |              | 
 type   | integer | not null                                                       | plain    |              | 
Indexes:
    "details_pkey" PRIMARY KEY, btree (id)
Triggers:
    details_part_trig BEFORE INSERT ON partition.details FOR EACH ROW EXECUTE PROCEDURE partition.details_part_trig_func()
Child tables: partition.details_p0,
              partition.details_p12,
              partition.details_p3,
              partition.details_p6,
              partition.details_p9

There was no data, so the current partition is partition.details_p0, who's maximum allowed value is "2". So I ran an insert statement to insert up to "5". This caused the next, future partition to be created: partition.details_p15

keith@keith=# insert into partition.details (name, type) values ('stuff', generate_series(1,5));
INSERT 0 0
Time: 188.483 ms
keith@keith=# \d+ partition.details
                                                 Table "partition.details"
 Column |  Type   |                           Modifiers                            | Storage  | Stats target | Description 
--------+---------+----------------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('partition.details_id_seq'::regclass) | plain    |              | 
 name   | text    |                                                                | extended |              | 
 type   | integer | not null                                                       | plain    |              | 
Indexes:
    "details_pkey" PRIMARY KEY, btree (id)
Triggers:
    details_part_trig BEFORE INSERT ON partition.details FOR EACH ROW EXECUTE PROCEDURE partition.details_part_trig_func()
Child tables: partition.details_p0,
              partition.details_p12,
              partition.details_p15,
              partition.details_p3,
              partition.details_p6,
              partition.details_p9

[Sam2002]

Yes, this is my testing table, I need to test first before implementing it to my actual DB, thanks for the reply, I'll test it along with the python script and let you know the results

[keithf4]

I'm going ahead with changing the minimal interval for serial partitioning to 10 in the next version. Should avoid confusion like this and even that is still far lower an interval than I'd ever suggest partitioning a table.

[keithf4]

v1.8.2 has been released with the above mentioned fix.