PG11 NATIVE not create new table when maintenance

[roytan883]

When type is native, seems premake only work at first creation, after that the run_maintenance() only drop retention table but not create new table.
Here is my commands:

DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
CREATE EXTENSION pg_partman SCHEMA test;
GRANT ALL ON ALL TABLES IN SCHEMA test TO partman;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA test TO partman;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA test TO partman;  -- PG11+ only
GRANT ALL ON SCHEMA test TO partman;
GRANT CREATE ON DATABASE part TO partman;

CREATE TABLE test.push_msg (id serial, msg text, created timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (id, created) )  PARTITION BY RANGE (created);
SELECT test.create_parent('test.push_msg', 'created', 'native', '1 min', NULL, 1); 
UPDATE test.part_config SET
"retention" = '3 min',
"retention_schema" = NULL,
"retention_keep_table" = FALSE,
"retention_keep_index" = FALSE
WHERE "parent_table" = 'test.push_msg';

call run_maintenance_proc();

each time call run_maintenance_proc(); only remove those retention table push_msg_p2018_11_06_xxxx. But no new table created.
Eg: i created at 2018_11_06_14:30, after call run_maintenance_proc(); i got those initial tables

push_msg_p2018_11_06_1427
push_msg_p2018_11_06_1428
push_msg_p2018_11_06_1429
push_msg_p2018_11_06_1430
push_msg_p2018_11_06_1431 

But after 2 minutes, at 2018_11_06_14:32, call run_maintenance_proc(); again. only those tables left:

push_msg_p2018_11_06_1429
push_msg_p2018_11_06_1430
push_msg_p2018_11_06_1431 

After 4 minutes, at 2018_11_06_14:34, call run_maintenance_proc(); again. only one table left:

push_msg_p2018_11_06_1431 

No new table created at any time !!!

After 7 minutes, call run_maintenance_proc() got those error:

ERROR: Child table given does not exist (test.push_msg_p2018_11_06_1431)
CONTEXT: PL/pgSQL function show_partition_info(text,text,text) line 38 at RAISE
SQL statement "SELECT child_start_time FROM test.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table)"
PL/pgSQL function run_maintenance(text,boolean,boolean,boolean) line 196 at SQL statement
SQL statement "SELECT test.run_maintenance('test.push_msg', p_jobmon := 't', p_debug := 'f')"
PL/pgSQL function run_maintenance_proc(integer,boolean,boolean,boolean) line 44 at EXECUTE
DETAIL:
HINT:
CONTEXT: PL/pgSQL function run_maintenance(text,boolean,boolean,boolean) line 398 at RAISE
SQL statement "SELECT test.run_maintenance('test.push_msg', p_jobmon := 't', p_debug := 'f')"
PL/pgSQL function run_maintenance_proc(integer,boolean,boolean,boolean) line 44 at EXECUTE 

Which was strange, I did not call any other commands, but the function says table not exist. I checked the database, the test.push_msg_p2018_11_06_1431 was exist.

[roytan883]

Did i miss something config to auto create new partition tables?

[thomasboussekey]

Hello @Roytan, Are your data not inserted at all, or inserted into the wrong partition, or else? You have configured your maintenance operation to run in a `jobmon` context, all the configuration related is set? What is the value for premake for your partitioning configuration? I never used native partitioning in production.. Regards, Thomas Le mar. 6 nov. 2018 à 07:52, roytan883 <notifications@github.com> a écrit :

…

Did i miss something config to auto create new partition tables? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#237 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANrwfH1qQTL9ZmCCvMIOGx0MDqn9lVgiks5usTG7gaJpZM4YP2Bu> .

[roytan883]

@thomasboussekey Already provide all information how to reproduce this bug. Above SQL command you can just copy and run to see the result.

[keithf4]

By default, for time-based partitioning, if no new data is being inserted, no new partitions will be created. If you want new partitions to always be created despite there being no new data, set the infinite_time_partitions option in the config table to true.

I'll see if I can recreate that error you're getting.

[marcocitus]

It might make sense to make infinite_time_partitions the default for native partitioning because the partitions need to exist before data can be inserted.

[keithf4]

That is the purpose of the premake value. That should be set high enough to account for the time period that your data is being inserted. And with PG11, the default partition is always created by pg_partman for you to handle when the child table doesn't exist. If that is an important feature for you in native, I highly recommend upgrading to 11.

I have this set as the default for time because it's the only option for serial partitioning and I want the default consistent between both.

[roytan883]

@keithf4 Before use infinite_time_partitions, i have try to insert some new data at time: 2018_11_06_14:32, but all of them go to push_msg_default.

Now i test infinite_time_partitions, it works, thanks a lot.

[keithf4]

If your child table names are as they were above, that would make sense since that time period has no child table. But in normal operations, you should not be inserting data that does not have a specific child table. The default is there to catch mistakes without losing data, but if you continue to allow a lot of data to go to the default, you're defeating the entire purpose of partitioning and will see a degradation in performance over time.

It's also not quite as easy to move data out of that default with native since you cannot create any child tables that have data that exists in the default. You have to move the data out of the default, create the table, then insert it back in.

So, again, please set your premake high enough to account for your normal data operations.

[roytan883]

@keithf4 For 7 minutes error, I'm sure i did not drop any table manually. I may restart the PG11 at minutes 6.
The error is easy to reproduce, just use type: native and do not use infinite_time_partitions, after time pass the last partition table. Maybe need restart PG11.

[roytan883]

@keithf4 This just a demo for me to test partman. I'm plan to use time partition with 5 day for each in my project. So i guess premake:1 is enough for me.

[keithf4]

I would recommend leaving it at the default of 4

[roytan883]

I'm worried about premake:4 will lead some query waste performance to query in future tables. Like use 5 day each partition table to query where id=5 and time > now() - interval '1 hour'. If premake:1, it only check current and later one partition table. But if premake:4, the other 3 check partition table make no sence.

[keithf4]

4 is not enough to make any noticeable difference, especially when the tables are empty and on PG11 where true partition pruning was introduced.

[roytan883]

I just found that by explain the query, it said will check 5 partition table, scare me.
true partition pruning was it default enabled or need some config? If premake:4 not impact any performance i will use it.

[keithf4]

It's enabled by default.

[roytan883]

@keithf4 BTW, infinite_time_partitions = FALSE seem make no sence. Why it default disabled? If not auto create partition tables, all INSERT go to xxx_default when initial time passed. As you said, store data to xxx_default is not recommend.
Or it should be default TRUE on PG11 with native ? I'm using PG11 right now

[keithf4]

Please see my previous response above as to why this is the default.

Additionally, if you have normal data insertion happening, you will never run into the issue of the new table not existing. If you have no data being inserted, why should it continue creating empty, unused tables indefinitely? If you have huge gaps in data insertion periods, then at that point, the better question to ask is why is your partition interval so small that it's causing gaps?

The default encourages proper usage of partitioning.

[keithf4]

So I believe this is the same issue being reported here: #227

The user there also only encountered this while testing and not really using pg_partman in normal operating situations. So I am working on a fix for this, but it's not something you should run into as long as new data is being inserted and new partitions are being created as old ones are being dropped. It basically only happens when you're down to a single partition existing.

[keithf4]

This issue as been fixed as of v4.2.0.