Re: failure to drop table due to pg_temp_7 schema

Ron Johnson <ronljohnsonjr(at)gmail(dot)com> writes:
> On Sat, Nov 15, 2025 at 10:00 AM Peter 'PMc' Much <
> pmc(at)citylink(dot)dinoex(dot)sub(dot)org> wrote:
>> 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.

>> Then, trying to figure out how this is supposed to be cleaned up,
>> I find this article by subject matter expert Laurenz Albe:
>>
>> 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.

Yeah. I'd probably try to clean this up with (as superuser)

DROP SCHEMA pg_temp_7 CASCADE;

being sure that there is no other session that could be using that
temp schema.

There's no need to get rid of the pg_temp_NN schemas themselves;
they are meant to hang around even when not in use, to reduce
catalog thrashing. But any objects in them should have gone
away at exit of the owning session. As Ron says, that's
typically a consequence of an abnormal shutdown not affording any
opportunity to drop the objects. It's normally harmless, because
the next session that wants to use that schema is also expected
to be willing to drop everything in it.

There could be some deeper problem such as broken dependencies,
in which case the recommended manual DROP SCHEMA would fail.
But there's not evidence of that, yet.

regards, tom lane