CREATE SCHEMA still resolves unqualified type names in pg_temp
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
Hi Ayoub,
I tested the patch on current master (206e719, built with cassert and tap tests) and it applies cleanly. Before the patch, a column type and a domain created in CREATE SCHEMA resolved to pg_temp, and an FK, view or LIKE pointing at a table created earlier in the same statement failed with the "permanent tables may reference only permanent tables" error. After the patch all of them resolve to the new schema, an explicit pg_temp at the front of the old path is still honored, and the regress suite passes (243 subtests).
What I did not test: the isolation and tap suites, pgindent and the docs.
Small suggestions: the new test could also cover a DOMAIN, a VIEW and LIKE (they work for me), and the commit message could mention b6dae498dfa. I also wondered about your own question on whether pg_temp should come last in the search path; the documentation for search_path does not mention CREATE SCHEMA, so maybe a short note is worth adding. I have no objection to the approach.
Regards,
Joao Rito
Hi Ayoub
On 13/11/2026 14:10, Ayoub Kazar wrote:
Attached is the fix, thoughts ?
Thanks for the patch!
I tried it out and it solves the described issue. Initially I was not
very convinced that unconditionally adding pg_temp to pathbuf was good
idea, but all the altenatives I came up with were too complex .. and
since execute_extension_script() in extension.c uses a similar trick, I
guess it is ok.
While experimenting with new code I found something related in IMPORT
FOREIGN SCHEMA -- it probably deserves its own patch. The cause seems to
be the same.
Example;
psql (20devel)
Type "help" for help.
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create server srv foreign data wrapper postgres_fdw options
(dbname 'postgres');
CREATE SERVER
postgres=# create user mapping for current_user server srv ;
CREATE USER MAPPING
postgres=# create schema s1;
CREATE SCHEMA
postgres=# create table s1.t (foo int4range);
CREATE TABLE
postgres=# create schema s2;
CREATE SCHEMA
postgres=# create type pg_temp.int4range as (x text);
CREATE TYPE
postgres=# import foreign schema s1 from server srv into s2;
IMPORT FOREIGN SCHEMA
.. the column was imported using pg_temp_N.int4range, not the one from
the catalog:
postgres=# select typname,t.typnamespace::regnamespace
from pg_attribute a join pg_type t on t.oid = a.atttypid
where a.attrelid = 's2.t'::regclass AND a.attname = 'foo';
typname | typnamespace
-----------+--------------
int4range | pg_temp_12
(1 row)
Which means it gets dropped when the session ends:
postgres=# \d s2.t
Foreign table "s2.t"
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------+-----------+----------+---------+---------------------
foo | int4range | | | | (column_name 'foo')
Server: srv
FDW options: (schema_name 's1', table_name 't')
postgres=# \q
$ psql postgres
psql (20devel)
Type "help" for help.
postgres=# \d s2.t
Foreign table "s2.t"
Column | Type | Collation | Nullable | Default | FDW options
--------+------+-----------+----------+---------+-------------
Server: srv
FDW options: (schema_name 's1', table_name 't')
WDYT?
Best, Jim
On 15/11/2026 20:45, Jim Jones wrote:
While experimenting with new code I found something related in IMPORT
FOREIGN SCHEMA -- it probably deserves its own patch. The cause seems to
be the same.
That is correct!
WDYT?
Yeah this deserves a separate patch, I considered if we should create a
helper func to unify search path stuff in schemacmds.c, foreigncmds.c,
and extension.c. But the latter is a bit different and it's not the
right moment for a refactor on them anyways, so i think duplicating and
following extension.c is okay.
Thanks for the review!
Regards,
Ayoub Kazar
ImportForeignSchema() executes the CREATE FOREIGN TABLE commands from the
FDW with the caller's search_path, which implicitly searches pg_temp first.
A temporary type with the same name as a catalog type, for example
pg_temp.int4range, made an imported column use the temporary type. The
imported column depended on a temporary type and was dropped at session
end.
Append pg_temp to the search_path while the commands run, as
execute_extension_script() does, keeping an explicit pg_temp in the
current path where it is.
This is the same problem that was found for CREATE SCHEMA, see
b6dae498dfa. Add a regression test to postgres_fdw.
---
Hi Ayoub, Jim,
Following Jim's report and Ayoub's note that this deserves a separate
patch, here is one for IMPORT FOREIGN SCHEMA. It follows the approach
of the CREATE SCHEMA patch, and the logic is duplicated as discussed.
Thanks both for the earlier review.
Tested on master (5ffc82103773, --enable-cassert --enable-tap-tests,
Debian container, 0 warnings). Without the patch, Jim's case gives a
column of type pg_temp_N.int4range, and the column disappears in a new
session. With the patch it is pg_catalog.int4range and the column stays.
The new postgres_fdw test fails without the C change and passes with it.
make check passes (243 tests), and so do make check in contrib/postgres_fdw,
file_fdw and dblink. The patch applies cleanly to master.
Not tested: pgindent (I followed the neighbouring style by hand),
check-world, isolation, any FDW other than postgres_fdw, and an empty
search_path (the code handles it, but I have no test for it).
Differences from the CREATE SCHEMA patch: the current search_path is
kept as it is, with pg_temp added at the end, because there is no new
schema to put first and the table name is already qualified.
Regards,
Joao Rito
.../postgres_fdw/expected/postgres_fdw.out | 21 ++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 18 ++++++++++
src/backend/commands/foreigncmds.c | 34 +++++++++++++++++++
3 files changed, 73 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index b3c73b8..3104367 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11077,6 +11077,27 @@ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
ERROR: schema "notthere" does not exist
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
ERROR: server "nowhere" does not exist
+-- Check that a temporary type does not shadow a catalog type. The type
+-- name is sent without schema qualification, so pg_temp must not be
+-- searched first when the foreign table is created.
+CREATE TABLE import_source.t_temp (c1 int4range);
+CREATE TYPE pg_temp.int4range AS (x text);
+CREATE SCHEMA import_dest_temp;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t_temp)
+ FROM SERVER loopback INTO import_dest_temp;
+SELECT a.attname, t.typname, t.typnamespace::regnamespace,
+ t.typnamespace = pg_my_temp_schema() AS is_temp
+ FROM pg_attribute a JOIN pg_type t ON t.oid = a.atttypid
+ WHERE a.attrelid = 'import_dest_temp.t_temp'::regclass AND a.attnum > 0;
+ attname | typname | typnamespace | is_temp
+---------+-----------+--------------+---------
+ c1 | int4range | pg_catalog | f
+(1 row)
+
+DROP TYPE pg_temp.int4range;
+DROP SCHEMA import_dest_temp CASCADE;
+NOTICE: drop cascades to foreign table import_dest_temp.t_temp
+DROP TABLE import_source.t_temp;
-- Check case of a type present only on the remote server.
-- We can fake this by dropping the type locally in our transaction.
CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8fc564a..ab19808 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3579,6 +3579,24 @@ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
+-- Check that a temporary type does not shadow a catalog type. The type
+-- name is sent without schema qualification, so pg_temp must not be
+-- searched first when the foreign table is created.
+CREATE TABLE import_source.t_temp (c1 int4range);
+CREATE TYPE pg_temp.int4range AS (x text);
+
+CREATE SCHEMA import_dest_temp;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t_temp)
+ FROM SERVER loopback INTO import_dest_temp;
+SELECT a.attname, t.typname, t.typnamespace::regnamespace,
+ t.typnamespace = pg_my_temp_schema() AS is_temp
+ FROM pg_attribute a JOIN pg_type t ON t.oid = a.atttypid
+ WHERE a.attrelid = 'import_dest_temp.t_temp'::regclass AND a.attnum > 0;
+
+DROP TYPE pg_temp.int4range;
+DROP SCHEMA import_dest_temp CASCADE;
+DROP TABLE import_source.t_temp;
+
-- Check case of a type present only on the remote server.
-- We can fake this by dropping the type locally in our transaction.
CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index c4852be..c8888bc 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -32,9 +32,11 @@
#include "foreign/foreign.h"
#include "miscadmin.h"
#include "parser/parse_func.h"
+#include "parser/scansup.h"
#include "tcop/utility.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
@@ -1595,6 +1597,9 @@ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
AclResult aclresult;
List *cmd_list;
ListCell *lc;
+ int save_nestlevel;
+ StringInfoData pathbuf;
+ char *nsp = namespace_search_path;
/* Check that the foreign server exists and that we have USAGE on it */
server = GetForeignServerByName(stmt->server_name, false);
@@ -1622,6 +1627,32 @@ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
/* Call FDW to get a list of commands */
cmd_list = fdw_routine->ImportForeignSchema(stmt, server->serverid);
+ /*
+ * Append pg_temp to the search path while executing the generated
+ * commands, so that pg_temp is not implicitly searched first. Otherwise
+ * a temporary object could shadow a type or collation that the FDW named
+ * without schema qualification, and the imported table would then depend
+ * on it. If the current path already lists pg_temp, that explicit
+ * position is preserved.
+ *
+ * We use the equivalent of a function SET option, so the setting lasts
+ * exactly as long as the import; guc.c also undoes it on error.
+ */
+ save_nestlevel = NewGUCNestLevel();
+
+ initStringInfo(&pathbuf);
+
+ while (scanner_isspace(*nsp))
+ nsp++;
+
+ 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);
+
/* Parse and execute each command */
foreach(lc, cmd_list)
{
@@ -1698,6 +1729,9 @@ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
error_context_stack = sqlerrcontext.previous;
}
+
+ /* Restore the GUC variable search_path we set above. */
+ AtEOXact_GUC(true, save_nestlevel);
}
/*
--
2.47.3