MIME-Version: 1.0
References: <CAEzWdqdHXZRpOq75Tkj-4JL1S_Q3vtL-9KJasubhhELAvapRuA@mail.gmail.com>
 <CANzqJaD8iuULuUOUj9LXoGKBHAL1miSPsQ8jEV9UUYf3EDAaqw@mail.gmail.com> <CAEzWdqc9XGdySv0y0CU16Fi+k1x49Z4PqQH6r9gvvaxuuVOhrg@mail.gmail.com>
In-Reply-To: <CAEzWdqc9XGdySv0y0CU16Fi+k1x49Z4PqQH6r9gvvaxuuVOhrg@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Sun, 9 Jun 2024 11:57:11 +0530
Message-ID: <CAD=mzVXXjR7Ur4+YaRgd-QeYhtNVqkmHtepj-69VS8SJrUMbvg@mail.gmail.com>
Subject: Re: Question on pg_cron
To: yudhi s <learnerdatabase99@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003e123b061a6f2076"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVXXjR7Ur4%2BYaRgd-QeYhtNVqkmHtepj-69VS8SJrUMbvg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jun 8, 2024 at 10:05=E2=80=AFPM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

>
>
> 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.=
com>
>> wrote:
>>
>>> Hello All,
>>>
>>> We have around 10 different partition tables for which the partition
>>> maintenance is done using pg_partman extension. These tables have forei=
gn
>>> key dependency between them.  We just called partman.run_maintanance_pr=
oc()
>>> through pg_cron without any parameters and it was working fine. So we c=
an
>>> 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 n=
ot
>>> 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 strategie=
s,
> should we follow, please advise?
>

 I believe, You should log this as an issue in the pg_partman open source
project.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jun 8, 2024 at 10:05=E2=80=AFPM y=
udhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99=
@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div><br><br><div cl=
ass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 8 Jun, 20=
24, 9:53 pm Ron Johnson, &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" tar=
get=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><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"><div dir=3D"ltr">On =
Sat, Jun 8, 2024 at 5:31=E2=80=AFAM yudhi s &lt;<a href=3D"mailto:learnerda=
tabase99@gmail.com" rel=3D"noreferrer" target=3D"_blank">learnerdatabase99@=
gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello All,<div><br>We =
have around 10 different partition tables for which the partition maintenan=
ce is done using pg_partman extension. These tables have foreign key depend=
ency between them.=C2=A0 We just called partman.run_maintanance_proc() thro=
ugh pg_cron without any parameters and it was working fine. So we can see o=
nly 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 without any issue. But suddenly we started=
 seeing, its getting failed with error &quot;ERROR: can not drop schema1.ta=
b1_part_p2023_12_01 because other objects depend on it&quot;<br></div></div=
></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><di=
v dir=3D"auto">No version change, but we updated the part_config to set pre=
make from 30 to 60 for all the tables. But not sure how that impacted this =
behavior.=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">However,=
 do you think, we should better control the order of execution rather letti=
ng postgres to decide it&#39;s own, considering there is no such parameters=
 for this ordering in part_config? And in that case which approach should w=
e use out of the two i mentioned. Or any other strategies, should we follow=
, please advise?=C2=A0</div></div></blockquote><div><br></div><div>=C2=A0I =
believe, You should log this as an issue in the pg_partman open source=C2=
=A0 project.</div></div></div>

--0000000000003e123b061a6f2076--