Re: Temp schema drop leaves an inconsistent state behind

On 22/09/2026 05:54, Manu wrote:
> The attached temp_schema_drop_cases.sql has all of the above as a plain
> psql script, one case per session, with what each should return. Case 8
> deletes a pg_namespace row, so it is meant for a scratch database. On
> master cases 1, 5 and 8 fail (8 by crashing, as the last statement), with
> 0001 and 0002 cases 2, 3, 6 and 7, and with the diff none.

On top of that, a just dropped schema can still be used to create
temporary objects, which creates a pg_class orphan:

psql (20devel)
Type "help" for help.

postgres=# create temporary table t();
CREATE TABLE
postgres=# \d t
Table "pg_temp_54.t"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------

postgres=# select pg_my_temp_schema();
pg_my_temp_schema
-------------------
17398
(1 row)

postgres=# drop schema pg_temp_54 cascade;
NOTICE: drop cascades to table t
DROP SCHEMA
postgres=# set search_path = pg_temp, public;
SET
postgres=# create sequence s;
CREATE SEQUENCE
postgres=# select relname, relpersistence, relnamespace from pg_class
where relname = 's';
relname | relpersistence | relnamespace
---------+----------------+--------------
s | t | 17398
(1 row)

The dropped schema (oid 17398) is still being used, so a concurrent
pg_dump will also fail:

$ pg_dump postgres
pg_dump: error: schema with OID 17398 does not exist

IIUC the problem is that activeCreationNamespace is used when the
relpersistence is not RELPERSISTENCE_TEMP. So with pg_temp first in
search_path, activeCreationNamespace is the dropped schema.

Thanks!

Best, Jim