Subject: Partition boundary messed up

From: Lok P <[email-masked]>
Date: Thu, 25 Jul 2024 00:54:47 +0530

Hello All,
We normally operate on UTC timezone so we normally create partitions in UTC
timezone so that each day partition starts from today's midnight UTC to
next day's midnight UTC. The script looks something like below. And also
that way reference partition tables are also created in a similar way. Say
for example the 29th august partition in parent partition reference to 29th
august child partition as FK. This was working fine even for partition
maintenance(mainly while dropping historical partitions) too without any
issue.

For one of the newly introduced partition tables, by mistake somebody
created the initial partition definition without setting the timezone to
UTC and all the partitions created are in different time zones. And as this
table refers to another partition table (which is the parent and have
partitions created in UTC timezone) the rows are spanning across two
partitions and it's breaking the partition maintenance process while we try
to drop the historical partition.

Now the issue is that the newly introduced table already has billions of
rows pumped into it spanning across 40 partitions. So is there an easy way
to just alter the partition boundary to make it UTC midnight to midnight
range?
or
The only way is to create a new table from scratch with the correct
partition boundary in UTC timezone and then move the data and then create
foreign key on that(which I believe is going to take a lot of time too)?

Another thing we noticed, it shows initial partitions having boundaries in
NON UTC (which we understand because of the missing timezone syntax) but
then suddenly the subsequent partitions are getting created UTC too, not
sure how it happened. And I believe it will create issues while rows come
into the database which falls in between these ranges? Wondering if there
is any easy way to correct this mess now? Note- This is postgres version
15+.

Below partition creation script we use:-
set timesozne='UTC';
SELECT partman.create_parent(
   p_parent_table := 'schema1.tab1',
   p_control := 'TRAN_DATE',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 90,
   p_start_partition => '2024-02-15 00:00:00'
);
UPDATE partman.part_config SET infinite_time_partitions = 'true' ,
premake=20 WHERE parent_table = 'schema1.tab1';
CALL partman.run_maintenance_proc();

Below details i fetched from  pg_class for the table which is messed up:-

Partition_name       Partition Expressions
TAB1_p2024_08_29 FOR VALUES FROM ('2024-08-29 00:00:00+05:30') TO
('2024-08-30 00:00:00+05:30')
TAB1_p2024_08_30 FOR VALUES FROM ('2024-08-30 00:00:00+05:30') TO
('2024-08-31 00:00:00+05:30')
TAB1_p2024_08_31 FOR VALUES FROM ('2024-08-31 00:00:00+05:30') TO
('2024-09-01 00:00:00+05:30')
TAB1_p2024_09_01 FOR VALUES FROM ('2024-09-01 00:00:00+05:30') TO
('2024-09-02 00:00:00+05:30')

*TAB1_p2024_09_02 FOR VALUES FROM ('2024-09-02 00:00:00+05:30') TO
('2024-09-03 00:00:00+05:30')*
*TAB1_p2024_09_03 FOR VALUES FROM ('2024-09-03 05:30:00+05:30') TO
('2024-09-04 05:30:00+05:30')*TAB1_p2024_09_04 FOR VALUES FROM ('2024-09-04
05:30:00+05:30') TO ('2024-09-05 05:30:00+05:30')
TAB1_p2024_09_05 FOR VALUES FROM ('2024-09-05 05:30:00+05:30') TO
('2024-09-06 05:30:00+05:30')
TAB1_p2024_09_06 FOR VALUES FROM ('2024-09-06 05:30:00+05:30') TO
('2024-09-07 05:30:00+05:30')

Regards
Lok

---

From: yudhi s <[email-masked]>
Date: Sat, 27 Jul 2024 14:08:11 +0530

If you were thinking of some ALTER command which will just alter the
boundaries of the incorrect partition and make it correct ,  I don't think
there exists any such. You may have to create a whole new table and run the
partman.create_parent block with the UTC time zone set so that all the
subsequent partitions will be created with correct boundaries and then move
the data into it from the existing table.

On Thu, Jul 25, 2024 at 12:55 AM Lok P <[email-masked]> wrote:

> Hello All,
> We normally operate on UTC timezone so we normally create partitions in
> UTC timezone so that each day partition starts from today's midnight UTC to
> ('2024-09-04 05:30:00+05:30')*TAB1_p2024_09_04 FOR VALUES FROM
> ('2024-09-04 05:30:00+05:30') TO ('2024-09-05 05:30:00+05:30')

---

From: Lok P <[email-masked]>
Date: Sat, 27 Jul 2024 14:17:26 +0530

Thank you. I understand that is going to take a lot of time as we
already have billions of rows in the main table spread across 40+
partitions.
Also this table is child to another parent partition table and so,  it will
take a lot of time to validate the FK constraint back for the new table.
Is there a less disruptive way(zero to minimal downtime) possible to fix
this mess?