pg_partman
pg_partman copied to clipboard
pgpartman
Allow batching partition_data_* for native partitioning (Was: partition_data_* did not move the data out of the default partition)
Ref: https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md
The partition_data_proc() can handle moving the data out of the default. However, it cannot move data in any interval smaller than the partition interval when moving data out of the DEFAULT.
I feel like I have stepped on the pit:
postgres=# \c bdc;
You are now connected to database "bdc" as user "postgres".
bdc=# select partman.check_default();
check_default
--------------------------------------------
(ictf6.ictlogtestpart_ao_default,27894963)
(ictf3.ictlogtestpart_ao_default,4711843)
(2 rows)
bdc=# \x
Expanded display is on.
bdc=# select * from partman.part_config where parent_table = 'ictf6.ictlogtestpart_ao';
-[ RECORD 1 ]--------------+-----------------------------------------
parent_table | ictf6.ictlogtestpart_ao
control | testtime
partition_type | native
partition_interval | 1 day
constraint_cols |
premake | 15
optimize_trigger | 4
optimize_constraint | 30
epoch | none
inherit_fk | t
retention | 15 days
retention_schema |
retention_keep_table | f
retention_keep_index | t
infinite_time_partitions | t
datetime_string | YYYY_MM_DD
automatic_maintenance | on
jobmon | t
sub_partition_set_full | f
undo_in_progress | f
trigger_exception_handling | f
upsert |
trigger_return_null | t
template_table | partman.template_ictf6_ictlogtestpart_ao
publications |
inherit_privileges | f
constraint_valid | t
subscription_refresh |
drop_cascade_fk | f
bdc=# \d+ ictf6.ictlogtestpart_ao
Partitioned table "ictf6.ictlogtestpart_ao"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
...
Partition key: RANGE (testtime)
Partitions: ictf6.ictlogtestpart_ao_p2022_12_27 FOR VALUES FROM ('2022-12-27 00:00:00') TO ('2022-12-28 00:00:00'),
ictf6.ictlogtestpart_ao_p2022_12_28 FOR VALUES FROM ('2022-12-28 00:00:00') TO ('2022-12-29 00:00:00'),
ictf6.ictlogtestpart_ao_p2022_12_29 FOR VALUES FROM ('2022-12-29 00:00:00') TO ('2022-12-30 00:00:00'),
ictf6.ictlogtestpart_ao_p2022_12_30 FOR VALUES FROM ('2022-12-30 00:00:00') TO ('2022-12-31 00:00:00'),
ictf6.ictlogtestpart_ao_p2022_12_31 FOR VALUES FROM ('2022-12-31 00:00:00') TO ('2023-01-01 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_01 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-01-02 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_02 FOR VALUES FROM ('2023-01-02 00:00:00') TO ('2023-01-03 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_03 FOR VALUES FROM ('2023-01-03 00:00:00') TO ('2023-01-04 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_04 FOR VALUES FROM ('2023-01-04 00:00:00') TO ('2023-01-05 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_05 FOR VALUES FROM ('2023-01-05 00:00:00') TO ('2023-01-06 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_06 FOR VALUES FROM ('2023-01-06 00:00:00') TO ('2023-01-07 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_07 FOR VALUES FROM ('2023-01-07 00:00:00') TO ('2023-01-08 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_08 FOR VALUES FROM ('2023-01-08 00:00:00') TO ('2023-01-09 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_09 FOR VALUES FROM ('2023-01-09 00:00:00') TO ('2023-01-10 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_10 FOR VALUES FROM ('2023-01-10 00:00:00') TO ('2023-01-11 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_11 FOR VALUES FROM ('2023-01-11 00:00:00') TO ('2023-01-12 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_12 FOR VALUES FROM ('2023-01-12 00:00:00') TO ('2023-01-13 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_13 FOR VALUES FROM ('2023-01-13 00:00:00') TO ('2023-01-14 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_14 FOR VALUES FROM ('2023-01-14 00:00:00') TO ('2023-01-15 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_15 FOR VALUES FROM ('2023-01-15 00:00:00') TO ('2023-01-16 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_16 FOR VALUES FROM ('2023-01-16 00:00:00') TO ('2023-01-17 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_17 FOR VALUES FROM ('2023-01-17 00:00:00') TO ('2023-01-18 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_18 FOR VALUES FROM ('2023-01-18 00:00:00') TO ('2023-01-19 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_19 FOR VALUES FROM ('2023-01-19 00:00:00') TO ('2023-01-20 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_20 FOR VALUES FROM ('2023-01-20 00:00:00') TO ('2023-01-21 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_21 FOR VALUES FROM ('2023-01-21 00:00:00') TO ('2023-01-22 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_22 FOR VALUES FROM ('2023-01-22 00:00:00') TO ('2023-01-23 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_23 FOR VALUES FROM ('2023-01-23 00:00:00') TO ('2023-01-24 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_24 FOR VALUES FROM ('2023-01-24 00:00:00') TO ('2023-01-25 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_25 FOR VALUES FROM ('2023-01-25 00:00:00') TO ('2023-01-26 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_26 FOR VALUES FROM ('2023-01-26 00:00:00') TO ('2023-01-27 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_27 FOR VALUES FROM ('2023-01-27 00:00:00') TO ('2023-01-28 00:00:00'),
ictf6.ictlogtestpart_ao_p2023_01_28 FOR VALUES FROM ('2023-01-28 00:00:00') TO ('2023-01-29 00:00:00'),
ictf6.ictlogtestpart_ao_default DEFAULT
bdc=# select now();
-[ RECORD 1 ]----------------------
now | 2023-01-29 15:24:08.578172+08
bdc=# select count(1) from ictf6.ictlogtestpart_ao where testtime >= '2023-01-30 00:00:00' or testtime < '2022-12-27 00:00:00';
-[ RECORD 1 ]
count | 0
bdc=# call partman.partition_data_proc(p_parent_table:='ictf6.ictlogtestpart_ao');
ERROR: updated partition constraint for default partition would be violated by some row
CONTEXT: SQL statement "ALTER TABLE ictf6.ictlogtestpart_ao ATTACH PARTITION ictf6.ictlogtestpart_ao_p2023_01_29 FOR VALUES FROM ('2023-01-29 00:00:00+08') TO ('2023-01-30 00:00:00+08')"
PL/pgSQL function partman.create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
SQL statement "SELECT partman.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze)"
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 277 at PERFORM
DETAIL:
HINT:
CONTEXT: PL/pgSQL function partman.create_partition_time(text,timestamp with time zone[],boolean,text) line 509 at RAISE
SQL statement "SELECT partman.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze)"
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 277 at PERFORM
Enviroment:
- pg_partman version: 4.4.0 - 4.6.2
- postgres version: 12.x
Say your partition set is set to a monthly interval. If you're trying to move data out of the default partition using the partition_data_*() functions or procedures, you cannot feed it an interval parameter less than 1 month. This is because you cannot have data in the default that matches the constraint of any other child tables in the partition set.
Is it feasible to first migrate the data in the default partition to a temporary table instead of a sub-partition?
That is actually what the partition_data_*() functions are doing in the case of native partitioning.
Perhaps I could have a mode that only works with the partition_data_proc() procedure that moves the data in batches between the default -> temp and then again temp -> child table. I didn't initially do that because my goal was to make the data migration invisible to any users of the table without throwing any errors about missing data during the migration.
I see you changed the title of this issue. Did you run into an issue where the procedure didn't actually move data?
Yes. The problem is that if the data is partitioned by daily, the default partition has already written today's data, and partition_data_proc() cannot create today's partition. I need to create a temporary table to perform data migration.
partition_data_proc() should automatically make the temp table and do all that for you, so I'm concerned if this is happening.
Can you give me a reproducible use-case where this is happening so I can see what may be wrong?
Do I need to add an exclusive lock first?
I suspect that data is written during the maintenance process?
You don't need to manage any locks. The procedure should take care of what needs to be done.
Just to get come clarity here, data for "today" should never be going into the default. Nor for any data in the very near future. And the child partition for "today" should never be missing during the same day its for.That is what the premake value is for. It defaults to 4 so there are at least 4 partitions ahead of "now()" that are created during maintenance with time partitioning. That also avoids contention during maintenance since it should be creating child tables that are not currently needed. The premake value should be set high enough to account for your normally expected window of data. This will avoid any data going into the default outside of edge cases you're not expecting.
So, the error for maintenance not being able to create a partition should hopefully happen well before "today" and would mean that your application inserted data further into the future than you anticipated. Yes, that means maintenance is now basically blocked until that is fixed. But hopefully you've got some monitoring in place to watch for that (use the check_default() function to watch for data going into the default).
Once that alert goes off, you should be able to use partition_data_proc() to move that data out of the default into a relevant child table, if and only if, that data is valid. Only you can judge that. If it's not valid, just delete it or archive it off somewhere else outside of the partition set.
Just checking to see if you're still having any issues with partitioning data out of the default
Changing this to be marked as Feature Request due to seeing about adding a mode to partition_data_proc() so that it can work in batches.
This is going to require a bigger re-design than I can do at the moment, so setting for 5.x redesign milestone. Currently the temp table is set to ON COMMIT DROP and for good reason right now. Will likely look at changing to use a real UNLOGGED table instead to be able to persist across transactions or even sessions to allow it to resume if failed.
partition_data_proc() should automatically make the temp table and do all that for you, so I'm concerned if this is happening.
Can you give me a reproducible use-case where this is happening so I can see what may be wrong?
I added it to the issue description.
Solution:
BEGIN;
CREATE TABLE ictf6.ictlogtestpart_ao_p2023_01_29 (LIKE ictf6.ictlogtestpart_ao);
INSERT into ictf6.ictlogtestpart_ao_p2023_01_29 SELECT * FROM ictf6.ictlogtestpart_ao_default;
TRUNCATE TABLE ictf6.ictlogtestpart_ao_default;
ALTER TABLE ictf6.ictlogtestpart_ao ATTACH PARTITION ictf6.ictlogtestpart_ao_p2023_01_29
FOR VALUES FROM ('2023-01-29 00:00:00') TO ('2023-01-30 00:00:00');
COMMIT;
SELECT partman.run_maintenance();
Apologies for the delays. I haven't had a chance to get started on this one yet. I'd planned it for the 5.1 release, so will try and see if I can get it in there. If not, I will definitely try for this in 5.2.
Thanks for still expressing interest in the feature! Helps me prioritize it a little more.
FYI, I ran into similar problem. Basically what happened in my case is that I ran initial partition based on day/time then realized after the fact that you need maintenance running on a cron to stay ahead and create partitions ahead of time (or some other mechanism to do that). So i created with the default 4 days lookahead, but came back to implement my run maintenance cronjob after the fact, at which point 4 days had passed and pg_partman, not having a partition set up, began inserting data into the default partition instead. So then run_maintenance call failed with the error above, because data existed in the default partition. So I needed to move data from default partition table to partitioned table. But to start doing that, I needed to empty out default partition table or else partition_data_proc would not run. Something like this worked for me:
SET work_mem = '10GB';
CREATE TABLE temp_metadata AS SELECT * FROM public.metadata LIMIT 0;
INSERT INTO public.temp_metadata
SELECT * FROM public.metadata_default;
---------------------------------------------
-- verify that your temp table got populated before running this
truncate table public.metadata_default;
CALL partman.partition_data_proc(
p_parent_table := 'public.metadata'
, p_batch := 200
, p_interval := '1 day'
, p_source_table := 'public.temp_metadata'
);
---------------------------------------------
DROP table public.temp_metadata
---------------------------------------------
SET work_mem = '10GB';
CREATE TABLE temp_log AS SELECT * FROM public.log LIMIT 0;
INSERT INTO public.temp_log
SELECT * FROM public.log_default;
---------------------------------------------
-- verify that your temp table got populated before running this
truncate table public.log_default;
---------------------------------------------
SET work_mem = '10GB';
CALL partman.partition_data_proc(
p_parent_table := 'public.log'
, p_batch := 200
, p_interval := '1 day'
, p_source_table := 'public.temp_log'
);
---------------------------------------------
DROP table public.temp_log
It would be nice if partition_data_proc handled that under the hood for you. Thanks for pg_partman :)
partition_data_proc can move data out of the default. That's what it does unless you give it another table as the source.
Interesting, that did not work for me (can share error when I’m at a machine again) but as I’m using 4.5.1 it’s probably best to look at upgrading first. Thanks for the response!
So I think the problem is that you set public.temp_metadata with the p_source_table variable. At least in the example you gave. If you don't set p_source_table, it uses the default table (native partitioning) or the parent table (trigger-based partitioning on versions prior to 5.x) as the source.
I should probably put in a check to make sure people aren't setting the default as the source table like this. It's probably reinserting the data right back into the default again and failing when it then tries to make the new child table.
That would make total sense! I copy pasted that argument from my initial load which does not use the default table. Perhaps a simple solution is to check if end users are specifying the default table to just bypass using that argument.
In my case I was using native partitioning if it makes a difference