Обсуждение: failure to drop table due to pg_temp_7 schema

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, and, as one step among many, it tried
to remove the Que objects, using scripts included in the Que
software distribution.
This then fails reproducible, as shown above.

Que is this: https://github.com/que-rb/que

Now I could probably follow the HINT and do DROP...CASCADE, and that
might actually work. But I don't think this is a sane approach - to
mangle around inside a vast tree of vendor-provided routines.

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').

But, besides this explanation, I find mostly tutorials about
using temporary tables, and nothing about housekeeping to keep
these things proper.

BTW, I find the same vast amount of pg_temp schemas on the
production database also (but there might be sessions).

So, before I now dive really deep into the mess and figure out
how it all does (not) work, I thought I might in parallel drop a note
here. 

PostgresQL version is 15.14
OS is FreeBSD 14.3
Application is Rails 8.0.1

cheers,
PMc

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

On 11/15/25 06:57, Peter 'PMc' Much wrote:
> 
> Hi,

> Que is this: https://github.com/que-rb/que

Personally I would be more worried about an application
where the last commit was:

Changelog: Add entry for version 2.4.1
committed
on Oct 27, 2024.

Makes you wonder what will happen if you upgrade to a newer version
of Postgres?


> cheers,
> PMc
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Nov 15, 2025 at 10:28:18AM -0500, Ron Johnson wrote:
! On Sat, Nov 15, 2025 at 10:00 AM Peter 'PMc' Much <

! > The routine was trying to remove all database objects in the order
! > they were formerly created,
!
!
! In the *REVERSE* order they were created?

I might suppose so ;)

! >     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.

Hmja.
I did a bit of research in the meantime. What the Que software does,
is this:

https://github.com/que-rb/que/blob/17fb2c3b75b37599bf17043dbb692555f582f249/lib/que/poller.rb#L188

Now if I do the same:

  postgres=# CREATE FUNCTION pg_temp.mycrap() RETURNS text AS $$
  select '1';
  $$
  LANGUAGE SQL;

and then quit the psql, the pg_tmp_10 schema stays, but the function
is gone. Okay.
If I instead kill-9 the psql, then - the function is gone. Very well.

Then, if the serving process is hard killed... then some more things
might fall down - no, I don't want to try that currently, and it
shouldn't happen anyway.

But what about power loss? We have here, once or twice a year - not
enough to warrant an UPS, but to consider.
Lets find out if these objects are persisted... they are.

That could be the explanation. At least during a clean restart
(as I just did) the server does not consider that there cannot
be any sessions right now, and remove these objects.
Then most likely, during an unclean restart it doesn't either.
(maybe that cannot be done so easily with replicas or such - no idea)

Okay, that figures as a root case. And anyway it seems there
is no problem in just dropping these things while the application is
down.


cheers,
PMc

On Sat, Nov 15, 2025 at 08:06:22AM -0800, Adrian Klaver wrote:
! On 11/15/25 06:57, Peter 'PMc' Much wrote:
! > 
! > Hi,
! 
! > Que is this: https://github.com/que-rb/que
! 
! Personally I would be more worried about an application
! where the last commit was:
! 
! Changelog: Add entry for version 2.4.1
! committed
! on Oct 27, 2024.

Really? I'd call that quite recently.

And there is an explanation: Rails has dropped automated support
for Que. That doesn't matter to me, because I'm not using it in the
automated fashion. But it means the big user base is gone, and
therewith the influx of improvement desires.

! Makes you wonder what will happen if you upgrade to a newer version
! of Postgres?

I'll see when I'm there. Still have to wait for the new kerberos in
FreeBSD - there will be a lot more to mangle anyway.

But speaking generally, I am quite bewildered that a simple tool
being stable for a year might already be considered worrisome.
Normally, a new technology brings a vast amount of innovation for
the first or second decade, and then it starts to stabilize. 
We in the IT do the opposite, we ever increase the change rate,
and I am wondering where this is supposed to lead.

