On Wed, Sep 3, 2025 at 5:24 AM Tom Lane <[email protected]> wrote: >
> jian he <[email protected]> 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/
v8-0002-make-create-foreign-key-executed-at-the-end-of-CREATE-SCHEMA.nocfbot
Description: Binary data
v8-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.nocfbot
Description: Binary data
