MIME-Version: 1.0
References: <aRiU72GYiytZUyQv@disp.intra.daemon.contact>
In-Reply-To: <aRiU72GYiytZUyQv@disp.intra.daemon.contact>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 15 Nov 2025 10:28:18 -0500
Message-ID: <CANzqJaD-J8kSsmvUC+9W5hix8WtKkvhr4UGDKggwheRusvVRtg@mail.gmail.com>
Subject: Re: failure to drop table due to pg_temp_7 schema
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001a283b0643a3c421"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD-J8kSsmvUC%2B9W5hix8WtKkvhr4UGDKggwheRusvVRtg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Nov 15, 2025 at 10:00=E2=80=AFAM Peter 'PMc' Much <
pmc@citylink.dinoex.sub.org> wrote:

>
> Hi,
>   trying to unload (and then reload) a development application,
> failed with this error:
>
> fin(dev)> Que.migrate! version: 0
> ERROR:  cannot drop table que_jobs because other objects depend on it
> (PG::DependentObjectsStillExist)
> DETAIL:  function pg_temp_7.lock_and_update_priorities(jsonb,que_jobs)
> depends on type que_jobs
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
>
> The routine was trying to remove all database objects in the order
> they were formerly created,


In the *REVERSE* order they were created?

[snip]


> I would rather figure out what actually went wrong (and then probably
> fix it for the future).
>
> So I started to investigate. Enabling "System Objects" in pgadmin4,
> I find a vast amount of pg_temp_### schemas, and therein I actually
> find the offending object - it indeed contains some stuff the Que
> software would probably use.
>
> Then, trying to figure out how this is supposed to be cleaned up,
> I find this article by subject matter expert Laurenz Albe:
> https://stackoverflow.com/a/79693897
>
>     Temporary tables are automatically removed when the database
>     session terminates. Consequently, your users are running long
>     database sessions.
>
> Sadly, this does not make much sense to me, because there are
> (currently) no sessions on the database (checked with 'ps ax').
>

Abnormal session termination is the typical reason for them to hang around.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Nov 15, 2025 at 10:00=E2=80=AFAM =
Peter &#39;PMc&#39; Much &lt;<a href=3D"mailto:pmc@citylink.dinoex.sub.org"=
>pmc@citylink.dinoex.sub.org</a>&gt; wrote:</div><div class=3D"gmail_quote =
gmail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><b=
r>
Hi,<br>
=C2=A0 trying to unload (and then reload) a development application,<br>
failed with this error:<br>
<br>
fin(dev)&gt; Que.migrate! version: 0<br>
ERROR:=C2=A0 cannot drop table que_jobs because other objects depend on it =
(PG::DependentObjectsStillExist)<br>
DETAIL:=C2=A0 function pg_temp_7.lock_and_update_priorities(jsonb,que_jobs)=
 depends on type que_jobs<br>
HINT:=C2=A0 Use DROP ... CASCADE to drop the dependent objects too.<br>
<br>
<br>
The routine was trying to remove all database objects in the order<br>
they were formerly created, </blockquote><div><br></div><div>In the <b>REVE=
RSE</b>=C2=A0order they were created?</div><div><br></div><div>[snip]=C2=A0=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I =
would rather figure out what actually went wrong (and then probably<br>
fix it for the future).<br>
<br>
So I started to investigate. Enabling &quot;System Objects&quot; in pgadmin=
4,<br>
I find a vast amount of pg_temp_### schemas, and therein I actually<br>
find the offending object - it indeed contains some stuff the Que<br>
software would probably use.<br>
<br>
Then, trying to figure out how this is supposed to be cleaned up,<br>
I find this article by subject matter expert Laurenz Albe:<br>
<a href=3D"https://stackoverflow.com/a/79693897" rel=3D"noreferrer" target=
=3D"_blank">https://stackoverflow.com/a/79693897</a><br>
<br>
=C2=A0 =C2=A0 Temporary tables are automatically removed when the database<=
br>
=C2=A0 =C2=A0 session terminates. Consequently, your users are running long=
<br>
=C2=A0 =C2=A0 database sessions.<br>
<br>
Sadly, this does not make much sense to me, because there are<br>
(currently) no sessions on the database (checked with &#39;ps ax&#39;).<br>=
</blockquote><div></div></div><div><br></div><div>Abnormal session terminat=
ion is the typical reason for them to hang around.</div><div><br></div><spa=
n class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"=
gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sau=
ce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&g=
t; lobster!</div></div></div></div></div></div>

--0000000000001a283b0643a3c421--