cheers,
PMc

On 11/15/25 10:10, Peter 'PMc' Much wrote:
> On Sat, Nov 15, 2025 at 08:06:22AM -0800, Adrian Klaver wrote:
> ! On 11/15/25 06:57, Peter 'PMc' Much wrote:
> ! >
> ! > Hi,
> !
> ! > Que is this: https://github.com/que-rb/que
> !
> ! Personally I would be more worried about an application
> ! where the last commit was:
> !
> ! Changelog: Add entry for version 2.4.1
> ! committed
> ! on Oct 27, 2024.
> 
> Really? I'd call that quite recently.
> 
> And there is an explanation: Rails has dropped automated support
> for Que. That doesn't matter to me, because I'm not using it in the
> automated fashion. But it means the big user base is gone, and
> therewith the influx of improvement desires.
> 
> ! Makes you wonder what will happen if you upgrade to a newer version
> ! of Postgres?
> 
> I'll see when I'm there. Still have to wait for the new kerberos in
> FreeBSD - there will be a lot more to mangle anyway.
> 
> But speaking generally, I am quite bewildered that a simple tool
> being stable for a year might already be considered worrisome.

If the tool was self contained and did not rely on other software that 
might be alright. This tool does not, it has dependencies on Postgres 
and Rails and OS. They will be moving on. If you never change any of 
current versions of these then again you may be alright. Is that your 
intention?

Then there is the issue of issues:

https://github.com/que-rb/que/issues

The last one was closed Jan 30, 2024, with six new ones added since then 
and 37 open ones from before. The question is it moving from stable to 
moribund? Or more to the point are you willing to do your own tech 
support for the tool?



> Normally, a new technology brings a vast amount of innovation for
> the first or second decade, and then it starts to stabilize.
> We in the IT do the opposite, we ever increase the change rate,
> and I am wondering where this is supposed to lead.
> 
> cheers,
> PMc


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Nov 15, 2025 at 10:36:54AM -0800, Adrian Klaver wrote:
! On 11/15/25 10:10, Peter 'PMc' Much wrote:
! > On Sat, Nov 15, 2025 at 08:06:22AM -0800, Adrian Klaver wrote:
! > ! On 11/15/25 06:57, Peter 'PMc' Much wrote:
! > ! >
! > ! > Hi,
! > !
! > ! > Que is this: https://github.com/que-rb/que
! > !
! > ! Personally I would be more worried about an application
! > ! where the last commit was:
! > !
! > ! Changelog: Add entry for version 2.4.1
! > ! committed
! > ! on Oct 27, 2024.
! > 
! > Really? I'd call that quite recently.
! > 
! > And there is an explanation: Rails has dropped automated support
! > for Que. That doesn't matter to me, because I'm not using it in the
! > automated fashion. But it means the big user base is gone, and
! > therewith the influx of improvement desires.
! > 
! > ! Makes you wonder what will happen if you upgrade to a newer version
! > ! of Postgres?
! > 
! > I'll see when I'm there. Still have to wait for the new kerberos in
! > FreeBSD - there will be a lot more to mangle anyway.
! > 
! > But speaking generally, I am quite bewildered that a simple tool
! > being stable for a year might already be considered worrisome.
! 
! If the tool was self contained and did not rely on other software that might
! be alright. This tool does not, it has dependencies on Postgres and Rails
! and OS. They will be moving on. If you never change any of current versions
! of these then again you may be alright. Is that your intention?

You're right in that regard, regular maintenance is necessary. But
sadly, this is what people seem not so fond of doing, so we either
get tools that constantly bring new features and are well maintained,
or that lack in maintenance over all.

! Then there is the issue of issues:
! 
! https://github.com/que-rb/que/issues
! 
! The last one was closed Jan 30, 2024, with six new ones added since then and
! 37 open ones from before. The question is it moving from stable to moribund?
! Or more to the point are you willing to do your own tech support for the
! tool?

