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 1tIZBS-00F7Js-EX
	for pgsql-hackers@arkaria.postgresql.org; Tue, 03 Dec 2024 20:07:46 +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 1tIZBP-00CgNs-Tv
	for pgsql-hackers@arkaria.postgresql.org; Tue, 03 Dec 2024 20:07:45 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	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 1tIZBP-00CgNj-JW
	for pgsql-hackers@lists.postgresql.org; Tue, 03 Dec 2024 20:07:44 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.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 1tIZBN-000sUR-7r
	for pgsql-hackers@lists.postgresql.org; Tue, 03 Dec 2024 20:07:43 +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 4B3K7clT1990480;
	Tue, 3 Dec 2024 15:07:38 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: jian he <jian.universality@gmail.com>,
        Kirill Reshke <reshkekirill@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
In-reply-to: <345c20e5-a6d7-477a-9598-982661bb5740@eisentraut.org>
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> <345c20e5-a6d7-477a-9598-982661bb5740@eisentraut.org>
Comments: In-reply-to Peter Eisentraut <peter@eisentraut.org>
	message dated "Tue, 03 Dec 2024 20:35:10 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1990478.1733256458.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 03 Dec 2024 15:07:38 -0500
Message-ID: <1990479.1733256458@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/1990479.1733256458%40sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter@eisentraut.org> writes:
> On 30.11.24 20:08, Tom Lane wrote:
>> ... I think we ought to nuke
>> that concept from orbit and just execute the schema elements in the
>> order presented.  I looked at several iterations of the SQL standard
>> and cannot find any support for the idea that CREATE SCHEMA needs to
>> be any smarter than that.

> SQL/Framework subclause "Descriptors" says:

> """
> The execution of an SQL-statement may result in the creation of many 
> descriptors. An SQL object that is created as a result of an 
> SQL-statement may depend on other descriptors that are only created as a 
> result of the execution of that SQL statement.

> NOTE 8 — This is particularly relevant in the case of the <schema 
> definition> SQL-statement. A <schema definition> can, for example, 
> contain many <table definition>s that in turn contain <table 
> constraint>s. A single <table constraint> in one <table definition> can 
> reference a second table being created by a separate <table definition> 
> which itself is able to contain a reference to the first table. The 
> dependencies of each table on the descriptors of the other are valid 
> provided that all necessary descriptors are created during the execution 
> of the complete <schema definition>.
> """

Ah, thanks for the pointer.

> So this says effectively that forward references are allowed.  Whether 
> reordering the statements is a good way to implement that is dubious, as 
> we are discovering.

Yeah, I think it's a long way from this text to the conclusion that
the implementation is responsible for reordering the subcommands
to remove forward references.  And it really offers no help at all
for the ensuing problem of distinguishing forward references from
external references.

The one aspect of the spec's definition that seems useful to me in
practice is the ability to create quasi-circular foreign keys (that
is, t1 has an FK to t2 and t2 has an FK to t1).  But that is something
we have never implemented in 22 years and nobody has complained of
the lack.  I'm totally willing to throw that possibility overboard
permanently in order to expand the set of creatable object types
without introducing a ton of restrictions and weird behaviors.
What do you think?

			regards, tom lane

PS: if we were really excited about allowing circular FKs to be
made within CREATE SCHEMA, a possible though non-standard answer
would be to allow ALTER TABLE ADD CONSTRAINT as a <schema element>.