On Wed, Sep 3, 2025 at 5:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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
> 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.
>
Hi.
I just want to confirm we are on the same page. so you expect CREATE SCHEMA
OptSchemaEltList executed as the specified order given, also expect cross table
FOREIGN KEY referencing works just fine too?
If so, obviously It will be a hack, but it seems the hack is easier to
understand.
1. For CREATE TABLE, some case, we wrap the FK Constraint node in the form of an
ALTER TABLE ADD CONSTRAINT command, creating the FK constraint only after the
relation itself has been created. see transformFKConstraints comments. For
CREATE SCHEMA, we can make it after multiple relations are created, then process
FK constraint too.
2. src/backend/parser/gram.y already processes most of the information for FK.
transformFKConstraints merely wraps the FK Constraint node into an
AlterTableCmd, which is then wrapped into an AlterTableStmt.
In the HEAD, CREATE SCHEMA will fail when you first mention FK then PK.
but with the attached, it will work fine.
for example, below two will error out in the HEAD, but it will works
fine with the attached
patch.
CREATE SCHEMA s2
CREATE TABLE t2(a int, b int, c int, foreign key (a, b) references
t1 match full) partition by range (a, b, c)
CREATE TABLE t1(a int, b int, primary key(b, a)) partition by range (a, b);
CREATE SCHEMA s2
CREATE TABLE t2(a int, b int, c int, foreign key (a, b) references
t1 match full) partition by range (a, b, c)
CREATE TABLE t1(a int, b int, primary key(b, a)) partition by range (a, b);
--
jian
https://www.enterprisedb.com/