In my case, absolutely the latter. :) I have local patchsets for
the OS (mainly IPv6 firewalling stuff), for some applications, and
have the tools to properly manage these modifications, i.e. local
build&deploy, versioning etc.
To me, it's a way to stay in control, to somehow surf the future
shockwave. 

In this case, I looked into what Rails now supports as a scheduler,
and found it too elabore (for now) to grok in-depth. And a small
and a bit older tool has an advantage, that one can quite easily
understand it and, if need arises, make it do what is locally
required. So it's a balance. And in recent years, the modernizations
often happened to curtate in delightfulness (with PostgreSQL sadly
not being an exception).


cheers,
PMc



! 
! 
! 
! > Normally, a new technology brings a vast amount of innovation for
! > the first or second decade, and then it starts to stabilize.
! > We in the IT do the opposite, we ever increase the change rate,
! > and I am wondering where this is supposed to lead.
! > 
! > cheers,
! > PMc
! 
! 
! -- 
! Adrian Klaver
! adrian.klaver@aklaver.com

On 11/15/25 12:25, Peter 'PMc' Much wrote:

> In this case, I looked into what Rails now supports as a scheduler,
> and found it too elabore (for now) to grok in-depth. And a small
> and a bit older tool has an advantage, that one can quite easily
> understand it and, if need arises, make it do what is locally
> required. So it's a balance. And in recent years, the modernizations
> often happened to curtate in delightfulness (with PostgreSQL sadly
> not being an exception).

Where is delightfulness short changed in?:

https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-HIGHLIGHTS



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Sat, Nov 15, 2025 at 02:10:49PM -0800, Adrian Klaver wrote:
! On 11/15/25 12:25, Peter 'PMc' Much wrote:

! > required. So it's a balance. And in recent years, the modernizations
! > often happened to curtate in delightfulness (with PostgreSQL sadly
! > not being an exception).
! 
! Where is delightfulness short changed in?:
! 
! https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-HIGHLIGHTS

Oh yes, these are fine things, and I know people will be delighted.
But, honestly, none of them would make me upgrade, as I do not
currently have a specific usecase. 

Recap: The last time I was really overjoyed, that was when
Erwin Brandstetter showed me about window functions and with-
clauses, and what they can do:
        https://stackoverflow.com/a/36659866/6201427
I didn't understand that immediately (because when you do
everything, and try to do it in-depth, you cannot do it quickly also,
you have to choose two of these three). But it was that feeling,
wow, there are things to learn, and it will get better and better.

Now for the downside, there are these usual sources of frustration
that just do happen. For instance, whenever I get into the mood
of doing a bit of lengthy SQL coding, I end up at the point where
some erratic fluctuations of execution times appear, like 50 vs.
2000 ms for the same query with roughly the same amount of rows,
just some different timestamps in the payload.
But these things do happen, the web has a lot of articles on
switching off nestloop, and you can't store statistics for a CTE
before invoking the query.

The greater trouble comes from things being "improved" to
cater for stupidity. In most cases that doesn't make things better.
And the postgtreSQL example for this would be the backup scheme - 
that is a funny little story about how to overwrite your production
database (well, if you consider that funny):

With Rel.13 came a new fashion of backup, and I was against it.
I think I mentioned it here, and that was not well received - it's
necessary for safety, was the bottomline.

So I sat down and wrote the new backup routine, all precisely
according to the book - since my backup tool didn't have
anything suitable to offer, at that time.
Then finally, last year or so, they (Bareos) came along with a proper
backup routine, and I wanted to switch. But before retiring my own
script, I wanted to see if the restore would actually work as smooth
as I had imagined. (I do not normally do restore tests, I think the
logical proof that the correct data is saved to the correct place,
should suffice.)

