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

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.