Re: CREATE SCHEMA still resolves unqualified type names in pg_temp

From: Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
To: Ayoub Kazar <ayoub(dot)kazar(at)data-bene(dot)io>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: CREATE SCHEMA still resolves unqualified type names in pg_temp
Date: 2026-11-15 19:45:56
Message-ID: 7a57d609-2b0b-41d7-8429-eaaf6cb2bb82@uni-muenster.de
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zsolt Parragi 2026-11-15 21:15:36 Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Previous Message Sho Ito 2026-11-15 18:45:40 Re: psql: Don't offer USING INDEX after REPACK (CONCURRENTLY) or (ANALYZE)