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: 
 <CACJufxGOtXuJD3VLOMUQr08Zt3=8HjH0KYk2NivYPuMbHG2JQg@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>
 <345c20e5-a6d7-477a-9598-982661bb5740@eisentraut.org>
 <1990479.1733256458@sss.pgh.pa.us>
 <CALdSSPi1eFMhAY6Xc7ZShnU0m5YrswOFLyDKrFzLjYd7wefzQg@mail.gmail.com>
 <499425.1733936916@sss.pgh.pa.us>
 <CACJufxF28Vk27JhJ9u8tq10BoHLg=T9=wJ8zqTy_ajM4=Czunw@mail.gmail.com>
 <CALdSSPjH3Ag_6v=Q+mnhJocaYor0eGYOYkZWDG8w2rXRVioTXw@mail.gmail.com>
 <CACJufxGOtXuJD3VLOMUQr08Zt3=8HjH0KYk2NivYPuMbHG2JQg@mail.g!
 mail.com>
Comments: In-reply-to jian he <jian.universality@gmail.com>
	message dated "Tue, 26 Aug 2025 12:53:07 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <685421.1756848241.1@sss.pgh.pa.us>
Date: Tue, 02 Sep 2025 17:24:01 -0400
Message-ID: <685422.1756848241@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/685422.1756848241%40sss.pgh.pa.us>
Precedence: bulk

jian he <jian.universality@gmail.com> writes:
> Please check the latest attached.
> v7-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
> v7-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
> v7-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
> v7-0004-CREATE-SCHEMA-CREATE-TYPE.patch

I think this is still kind of blocked, because it's not clear to me
whether we have consensus about it being okay to do 0001.

Re-reading the thread, the only real use-case for re-ordering that
anyone proposed is that foreign key references should be able to be
forward references to tables created later in the same CREATE SCHEMA.
I concede first that this is a somewhat-plausible use-case and
second that it is pretty clearly required by spec.  The fact remains
however that we have never supported that in two dozen years, and
the number of complaints about the omission could be counted without
running out of thumbs.  So, how about the following plan of action?

1. Rip out subcommand re-ordering as currently implemented, and do the
subcommands in the given order.

2. When a CREATE TABLE subcommand includes a FOREIGN KEY clause,
transform that clause into ALTER TABLE ADD FOREIGN KEY, and push
it to the back of the CREATE SCHEMA's to-do list.

#2 gives us at least pro-forma spec compliance, and AFAICS it does
not introduce any command re-ordering bugs.  Foreign key clauses
don't depend on each other, so shoving them to the end without any
further sorting should be fine.

Also ... we don't really have to do #2 until someone complains about
the lack of ability to do forward references, which going by history
is probably not going to be soon.  I certainly don't feel that it has
to be completed in this patchset.

			regards, tom lane