So I created some filespaces and did the test restore. All went
fine, so far. I carefully read the manual (section 26.3.4. in the
Rel.15 manual) and noticed item 4:

         If you are using tablespaces, you should verify that the
         symbolic links in pg_tblspc/ were correctly restored.

Oh yes, I thought, a good point! They were obviousely NOT correct
for a test restore: they pointed into the production tablespace.
I fixed them appropriately, and assumed that should do the trick.
Then I started the rollforward show.

Some one or two hours into the flight, I was wondering why my test
partitions had no disk traffic. Then I noticed that my production
tablespaces had a lot of disk traffic.

And then I figured the rest: the new backup scheme requires that a
file "tablespace_map" gets included in the backup. And then obviousely
also in the restore. The server had found that file, had happily
deleted my carefully crafted symlinks and replaced them with some
new ones created from that file - which now again pointed into
the *production* filespace.
And that was it with the production database.

Crap, I thought - I told them not to do it. They did it nevertheless,
"for safety", as they said. And now we see what that "safety" can do.

Corrollary: Its the same as with the public transport here. I tell
them, that doesn't work, they say "don't worry and shut up", and in
the end I have to pay the price. (Specifically, they wanted to switch
from paper tickets to chipcard. I said, as a passenger I cannot ensure
that such a chipcard would work correctly, because I didn't build it
and not even have means check if it is working. Doesn't matter, they
said, for the customer nothing does change. It took about two years
for that cheapest-vendor-chip to go dead, and then they charged me
penalty. See our terms of service, they said: when the chip doesn't
work, then you have failed to present a valid ticket and therefore
are a fare dodger and get punished.)

Well, over all, nothing really bad had happened. I did another, real
restore, and found that there hadn't been any transactions in the
critical timeframe - because the timeline didn't switch.
(That's another little gotcha: if we do point-in-time restore,
and give a point-in-time that is *after* the last recorded
transaction, then the rollforward does not fully complete, and
the timeline does not switch. But the stuff works nevertheless.)

cheers,
PMc

On 11/17/25 14:08, Peter 'PMc' Much wrote:
> On Sat, Nov 15, 2025 at 02:10:49PM -0800, Adrian Klaver wrote:

> ! Where is delightfulness short changed in?:
> !
> ! https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-HIGHLIGHTS
> 
> Oh yes, these are fine things, and I know people will be delighted.
> But, honestly, none of them would make me upgrade, as I do not
> currently have a specific usecase.

Alright so what makes you happy. The chance the project make everyone 
happy for any given release is slim to none. That is the consequence of 
developing a general purpose piece of software.


> But these things do happen, the web has a lot of articles on
> switching off nestloop, and you can't store statistics for a CTE
> before invoking the query.

Your problem description is sort of broad, have you tried MATERIALIZED 
or NOT MATERIALIZED as the case may be?

Otherwise start a new thread with a more complete description of the 
issue including EXPLAIN ANALYZE that might help folks troubleshoot the 
problem.


> 
> With Rel.13 came a new fashion of backup, and I was against it.
> I think I mentioned it here, and that was not well received - it's
> necessary for safety, was the bottomline.

What new fashion of backup and what is your issue with it?

Why could you not use an older type of backup?

> 
> So I sat down and wrote the new backup routine, all precisely
> according to the book - since my backup tool didn't have
> anything suitable to offer, at that time.
> Then finally, last year or so, they (Bareos) came along with a proper
> backup routine, and I wanted to switch. But before retiring my own
> script, I wanted to see if the restore would actually work as smooth
> as I had imagined. (I do not normally do restore tests, I think the
> logical proof that the correct data is saved to the correct place,
> should suffice.)

To me, "...correct data is saved to the correct place, ...",  is only 
correct if you can use it to recreate the database instance or the 
entire cluster. In other words prove the restore process works.


> cheers,
> PMc


-- 
Adrian Klaver
adrian.klaver@aklaver.com