Further fixes for CREATE TABLE LIKE: cope with self-referential FKs.

Commit 502898192 was too careless about the order of execution of the
additional ALTER TABLE operations generated by expandTableLikeClause.
It just stuck them all at the end, which seems okay for most purposes.
But it falls down in the case where LIKE is importing a primary key
or unique index and the outer CREATE TABLE includes a FOREIGN KEY
constraint that needs to depend on that index.  Weird as that is,
it used to work, so we ought to keep it working.

To fix, make parse_utilcmd.c insert LIKE clauses between index-creation
and FK-creation commands in the transformed list of commands, and change
utility.c so that the commands generated by expandTableLikeClause are
executed immediately not at the end.  One could imagine scenarios where
this wouldn't work either; but currently expandTableLikeClause only
makes column default expressions, CHECK constraints, and indexes, and
this ordering seems fine for those.

Per bug #16730 from Sofoklis Papasofokli.  Like the previous patch,
back-patch to all supported branches.

Discussion: https://postgr.es/m/16730-b902f7e6e0276b30@postgresql.org

2 files changed, 21 insertions, 0 deletions

diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index fb2b2a03cd5..7e43c50eb49 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -162,6 +162,22 @@ ERROR:  duplicate key value violates unique constraint "inhg_x_key"
 DETAIL:  Key (x)=(15) already exists.
 DROP TABLE inhg;
 DROP TABLE inhz;
+/* Use primary key imported by LIKE for self-referential FK constraint */
+CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
+\d inhz
+              Table "public.inhz"
+ Column | Type | Collation | Nullable | Default 
+--------+------+-----------+----------+---------
+ x      | text |           |          | 
+ xx     | text |           | not null | 
+Indexes:
+    "inhz_pkey" PRIMARY KEY, btree (xx)
+Foreign-key constraints:
+    "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
+Referenced by:
+    TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
+
+DROP TABLE inhz;
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
 CREATE INDEX ctlt1_b_key ON ctlt1 (b);
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 79658b1ce67..8595d5e92d5 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -80,6 +80,11 @@ INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
 DROP TABLE inhg;
 DROP TABLE inhz;
 
+/* Use primary key imported by LIKE for self-referential FK constraint */
+CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
+\d inhz
+DROP TABLE inhz;
+
 -- including storage and comments
 CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
 CREATE INDEX ctlt1_b_key ON ctlt1 (b);