| From: | Joao Rito <joaovictor(dot)rito(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>, Ayoub Kazar <ayoub(dot)kazar(at)data-bene(dot)io> |
| Subject: | [PATCH] Fix IMPORT FOREIGN SCHEMA resolving lookup names in pg_temp |
| Date: | 2026-11-16 12:46:02 |
| Message-ID: | 20261116124602.1555-1-joaovictor.rito@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tomás Senart | 2026-11-16 13:00:33 | [PATCH v1] Avoid deforming unused trailing columns in COPY TO |
| Previous Message | Markos Fountoulakis | 2026-11-16 12:35:18 | [PATCH] basebackup: use sequential access advice for full-file reads |