Missing Records while trasfer data from main table to children

[Thavarajan]

Good evening Team,
it's a wonderful extension, when I try to implement this in our existing DB,parent table have approximately 4900000 records,

CALL partman.partition_data_proc( p_parent_table := 'public.tickets' , p_loop_count := 2000 , p_interval := '1000' , p_source_table := 'public.old_tickets' );

During the migration some of the records are missing, I don't know, when I try to do a count diff there are merely 6 or 7 records missing

am I missing something am I giving too many loops?

[keithf4]

I've not run across this issue nor had anyone report it yet. Without a working example or seeing which records are missing, it would be hard to debug this problem.

[keithf4]

You cannot set the loop value too high. As soon as it's moved all the data, it will stop. I frequently set it to a really high number myself just to make sure it does enough. Perhaps it wasn't enough? Is it actually running 2000 loops? If so, it may not have gotten all the data. Is there any data still left in the source table?

[keithf4]

Can you share the contents of the part_config table for this partition set? It seems to be moving a very small amount of records in each loop.

[keithf4]

Also share the output of \d+ public.tickets

[Thavarajan]

Sorry for the delayed reply, as I was not able to share data over the public domain, I need to get permission from my seniors,
following are the data of the part config

--------------------------+---------------------------------------------+
parent_table              |public.ticket                                |
control                   |ticket_date                                  |
partition_interval        |3 mons                                       |
partition_type            |range                                        |
premake                   |4                                            |
automatic_maintenance     |on                                           |
template_table            |partman.template_public_ticket               |
retention                 |                                             |
retention_schema          |                                             |
retention_keep_index      |true                                         |
retention_keep_table      |true                                         |
epoch                     |none                                         |
constraint_cols           |NULL                                         |
optimize_constraint       |30                                           |
infinite_time_partitions  |false                                        |
datetime_string           |YYYYMMDD                                     |
jobmon                    |true                                         |
sub_partition_set_full    |false                                        |
undo_in_progress          |false                                        |
inherit_privileges        |false                                        |
constraint_valid          |true                                         |
ignore_default_data       |true                                         |
default_table             |true                                         |
date_trunc_interval       |                                             |
maintenance_order         |                                             |
retention_keep_publication|false                                        |
maintenance_last_run      |                                             |

since I am not able to share the schema details of my table I will share the partition list

Partitions: ticket_p19691201 FOR VALUES FROM ('1969-12-01 00:00:00') TO ('1970-03-01 00:00:00'),
            ticket_p20190301 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-06-01 00:00:00'),
            ticket_p20190601 FOR VALUES FROM ('2019-06-01 00:00:00') TO ('2019-09-01 00:00:00'),
            ticket_p20230601 FOR VALUES FROM ('2023-06-01 00:00:00') TO ('2023-09-01 00:00:00'),
            ticket_p20230901 FOR VALUES FROM ('2023-09-01 00:00:00') TO ('2023-12-01 00:00:00'),
            ticket_p20231201 FOR VALUES FROM ('2023-12-01 00:00:00') TO ('2024-03-01 00:00:00'),
            ticket_p20240301 FOR VALUES FROM ('2024-03-01 00:00:00') TO ('2024-06-01 00:00:00'),
            ticket_p20240601 FOR VALUES FROM ('2024-06-01 00:00:00') TO ('2024-09-01 00:00:00'),
            ticket_p20240901 FOR VALUES FROM ('2024-09-01 00:00:00') TO ('2024-12-01 00:00:00'),
            ticket_p20241201 FOR VALUES FROM ('2024-12-01 00:00:00') TO ('2025-03-01 00:00:00'),
            ticket_p20250301 FOR VALUES FROM ('2025-03-01 00:00:00') TO ('2025-06-01 00:00:00'),
            ticket_p20250601 FOR VALUES FROM ('2025-06-01 00:00:00') TO ('2025-09-01 00:00:00'),
            ticket_default DEFAULT

[keithf4]

Ok, things look normal there. Just going back and looking at your partiton_data_proc() call, you've got an interval size of 1000, but you're partitioned by time with a 3 month interval. So it's interpreting your interval value as a number of seconds: 1000 sec = 16 minutes. So it's only moving an amount of data for a 16 minute interval of time each loop and likely not moving everything within 2000 loops.

Instead try setting the p_interval value to a time interval value that would equate to about how many records you want to move in each transaction loop. Maybe try 1 day and see how long that takes for a single batch. If you're trying to keep your transaction lengths shorter and that takes too long. Or if each batch is still going super quick and you want to speed up the partitioning time overall, increase the interval. Can keep adjusting that interval until you find what works best.

[keithf4]

Also, if you don't set p_loop_count it will just partition the entire thing and stop when it's done. If you're trying to keep it under control so it doesn't just keep running during a busy time, then setting p_loop_count can give you a predictable amount of loops that it will run so you'll know when it stops. But if it didn't move everything out, you'll have to call it several times.