Feedback-ID: ie0a040ee:Fastmail
Message-ID: <345c20e5-a6d7-477a-9598-982661bb5740@eisentraut.org>
Date: Tue, 3 Dec 2024 20:35:10 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
To: Tom Lane <tgl@sss.pgh.pa.us>, jian he <jian.universality@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.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>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <1075425.1732993688@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/345c20e5-a6d7-477a-9598-982661bb5740%40eisentraut.org>
Precedence: bulk

On 30.11.24 20:08, Tom Lane wrote:
> 2. transformCreateSchemaStmtElements is of the opinion that it's
> responsible for ordering the schema elements in a way that will work,
> but it just about completely fails at that task.  Ordering the objects
> by kind is surely not sufficient, and adding CREATE DOMAIN will make
> that worse.  (Example: a domain could be used in a table definition,
> but we also allow domains to be created over tables' composite types.)
> Yet we have no infrastructure that would allow us to discover the real
> dependencies between unparsed DDL commands, nor is it likely that
> anyone will ever undertake building such.  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.  I'd also argue that doing anything else is
> a POLA violation.  It's especially a POLA violation if the code
> rearranges a valid user-written command order into an invalid order,
> which is inevitable if we stick with the current approach.
> 
> The notion that we ought to sort the objects by kind appears to go
> all the way back to 95ef6a344 of 2002-03-21, which I guess makes it
> my fault.  There must have been some prior mailing list discussion,
> but I couldn't find much.  There is a predecessor of the committed
> patch in
> https://www.postgresql.org/message-id/flat/3C7F8A49.CC4EF0BE%40redhat.com
> but no discussion of why sorting by kind is a good idea.  (The last
> message in the thread suggests that there was more discussion among
> the Red Hat RHDB team, but if so it's lost to history now.)

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>.
"""

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.