MIME-Version: 1.0
References: <CAEzWdqdHXZRpOq75Tkj-4JL1S_Q3vtL-9KJasubhhELAvapRuA@mail.gmail.com>
 <CANzqJaD8iuULuUOUj9LXoGKBHAL1miSPsQ8jEV9UUYf3EDAaqw@mail.gmail.com>
In-Reply-To: <CANzqJaD8iuULuUOUj9LXoGKBHAL1miSPsQ8jEV9UUYf3EDAaqw@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 8 Jun 2024 22:04:48 +0530
Message-ID: <CAEzWdqc9XGdySv0y0CU16Fi+k1x49Z4PqQH6r9gvvaxuuVOhrg@mail.gmail.com>
Subject: Re: Question on pg_cron
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000041c923061a637f92"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqc9XGdySv0y0CU16Fi%2Bk1x49Z4PqQH6r9gvvaxuuVOhrg%40mail.gmail.com>
Precedence: bulk

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

On Sat, 8 Jun, 2024, 9:53 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Sat, Jun 8, 2024 at 5:31=E2=80=AFAM yudhi s <learnerdatabase99@gmail.c=
om>
> wrote:
>
>> Hello All,
>>
>> We have around 10 different partition tables for which the partition
>> maintenance is done using pg_partman extension. These tables have foreig=
n
>> key dependency between them.  We just called partman.run_maintanance_pro=
c()
>> through pg_cron without any parameters and it was working fine. So we ca=
n
>> 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 bein=
g
>> 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"
>>
>
> Have you changed version lately of PG, pg_cron or pg_partman?  Or maybe
> what pg_cron or pg_partman depends on?
>

No version change, but we updated the part_config to set premake from 30 to
60 for all the tables. But not sure how that impacted this behavior.

However, do you think, we should better control the order of execution
rather letting postgres to decide it's own, considering there is no such
parameters for this ordering in part_config? And in that case which
approach should we use out of the two i mentioned. Or any other strategies,
should we follow, please advise?

>

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Sat, 8 Jun, 2024, 9:53 pm Ron Johnson, &lt;<a href=
=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;borde=
r-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">O=
n Sat, Jun 8, 2024 at 5:31=E2=80=AFAM yudhi s &lt;<a href=3D"mailto:learner=
database99@gmail.com" target=3D"_blank" rel=3D"noreferrer">learnerdatabase9=
9@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello All,<div><br>W=
e have around 10 different partition tables for which the partition mainten=
ance is done using pg_partman extension. These tables have foreign key depe=
ndency between them.=C2=A0 We just called partman.run_maintanance_proc() th=
rough 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.<br><br>It wa=
s all working fine and we were seeing the historical partition being droppe=
d and new partitions being created without any issue. But suddenly we start=
ed seeing, its getting failed with error &quot;ERROR: can not drop schema1.=
tab1_part_p2023_12_01 because other objects depend on it&quot;<br></div></d=
iv></blockquote><div>=C2=A0</div><div>Have you changed version lately of PG=
, pg_cron or pg_partman?=C2=A0 Or maybe what pg_cron or pg_partman depends =
on?</div></div></div></blockquote></div></div><div dir=3D"auto"><br></div><=
div dir=3D"auto">No version change, but we updated the part_config to set p=
remake from 30 to 60 for all the tables. But not sure how that impacted thi=
s behavior.=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">Howeve=
r, do you think, we should better control the order of execution rather let=
ting postgres to decide it&#39;s own, considering there is no such paramete=
rs for this ordering in part_config? And in that case which approach should=
 we use out of the two i mentioned. Or any other strategies, should we foll=
ow, please advise?=C2=A0</div><div dir=3D"auto"><div class=3D"gmail_quote">=
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--00000000000041c923061a637f92--