MIME-Version: 1.0
References: <CAKna9VZ=b6f-zei3RY4uiKq7xjt0Ox_oftySPRq8t0YRWDN+4g@mail.gmail.com>
In-Reply-To: <CAKna9VZ=b6f-zei3RY4uiKq7xjt0Ox_oftySPRq8t0YRWDN+4g@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 27 Jul 2024 14:08:11 +0530
Message-ID: <CAEzWdqeWG2Ld7Y=_kdjBL0CdbRCSGzt+L4p3vzmHoNsxDk+POw@mail.gmail.com>
Subject: Re: Partition boundary messed up
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f7b674061e368c57"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqeWG2Ld7Y%3D_kdjBL0CdbRCSGzt%2BL4p3vzmHoNsxDk%2BPOw%40mail.gmail.com>
Precedence: bulk

--000000000000f7b674061e368c57
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM Lok P <loknath.73@gmail.com> 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
> 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. Sa=
y
> for example the 29th august partition in parent partition reference to 29=
th
> 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 th=
is
> 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 t=
ry
> 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 wa=
y
> 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 i=
n
> 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=3D'UTC';
> SELECT partman.create_parent(
>    p_parent_table :=3D 'schema1.tab1',
>    p_control :=3D 'TRAN_DATE',
>    p_type :=3D 'native',
>    p_interval :=3D '1 day',
>    p_premake :=3D 90,
>    p_start_partition =3D> '2024-02-15 00:00:00'
> );
> UPDATE partman.part_config SET infinite_time_partitions =3D 'true' ,
> premake=3D20 WHERE parent_table =3D '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
>

--000000000000f7b674061e368c57
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>If you were thinking of some ALTER command which will=
 just alter the boundaries=C2=A0of the incorrect=C2=A0partition and make it=
 correct=C2=A0,=C2=A0 I don&#39;t think there exists any such. You may have=
 to create a whole new table and run the partman.create_parent block with t=
he UTC time zone=C2=A0set so that all the subsequent partitions will be cre=
ated with correct=C2=A0boundaries and then move the data into it from the e=
xisting=C2=A0table.<br></div><div><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, Jul 25, 2024 at 12:55=E2=80=AFAM =
Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr">Hello All,<br>We normally operate on UTC timezone so we normall=
y create partitions in UTC timezone so that each day partition starts from =
today&#39;s midnight UTC to next day&#39;s midnight UTC. The script looks s=
omething like below. And also that way reference partition tables are also =
created in a similar way. Say for example the 29th august partition in pare=
nt partition reference to 29th august child partition as FK. This was worki=
ng fine even for partition maintenance(mainly while dropping historical par=
titions) too without any issue.<br><br>For one of the newly introduced part=
ition tables, by mistake somebody created the initial partition definition =
without setting the timezone to UTC and all the partitions created are in d=
ifferent time zones. And as this table refers to another partition table (w=
hich is the parent and have partitions created in UTC timezone) the rows ar=
e spanning across two partitions and it&#39;s breaking the partition mainte=
nance process while we try to drop the historical partition.<br><br>Now the=
 issue is that the newly introduced table already has billions of rows pump=
ed into it spanning across 40 partitions. So is there an easy way to just a=
lter the partition boundary to make it UTC midnight to midnight range? <br>=
or <br>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 f=
oreign key on that(which I believe is going to take a lot of time too)?<br>=
<br>Another thing we noticed, it shows initial partitions having boundaries=
 in NON UTC (which we understand because of the missing timezone syntax) bu=
t 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 i=
nto 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+.=
<div><div><br>Below partition creation script we use:-<div><span style=3D"f=
ont-family:monospace">set timesozne=3D&#39;UTC&#39;;</span></div><div><font=
 face=3D"monospace">SELECT partman.create_parent(<br>=C2=A0 =C2=A0p_parent_=
table :=3D &#39;schema1.tab1&#39;,<br>=C2=A0 =C2=A0p_control :=3D &#39;TRAN=
_DATE&#39;,<br>=C2=A0 =C2=A0p_type :=3D &#39;native&#39;,<br>=C2=A0 =C2=A0p=
_interval :=3D &#39;1 day&#39;,<br>=C2=A0 =C2=A0p_premake :=3D 90,<br>=C2=
=A0 =C2=A0p_start_partition =3D&gt; &#39;2024-02-15 00:00:00&#39;<br>);<br>=
UPDATE partman.part_config SET infinite_time_partitions =3D &#39;true&#39; =
, premake=3D20 WHERE parent_table =3D &#39;schema1.tab1&#39;;<br>CALL partm=
an.run_maintenance_proc();</font><br><br>Below details i fetched from=C2=A0=
 pg_class for the table which is messed up:-<br><br><font face=3D"monospace=
">Partition_name =C2=A0 =C2=A0 =C2=A0 Partition Expressions<br>TAB1_p2024_0=
8_29	FOR VALUES FROM (&#39;2024-08-29 00:00:00+05:30&#39;) TO (&#39;2024-08=
-30 00:00:00+05:30&#39;)<br>TAB1_p2024_08_30	FOR VALUES FROM (&#39;2024-08-=
30 00:00:00+05:30&#39;) TO (&#39;2024-08-31 00:00:00+05:30&#39;)<br>TAB1_p2=
024_08_31	FOR VALUES FROM (&#39;2024-08-31 00:00:00+05:30&#39;) TO (&#39;20=
24-09-01 00:00:00+05:30&#39;)<br>TAB1_p2024_09_01	FOR VALUES FROM (&#39;202=
4-09-01 00:00:00+05:30&#39;) TO (&#39;2024-09-02 00:00:00+05:30&#39;)<br><b=
>TAB1_p2024_09_02	FOR VALUES FROM (&#39;2024-09-02 00:00:00+05:30&#39;) TO =
(&#39;2024-09-03 00:00:00+05:30&#39;)<br></b><b>TAB1_p2024_09_03	FOR VALUES=
 FROM (&#39;2024-09-03 05:30:00+05:30&#39;) TO (&#39;2024-09-04 05:30:00+05=
:30&#39;)<br></b>TAB1_p2024_09_04	FOR VALUES FROM (&#39;2024-09-04 05:30:00=
+05:30&#39;) TO (&#39;2024-09-05 05:30:00+05:30&#39;)<br>TAB1_p2024_09_05	F=
OR VALUES FROM (&#39;2024-09-05 05:30:00+05:30&#39;) TO (&#39;2024-09-06 05=
:30:00+05:30&#39;)<br>TAB1_p2024_09_06	FOR VALUES FROM (&#39;2024-09-06 05:=
30:00+05:30&#39;) TO (&#39;2024-09-07 05:30:00+05:30&#39;)</font><br><div><=
font face=3D"monospace"><br></font></div><div><font face=3D"monospace">Rega=
rds</font></div><div><font face=3D"monospace">Lok</font></div></div></div><=
/div></div></blockquote><div><br></div><div><br></div><div>=C2=A0</div></di=
v></div>

--000000000000f7b674061e368c57--