From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian he <jian.universality@gmail.com>
cc: 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: <CACJufxEvYeyXZ9xj8f1A7cuMU4Fh4WF3Dg8HnArLSkwYbqKO3A@mail.gmail.com>
References: <CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com> <CACJufxG+mrh2O9RS0gX43gU6sv+CMY847eMjMQpe8t4ou-2ryg@mail.gmail.com> <CACJufxFUdgqDiK9B+VNtnAwZOj=O3NqdLtXO_OrOwE5XPdCpBA@mail.gmail.com> <CALdSSPggNNvcad69dhUceZ_gPuEYnKNNd=WJ_WnP=YDmh=iwmw@mail.gmail.com> <1898722.1732732780@sss.pgh.pa.us> <CALdSSPhckRXW+KEvdsUmkQ-ErbrP_vPNjGwgXNdpXDb8xnLEbQ@mail.gmail.com> <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>
Comments: In-reply-to jian he <jian.universality@gmail.com>
	message dated "Sun, 01 Dec 2024 17:59:08 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1353042.1733072243.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 01 Dec 2024 11:57:23 -0500
Message-ID: <1353043.1733072243@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1353043.1733072243%40sss.pgh.pa.us>
Precedence: bulk

jian he <jian.universality@gmail.com> writes:
> On Sun, Dec 1, 2024 at 1:53=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrot=
e:
>> (I'd be curious to know how other major implementations handle
>> this.  Are we the only implementation that ever read the spec
>> that way?)

> quote from  https://learn.microsoft.com/en-us/sql/t-sql/statements/creat=
e-schema-transact-sql?view=3Dsql-server-ver16
> <<>>
> CREATE SCHEMA can create a schema, the tables and views it contains, and=
 GRANT,
> REVOKE, or DENY permissions on any securable in a single statement. This
> statement must be executed as a separate batch. Objects created by the C=
REATE
> SCHEMA statement are created inside the schema that is being created.

> Securables to be created by CREATE SCHEMA can be listed in any order, ex=
cept for
> views that reference other views. In that case, the referenced view must=
 be
> created before the view that references it.

> Therefore, a GRANT statement can grant permission on an object before th=
e object
> itself is created, or a CREATE VIEW statement can appear before the CREA=
TE TABLE
> statements that create the tables referenced by the view. Also, CREATE T=
ABLE
> statements can declare foreign keys to tables that are defined later in =
the
> CREATE SCHEMA statement.
> <<>>

Interesting.  But I suspect this tells us more about SQL Server's
internal implementation of DDL actions than about spec requirements.

I looked at DB2's reference page:
https://www.ibm.com/docs/en/db2/11.5?topic=3Dstatements-create-schema
It doesn't have much of anything explicit on this topic, but they do
give an example showing that you can create two tables with mutually
referencing foreign keys, which means they postpone FK constraint
creation till the end.  There's also this interesting tidbit:
"Unqualified object names in any SQL statement within the CREATE SCHEMA
statement are implicitly qualified by the name of the created schema."
which eliminates some of the is-that-an-external-reference-or-a-
forward-reference ambiguities I was concerned about yesterday.
That ship sailed decades ago for us, however.

I'm also interested to note that like SQL Server, DB2 has strict
limits on the types of objects that can be created, much narrower
than what the spec suggests.  For DB2 it's:

CREATE TABLE statement, excluding typed tables and materialized query tabl=
es
CREATE VIEW statement, excluding typed views
CREATE INDEX statement
COMMENT statement
GRANT statement

That suggests, even though they don't say so, that they're trying to
do forward-reference removal; there'd be little reason for the
restriction otherwise.

MySQL doesn't have CREATE SCHEMA (it's a synonym for CREATE DATABASE),
so nothing to be learned there.

Whether or not the standard has an opinion on this topic, it's pretty
clear that real implementations are all over the place and have plenty
of ad-hoc restrictions.  I'm still thinking that "let's forget all
that and do the subcommands in order" is a win for sanity and
explainability.

			regards, tom lane