Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1tHvyI-00Aupp-HN
	for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Dec 2024 02:15:34 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1tHvyE-00D0NN-Tt
	for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Dec 2024 02:15:32 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tHvyE-00D0NF-KI
	for pgsql-hackers@lists.postgresql.org; Mon, 02 Dec 2024 02:15:31 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tHvyB-000Zqo-Fw
	for pgsql-hackers@lists.postgresql.org; Mon, 02 Dec 2024 02:15:29 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 4B22FNmg1525355;
	Sun, 1 Dec 2024 21:15:23 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
cc: jian he <jian.universality@gmail.com>,
        Kirill Reshke <reshkekirill@gmail.com>,
        Peter Eisentraut <peter@eisentraut.org>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
In-reply-to: <Z00OwQVHY4DfjVSA@paquier.xyz>
References: <2007224.1732773174@sss.pgh.pa.us> <CALdSSPgftbtV=UyeK_XcLit_mKQZr+g8pCxA0kLLxDi0kKwEGw@mail.gmail.com> <CACJufxH5TvkQiMOJ9dpDrDbRtyCa-yu+QOmYGt4WGGYVrN+P8g@mail.gmail.com> <1075425.1732993688@sss.pgh.pa.us> <1220935.1733009604@sss.pgh.pa.us> <CALdSSPi1tgLgXjSJ7jgQ7L65iRkpuDFL2cJH2QYSz-Hhx3DBew@mail.gmail.com> <1273964.1733032384@sss.pgh.pa.us> <CACJufxEvYeyXZ9xj8f1A7cuMU4Fh4WF3Dg8HnArLSkwYbqKO3A@mail.gmail.com> <1353043.1733072243@sss.pgh.pa.us> <1488327.1733092220@sss.pgh.pa.us> <Z00OwQVHY4DfjVSA@paquier.xyz>
Comments: In-reply-to Michael Paquier <michael@paquier.xyz>
	message dated "Mon, 02 Dec 2024 10:34:57 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1525353.1733105723.1@sss.pgh.pa.us>
Date: Sun, 01 Dec 2024 21:15:23 -0500
Message-ID: <1525354.1733105723@sss.pgh.pa.us>
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/1525354.1733105723%40sss.pgh.pa.us>
Precedence: bulk

Michael Paquier <michael@paquier.xyz> writes:
> If I'm parsing the spec right, the doc mentions in its 5)~6) of the
> syntax rules in CREATE SCHEMA that non-schema-qualified objects should
> use the new schema name defined in the CREATE SCHEMA query.  So that
> pretty much settles the rules to use when having a new object that has
> a reference to a non-qualified object created in the same CREATE
> SCHEMA query?

I don't see where you're getting that from?  DB2 says that unqualified
reference names (not to be confused with unqualified creation-target
names) are taken to be in the new schema, but I don't see any
corresponding restriction in the spec.

What I do see (11.1 SR 6 in SQL:2021) is:

    If <schema path specification> is not specified, then a <schema
    path specification> containing an implementation-defined <schema
    name list> that contains the <schema name> contained in <schema
    name clause> is implicit.

What I read this as is that the "search path" during schema-element
creation must include at least the new schema, but can also include
some other schemas as defined by the implementation.  That makes
our behavior compliant, because we can define the other schemas
as those in the session's prevailing search_path.  (DB2's behavior
is also compliant, but they're defining the path as containing only
the new schema.)

Also, if SQL intended to constrain the search path for unqualified
identifiers to be only the new schema, they'd hardly need a concept
of <schema path specification> at all.

			regards, tom lane