MIME-Version: 1.0
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>
In-Reply-To: 
 <CACJufxF28Vk27JhJ9u8tq10BoHLg=T9=wJ8zqTy_ajM4=Czunw@mail.gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Fri, 22 Aug 2025 13:59:37 +0500
Message-ID: 
 <CALdSSPjH3Ag_6v=Q+mnhJocaYor0eGYOYkZWDG8w2rXRVioTXw@mail.gmail.com>
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
To: jian he <jian.universality@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter@eisentraut.org>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALdSSPjH3Ag_6v%3DQ%2BmnhJocaYor0eGYOYkZWDG8w2rXRVioTXw%40mail.gmail.com>
Precedence: bulk

On Tue, 19 Aug 2025 at 08:37, jian he <jian.universality@gmail.com> wrote:
>
> On Thu, Dec 12, 2024 at 1:08=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrot=
e:
> > This cannot possibly work if an object-type-based re-ordering
> > is done to it.
> >
> > So IMV, we have three possibilities:
> >
> > 1. CREATE SCHEMA's schema-element feature remains forevermore
> > a sad joke that (a) doesn't cover nearly enough to be useful and
> > (b) doesn't come close to doing what the spec says it should.
> >
> > 2. We invest an enormous amount of engineering effort on trying
> > to extract dependencies from not-yet-analyzed parse trees, after
> > which we invest a bunch more effort figuring out heuristics for
> > ordering the subcommands in the face of circular dependencies.
> > (Some of that could be stolen from pg_dump, but not all: pg_dump
> > only has to resolve a limited set of cases.)
> >
> > 3. We bypass the need for #2 by decreeing that we'll execute
> > the subcommands in order.
> >
> >
> > >> 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>.
> >
> > > That's a nice feature to have by itself?
> >
> > Not unless we abandon the idea of subcommand reordering, because
> > where are you going to put the ALTER TABLE subcommands?
> >
>
> hi.

Hi!

> move this forward with option #3 (executing the subcommands in order).

Thank you. I am +1 on option #3.

> pg_dump don't use CREATE SCHEMA ...CREATE ...
> so if we error out
> CREATE SCHEMA regress_schema_2 CREATE VIEW abcd_view AS SELECT a FROM
> abcd CREATE TABLE abcd (a int);
> it won't be a big compatibility issue?
> Also this thread doesn=E2=80=99t show strong support for sorting the subc=
ommands.
>
> the full <schema definition> in 11.1 is:
> 11.1 <schema definition>
>
> <schema element> ::=3D
> <table definition>
> | <view definition>
> | <domain definition>
> | <character set definition>
> | <collation definition>
> | <transliteration definition>
> | <assertion definition>
> | <trigger definition>
> | <user-defined type definition>
> | <user-defined cast definition>
> | <user-defined ordering definition>
> | <transform definition>
> | <schema routine>
> | <sequence generator definition>
> | <grant statement>
> | <role definition>
>
> so I also add support for CREATE SCHEMA CREATE COLLATION.
>
> v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
> v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
> v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
>
> v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
> is refactor/rebase based on
> v1-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-S.patch
>
> v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
> for CREATE SCHEMA ... CREATE-DOMAIN
>
> v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
> for CREATE SCHEMA ... CREATE-COLLATION


With these patches applied:
```
reshke=3D# create schema  sh1 create type tp as  (i text);
ERROR:  unrecognized node type: 226
```
Without patches it will be a syntax error.


Also we need a better error message in this:
"CREATE SCHEMA ... CREATE OBJECT currently not support for..."

First of all, is it s/support/supported/ ? Also would vote for
something like "%s is not yet supported inside schema definition."
WDYT?


--=20
Best regards,
Kirill Reshke