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
