> On Wed, Sep 3, 2025 at 5:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
>
> > 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.
v8-0001 through v8-0004 are rebased from v7 with some adjustments.
v8-0005 transforms foreign key constraints into ALTER TABLE ... ADD FOREIGN KEY.
This works for both column constraint and table constraint. Below is a contrived
complex test case I came up with to verify correctness.
CREATE SCHEMA regress_schema_8
CREATE TABLE regress_schema_8.t2 (
b int,
a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
REFERENCES t3 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED,
CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 DEFERRABLE INITIALLY
DEFERRED NOT ENFORCED)
CREATE TABLE regress_schema_8.t1 (a int PRIMARY KEY)
CREATE TABLE t3 (a int PRIMARY KEY)
CREATE TABLE t4(b int,
a int REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED,
CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY
DEFERRED NOT ENFORCED)
CREATE TABLE t5 (a int, b int, PRIMARY KEY (a))
CREATE TABLE t6 (a int, b int, PRIMARY KEY (a));
Does this resolve your #2?
--
jian
https://www.enterprisedb.com/
