From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1548140642@qq.com
Subject: BUG #19874: CREATE SCHEMA view binds same-named table in pg_temp and fails
Date: Wed, 11 Nov 2026 10:17:19 +0000
Message-ID: <19874-1ade7299b36bdc5f@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19874
Logged by: Milo
Email address: 1548140642@qq.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 1548140642@qq.com
Subject: Re: BUG #19874: CREATE SCHEMA view binds same-named table in pg_temp and fails
In-Reply-To: <19874-1ade7299b36bdc5f@postgresql.org>
References: <19874-1ade7299b36bdc5f@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox