Hello,
b6dae498dfa fixed the index/trigger cases by filling in the new schema's
name on the created objects' RangeVars. But names that are looked up
inside the schema elements still go through the effective search_path,
where pg_temp is first. For a column type, a temporary type can
therefore shadow a permanent one, and the column is dropped at session
end.
Reproducer: (see below too for FK case)
-- session 1
CREATE TYPE public.mytype AS (x text);
CREATE TYPE pg_temp.mytype AS (y text);
CREATE SCHEMA s CREATE TABLE t (a mytype);
INSERT INTO s.t VALUES (ROW('test'));
-- s.t.a is pg_temp.mytype, not public.mytype
-- session 2, after session 1 exits
\d s.t
SELECT count(*) FROM s.t;
At session end the temp type is dropped, and the column's dependency on it
causes the column to be dropped. In session 2, s.t has no columns but
still has the row we inserted.
The same thing happens to objects created in the new schema. If pg_temp
has a table with the same name as a table created earlier in the same
CREATE SCHEMA, an unqualified FK reference in a later element resolves
to the temp table:
CREATE TABLE pg_temp.parent (id int PRIMARY KEY);
CREATE SCHEMA s
CREATE TABLE parent (id int PRIMARY KEY)
CREATE TABLE child (p_id int REFERENCES parent(id));
This fails with
ERROR: constraints on permanent tables may reference only permanent
tables
The same this affects LIKE, views, GRANT, etc.
The commit message for b6dae498dfa says names that will be looked up rather
than created should not resolve in pg_temp or pg_catalog, but only the
created objects' RangeVars are qualified. The general search_path docs
say pg_temp is searched first for relation and type names, but CREATE
SCHEMA doesn't say anything about it.
The band-aid-like fix I have in mind is to append pg_temp to the temporary
search_path in CreateSchemaCommand:
new_schema, old_path, pg_temp
finalNamespacePath() will produce
pg_catalog, new_schema, old_path, pg_temp
when pg_catalog is not explicitly listed in old_path; an explicit
pg_temp or pg_catalog keeps its position. That fixes the cases above,
but I'm not sure if it's the right answer, or this behavior is actually
the intended one... ; so maybe not a bug at all.
Attached is the fix, thoughts ?
Regards,
Ayoub Kazar
From 5ee5a218956f3ec8fd37719be4e15ec3e605e518 Mon Sep 17 00:00:00 2001
From: AyoubKAZ <[email protected]>
Date: Fri, 13 Nov 2026 14:03:35 +0100
Subject: [PATCH] Fix CREATE SCHEMA lookup names resolving in pg_temp.
CreateSchemaCommand() put the new schema ahead of the old search path,
but finalNamespacePath() still prepended pg_temp. Lookup names in schema
elements, such as column types and foreign key references, could therefore
resolve to temporary objects instead of objects in the new schema or the
user's path.
Append pg_temp to the temporary search_path so it remains last, while
preserving an explicit pg_temp in the old path.
Add regression tests for type and foreign key lookups.
---
src/backend/commands/schemacmds.c | 7 ++-
src/test/regress/expected/create_schema.out | 48 +++++++++++++++++++++
src/test/regress/sql/create_schema.sql | 35 +++++++++++++++
3 files changed, 89 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index b016983f904..daeb2700ba2 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -156,7 +156,10 @@ CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt,
CommandCounterIncrement();
/*
- * Prepend the new schema to the current search path.
+ * Prepend the new schema to the current search path, making it the
+ * default creation target namespace. Append pg_temp so that it is not
+ * implicitly prepended ahead of the new schema. If the old path already
+ * lists pg_temp, its explicit position is preserved.
*
* We use the equivalent of a function SET option to allow the setting to
* persist for exactly the duration of the schema creation. guc.c also
@@ -173,6 +176,8 @@ CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt,
if (*nsp != '\0')
appendStringInfo(&pathbuf, ", %s", nsp);
+ appendStringInfoString(&pathbuf, ", pg_temp");
+
(void) set_config_option("search_path", pathbuf.data,
PGC_USERSET, PGC_S_SESSION,
GUC_ACTION_SAVE, true, 0, false);
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 7a9b71f97b3..839e3755f80 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -170,5 +170,53 @@ drop cascades to table regress_schema_fk.t3
drop cascades to table regress_schema_fk.t4
drop cascades to table regress_schema_fk.t5
drop cascades to table regress_schema_fk.t6
+-- Test that pg_temp does not implicitly shadow objects in CREATE SCHEMA
+CREATE TYPE public.regress_temp_shadow_type AS (x int);
+CREATE TYPE pg_temp.regress_temp_shadow_type AS (y text);
+CREATE TABLE pg_temp.regress_temp_parent (id int PRIMARY KEY);
+-- The temp table and the table created in the new schema have the same
+-- name, so the FK must resolve to the new schema's table.
+CREATE SCHEMA regress_schema_temp
+ CREATE TABLE t (a regress_temp_shadow_type)
+ CREATE TABLE regress_temp_parent (id int PRIMARY KEY)
+ CREATE TABLE child (p_id int REFERENCES regress_temp_parent(id));
+SELECT a.attname, t.typname, t.typnamespace::regnamespace
+FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid
+WHERE a.attrelid = 'regress_schema_temp.t'::regclass AND a.attnum > 0;
+ attname | typname | typnamespace
+---------+--------------------------+--------------
+ a | regress_temp_shadow_type | public
+(1 row)
+
+\d regress_schema_temp.child
+ Table "regress_schema_temp.child"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ p_id | integer | | |
+Foreign-key constraints:
+ "child_p_id_fkey" FOREIGN KEY (p_id) REFERENCES regress_schema_temp.regress_temp_parent(id)
+
+-- An explicit pg_temp in the old path is honored
+SET search_path = pg_temp, public;
+CREATE SCHEMA regress_schema_temp2
+ CREATE TABLE t (a regress_temp_shadow_type);
+RESET search_path;
+SELECT a.attname, t.typname,
+ t.typnamespace = pg_my_temp_schema() AS is_temp
+FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid
+WHERE a.attrelid = 'regress_schema_temp2.t'::regclass AND a.attnum > 0;
+ attname | typname | is_temp
+---------+--------------------------+---------
+ a | regress_temp_shadow_type | t
+(1 row)
+
+DROP SCHEMA regress_schema_temp CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table regress_schema_temp.t
+drop cascades to table regress_schema_temp.regress_temp_parent
+drop cascades to table regress_schema_temp.child
+DROP SCHEMA regress_schema_temp2 CASCADE;
+NOTICE: drop cascades to table regress_schema_temp2.t
+DROP TYPE public.regress_temp_shadow_type CASCADE;
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 57014ac4930..45663021273 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -93,5 +93,40 @@ CREATE SCHEMA regress_schema_fk
DROP SCHEMA regress_schema_fk CASCADE;
+-- Test that pg_temp does not implicitly shadow objects in CREATE SCHEMA
+CREATE TYPE public.regress_temp_shadow_type AS (x int);
+CREATE TYPE pg_temp.regress_temp_shadow_type AS (y text);
+CREATE TABLE pg_temp.regress_temp_parent (id int PRIMARY KEY);
+
+-- The temp table and the table created in the new schema have the same
+-- name, so the FK must resolve to the new schema's table.
+CREATE SCHEMA regress_schema_temp
+ CREATE TABLE t (a regress_temp_shadow_type)
+ CREATE TABLE regress_temp_parent (id int PRIMARY KEY)
+ CREATE TABLE child (p_id int REFERENCES regress_temp_parent(id));
+
+SELECT a.attname, t.typname, t.typnamespace::regnamespace
+FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid
+WHERE a.attrelid = 'regress_schema_temp.t'::regclass AND a.attnum > 0;
+
+\d regress_schema_temp.child
+
+-- An explicit pg_temp in the old path is honored
+SET search_path = pg_temp, public;
+
+CREATE SCHEMA regress_schema_temp2
+ CREATE TABLE t (a regress_temp_shadow_type);
+
+RESET search_path;
+
+SELECT a.attname, t.typname,
+ t.typnamespace = pg_my_temp_schema() AS is_temp
+FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid
+WHERE a.attrelid = 'regress_schema_temp2.t'::regclass AND a.attnum > 0;
+
+DROP SCHEMA regress_schema_temp CASCADE;
+DROP SCHEMA regress_schema_temp2 CASCADE;
+DROP TYPE public.regress_temp_shadow_type CASCADE;
+
-- Clean up
DROP ROLE regress_create_schema_role;
--
2.34.1
