MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 8 Jun 2024 15:01:32 +0530
Message-ID: <CAEzWdqdHXZRpOq75Tkj-4JL1S_Q3vtL-9KJasubhhELAvapRuA@mail.gmail.com>
Subject: Question on pg_cron
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009bf5d4061a5d95e4"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqdHXZRpOq75Tkj-4JL1S_Q3vtL-9KJasubhhELAvapRuA%40mail.gmail.com>
Precedence: bulk

--0000000000009bf5d4061a5d95e4
Content-Type: text/plain; charset="UTF-8"

Hello All,

We have around 10 different partition tables for which the partition
maintenance is done using pg_partman extension. These tables have foreign
key dependency between them.  We just called partman.run_maintanance_proc()
through pg_cron without any parameters and it was working fine. So we can
see only one entry in the cron.job table. And it runs daily once.

It was all working fine and we were seeing the historical partition being
dropped and new partitions being created without any issue. But suddenly we
started seeing, its getting failed with error "ERROR: can not drop
schema1.tab1_part_p2023_12_01 because other objects depend on it"

Then we realized , it may be the case that it's trying to run the partition
maintenance for the Parent partition table first before the child partition
table. So not sure how pg_partman handles the ordering of tables while
doing the partition maintenance as we don't see any parameter to drive the
ordering of the partition maintenance in part_config and we were under the
impression pg_partman will take care of the sequence of partition
maintenance automatically.

So want to understand if anybody encountered such issues?

And to handle the above issue , we are planning to call the partition
maintenance of each of the TABLE by passing the table name explicitly to
the run_maintanance_proc(), something as below. Is this advisable?

SELECT cron.unschedule('run_maintenance_proc');
SELECT cron.schedule(
    'daily_partition_maintenance',
    '0 2 * * *',
    $$
    DO $$
    BEGIN
        -- Run maintenance for child tables first
        PERFORM partman.run_maintenance_proc('schema1.child_table1');
        PERFORM partman.run_maintenance_proc('schema1.child_table2');
        -- Add more child tables as needed

        -- Run maintenance for parent tables next
        PERFORM partman.run_maintenance_proc('schema1.parent_table1');
        PERFORM partman.run_maintenance_proc('schema1.parent_table2');
        -- Add more parent tables as needed
    END;
    $$;
$$
);


*Or else *
create a function like below and then call/schedule it through pg_cron

CREATE OR REPLACE FUNCTION run_partition_maintenance()
RETURNS void AS $$
BEGIN
    -- Run maintenance for child tables first
    PERFORM partman.run_maintenance_proc('schema1.child_table1');
    PERFORM partman.run_maintenance_proc('schema1.child_table2');
    -- Add more child tables as needed

    -- Run maintenance for parent tables next
    PERFORM partman.run_maintenance_proc('schema1.parent_table1');
    PERFORM partman.run_maintenance_proc('schema1.parent_table2');
    -- Add more parent tables as needed
END;
$$ LANGUAGE plpgsql;

SELECT cron.unschedule('run_maintenance_proc');

SELECT cron.schedule(
    'daily_partition_maintenance',
    '0 2 * * *',
    'CALL run_partition_maintenance()'
);

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

<div dir=3D"ltr">Hello All,<div><br>We have around 10 different partition t=
ables for which the partition maintenance is done using pg_partman extensio=
n. These tables have foreign key dependency between them.=C2=A0 We just cal=
led partman.run_maintanance_proc() through pg_cron without any parameters a=
nd it was working fine. So we can see only one entry in the cron.job table.=
 And it runs daily once.<br><br>It was all working fine and we were seeing =
the historical partition being dropped and new partitions being created wit=
hout any issue. But suddenly we started seeing, its getting failed with err=
or &quot;ERROR: can not drop schema1.tab1_part_p2023_12_01 because other ob=
jects depend on it&quot;<br><br>Then we realized , it may be the case that =
it&#39;s trying to run the partition maintenance for the Parent partition t=
able first before the child partition table. So not sure how pg_partman han=
dles the ordering of tables while doing the partition maintenance as we don=
&#39;t see any parameter to drive the ordering of the partition maintenance=
 in part_config and we were under the impression pg_partman will take care =
of the sequence of partition maintenance automatically.<br><br>So want to u=
nderstand if anybody encountered such issues?<br><br>And to handle the abov=
e issue , we are planning to call the partition maintenance of each of the =
TABLE by passing the table name explicitly to the run_maintanance_proc(), s=
omething as below. Is this advisable?<br><br><font face=3D"monospace">SELEC=
T cron.unschedule(&#39;run_maintenance_proc&#39;);<br>SELECT cron.schedule(=
<br>=C2=A0 =C2=A0 &#39;daily_partition_maintenance&#39;,<br>=C2=A0 =C2=A0 &=
#39;0 2 * * *&#39;,<br>=C2=A0 =C2=A0 $$<br>=C2=A0 =C2=A0 DO $$<br>=C2=A0 =
=C2=A0 BEGIN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Run maintenance for child ta=
bles first<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 PERFORM partman.run_maintenance_p=
roc(&#39;schema1.child_table1&#39;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 PERFORM=
 partman.run_maintenance_proc(&#39;schema1.child_table2&#39;);<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 -- Add more child tables as needed<br><br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -- Run maintenance for parent tables next<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 PERFORM partman.run_maintenance_proc(&#39;schema1.parent_=
table1&#39;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 PERFORM partman.run_maintenanc=
e_proc(&#39;schema1.parent_table2&#39;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- =
Add more parent tables as needed<br>=C2=A0 =C2=A0 END;<br>=C2=A0 =C2=A0 $$;=
<br>$$<br>);<br><br><br><b><font size=3D"4">Or else <br></font></b><br>crea=
te a function like below and then call/schedule it through pg_cron<br><br>C=
REATE OR REPLACE FUNCTION run_partition_maintenance()<br>RETURNS void AS $$=
<br>BEGIN<br>=C2=A0 =C2=A0 -- Run maintenance for child tables first<br>=C2=
=A0 =C2=A0 PERFORM partman.run_maintenance_proc(&#39;schema1.child_table1&#=
39;);<br>=C2=A0 =C2=A0 PERFORM partman.run_maintenance_proc(&#39;schema1.ch=
ild_table2&#39;);<br>=C2=A0 =C2=A0 -- Add more child tables as needed<br><b=
r>=C2=A0 =C2=A0 -- Run maintenance for parent tables next<br>=C2=A0 =C2=A0 =
PERFORM partman.run_maintenance_proc(&#39;schema1.parent_table1&#39;);<br>=
=C2=A0 =C2=A0 PERFORM partman.run_maintenance_proc(&#39;schema1.parent_tabl=
e2&#39;);<br>=C2=A0 =C2=A0 -- Add more parent tables as needed<br>END;<br>$=
$ LANGUAGE plpgsql;<br><br>SELECT cron.unschedule(&#39;run_maintenance_proc=
&#39;);<br><br>SELECT cron.schedule(<br>=C2=A0 =C2=A0 &#39;daily_partition_=
maintenance&#39;,<br>=C2=A0 =C2=A0 &#39;0 2 * * *&#39;,<br>=C2=A0 =C2=A0 &#=
39;CALL run_partition_maintenance()&#39;<br>);</font><br></div></div>

--0000000000009bf5d4061a5d95e4--