Re: BUG #19874: CREATE SCHEMA view binds same-named table in pg_temp and fails

From: Raja Sai pranav <rajasaipranav0(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 1548140642(at)qq(dot)com, kazarayoub2004(at)gmail(dot)com
Subject: Re: BUG #19874: CREATE SCHEMA view binds same-named table in pg_temp and fails
Date: 2026-11-21 06:42:40
Message-ID: nh-35fc6939-7698-4339-a83a-291837f78089@google.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

This has the same cause as the pgsql-hackers thread "CREATE SCHEMA still
resolves unqualified type names in pg_temp", which has a patch from Ayoub
Kazar that has been reviewed but not committed yet:
https://www.google.com/url?q=https://postgr.es/m/8d0b272d-1978-47a2-85a6-30679038765d@data-bene.io&source=gmail&ust=1795329760088000&sa=E

Names inside the CREATE SCHEMA elements are looked up with pg_temp first in
the search path, so the view's "items" resolves to the temporary table. I
applied that patch on master (4a26e36f87) and ran your script. With the
patch, CREATE SCHEMA succeeds and SELECT * FROM app_data.item_view returns
"new-schema". Without it, I get the same "cannot create temporary relation
in non-temporary schema" error you reported.

Ayoub, your test might be worth extending with this view case, which Joao
also suggested on your thread.

Regards,
Sai Pranav

On Sat, Nov 21, 2026 12:12 PM, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 19874
> Logged by: Milo
> Email address: 1548140642(at)qq(dot)com
> PostgreSQL version: 18.6
> Operating system: Linux/amd64
> Description:
>
> CREATE SCHEMA fails when it creates a table and a view over that table
> while
> pg_temp already contains a table with the same name. PostgreSQL treats the
> view as temporary and then rejects it because the target schema is
> permanent.
>
> The compound statement should create a permanent view whose query binds to
> the table created in the new schema. After inserting the value new-schema
> into that table, selecting from the view should return new-schema.
>
> How to repeat:
>
> \set ON_ERROR_STOP off
> DROP SCHEMA IF EXISTS app_data CASCADE;
> DROP TABLE IF EXISTS items;
>
> CREATE TEMP TABLE items(marker text);
> INSERT INTO items VALUES ('temporary');
>
> CREATE SCHEMA app_data
> CREATE TABLE items(marker text)
> CREATE VIEW item_view AS
> SELECT marker FROM items;
>
> INSERT INTO app_data.items VALUES ('new-schema');
>
> SELECT c.relkind, n.nspname
> FROM pg_class AS c
> JOIN pg_namespace AS n ON n.oid = c.relnamespace
> WHERE c.relname = 'item_view'
> ORDER BY n.nspname;
>
> SELECT * FROM app_data.item_view;
>
> DROP SCHEMA IF EXISTS app_data CASCADE;
> DROP TABLE IF EXISTS items;
>
> The relevant client output was:
>
> CREATE TABLE
> INSERT 0 1
> psql:reproducer.sql:11: NOTICE: view "item_view" will be a temporary view
> psql:reproducer.sql:11: ERROR: cannot create temporary relation in
> non-temporary schema
> psql:reproducer.sql:13: ERROR: relation "app_data.items" does not exist
> LINE 1: INSERT INTO app_data.items VALUES ('new-schema');
> ^
> relkind | nspname
> ---------+---------
> (0 rows)
>
> psql:reproducer.sql:21: ERROR: relation "app_data.item_view" does not
> exist
> LINE 1: SELECT * FROM app_data.item_view;
> ^
> psql:reproducer.sql:23: NOTICE: schema "app_data" does not exist, skipping
> DROP SCHEMA
> DROP TABLE
>
> The notice and first error show that the unqualified items reference inside
> the view query resolved to the temporary table. Failure of the compound
> statement removes app_data atomically, which accounts for the later
> missing-relation errors.
>
> The issue was reproduced on PostgreSQL 18.6 on Linux/amd64. This occurs
> when
> a compound CREATE SCHEMA statement creates a view over another object in
> that statement while pg_temp contains an object with the referenced name.
>
>
>
>
>
>
>

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Fujii Masao 2026-11-21 06:54:31 Re: BUG #19881: CLUSTER uses a disabled sort path when enable_sort is off
Previous Message Raja Sai pranav 2026-11-21 06:31:25 Re: BUG #19890: pg_receivewal advances past a truncated LZ4 WAL segment