Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit97390fe

Browse files
committed
Further fixes for CREATE TABLE LIKE: cope with self-referential FKs.
Commit5028981 was too careless about the order of execution of theadditional 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 keyor unique index and the outer CREATE TABLE includes a FOREIGN KEYconstraint 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-creationand FK-creation commands in the transformed list of commands, and changeutility.c so that the commands generated by expandTableLikeClause areexecuted immediately not at the end. One could imagine scenarios wherethis wouldn't work either; but currently expandTableLikeClause onlymakes column default expressions, CHECK constraints, and indexes, andthis 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
1 parentafaccbb commit97390fe

File tree

4 files changed

+56
-20
lines changed

4 files changed

+56
-20
lines changed

‎src/backend/parser/parse_utilcmd.c

Lines changed: 22 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,7 @@ typedef struct
8686
List*ckconstraints;/* CHECK constraints */
8787
List*fkconstraints;/* FOREIGN KEY constraints */
8888
List*ixconstraints;/* index-creating constraints */
89+
List*likeclauses;/* LIKE clauses that need post-processing */
8990
List*extstats;/* cloned extended statistics */
9091
List*blist;/* "before list" of things to do before
9192
* creating the table */
@@ -243,6 +244,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
243244
cxt.ckconstraints=NIL;
244245
cxt.fkconstraints=NIL;
245246
cxt.ixconstraints=NIL;
247+
cxt.likeclauses=NIL;
246248
cxt.extstats=NIL;
247249
cxt.blist=NIL;
248250
cxt.alist=NIL;
@@ -309,6 +311,20 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
309311
*/
310312
transformIndexConstraints(&cxt);
311313

314+
/*
315+
* Re-consideration of LIKE clauses should happen after creation of
316+
* indexes, but before creation of foreign keys. This order is critical
317+
* because a LIKE clause may attempt to create a primary key. If there's
318+
* also a pkey in the main CREATE TABLE list, creation of that will not
319+
* check for a duplicate at runtime (since index_check_primary_key()
320+
* expects that we rejected dups here). Creation of the LIKE-generated
321+
* pkey behaves like ALTER TABLE ADD, so it will check, but obviously that
322+
* only works if it happens second. On the other hand, we want to make
323+
* pkeys before foreign key constraints, in case the user tries to make a
324+
* self-referential FK.
325+
*/
326+
cxt.alist=list_concat(cxt.alist,cxt.likeclauses);
327+
312328
/*
313329
* Postprocess foreign-key constraints.
314330
*/
@@ -923,7 +939,7 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
923939
* Change the LIKE <srctable> portion of a CREATE TABLE statement into
924940
* column definitions that recreate the user defined column portions of
925941
* <srctable>. Also, if there are any LIKE options that we can't fully
926-
* process at this point, add the TableLikeClause to cxt->alist, which
942+
* process at this point, add the TableLikeClause to cxt->likeclauses, which
927943
* will cause utility.c to call expandTableLikeClause() after the new
928944
* table has been created.
929945
*/
@@ -1088,15 +1104,15 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
10881104
* We cannot yet deal with defaults, CHECK constraints, or indexes, since
10891105
* we don't yet know what column numbers the copied columns will have in
10901106
* the finished table. If any of those options are specified, add the
1091-
* LIKE clause to cxt->alist so that expandTableLikeClause will be called
1092-
* after we do know that.
1107+
* LIKE clause to cxt->likeclauses so that expandTableLikeClause will be
1108+
*calledafter we do know that.
10931109
*/
10941110
if (table_like_clause->options&
10951111
(CREATE_TABLE_LIKE_DEFAULTS |
10961112
CREATE_TABLE_LIKE_GENERATED |
10971113
CREATE_TABLE_LIKE_CONSTRAINTS |
10981114
CREATE_TABLE_LIKE_INDEXES))
1099-
cxt->alist=lappend(cxt->alist,table_like_clause);
1115+
cxt->likeclauses=lappend(cxt->likeclauses,table_like_clause);
11001116

11011117
/*
11021118
* We may copy extended statistics if requested, since the representation
@@ -2692,7 +2708,7 @@ transformFKConstraints(CreateStmtContext *cxt,
26922708
* Note: the ADD CONSTRAINT command must also execute after any index
26932709
* creation commands. Thus, this should run after
26942710
* transformIndexConstraints, so that the CREATE INDEX commands are
2695-
* already in cxt->alist.
2711+
* already in cxt->alist. See also the handling of cxt->likeclauses.
26962712
*/
26972713
if (!isAddConstraint)
26982714
{
@@ -3205,6 +3221,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
32053221
cxt.ckconstraints=NIL;
32063222
cxt.fkconstraints=NIL;
32073223
cxt.ixconstraints=NIL;
3224+
cxt.likeclauses=NIL;
32083225
cxt.extstats=NIL;
32093226
cxt.blist=NIL;
32103227
cxt.alist=NIL;

‎src/backend/tcop/utility.c

Lines changed: 13 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1138,17 +1138,22 @@ ProcessUtilitySlow(ParseState *pstate,
11381138
caseT_CreateForeignTableStmt:
11391139
{
11401140
List*stmts;
1141-
ListCell*l;
11421141
RangeVar*table_rv=NULL;
11431142

11441143
/* Run parse analysis ... */
11451144
stmts=transformCreateStmt((CreateStmt*)parsetree,
11461145
queryString);
11471146

1148-
/* ... and do it */
1149-
foreach(l,stmts)
1147+
/*
1148+
* ... and do it. We can't use foreach() because we may
1149+
* modify the list midway through, so pick off the
1150+
* elements one at a time, the hard way.
1151+
*/
1152+
while (stmts!=NIL)
11501153
{
1151-
Node*stmt= (Node*)lfirst(l);
1154+
Node*stmt= (Node*)linitial(stmts);
1155+
1156+
stmts=list_delete_first(stmts);
11521157

11531158
if (IsA(stmt,CreateStmt))
11541159
{
@@ -1214,23 +1219,16 @@ ProcessUtilitySlow(ParseState *pstate,
12141219
/*
12151220
* Do delayed processing of LIKE options. This
12161221
* will result in additional sub-statements for us
1217-
* to process.We can just tack those onto the
1218-
*to-do list.
1222+
* to process.Those should get done before any
1223+
*remaining actions, so prepend them to "stmts".
12191224
*/
12201225
TableLikeClause*like= (TableLikeClause*)stmt;
12211226
List*morestmts;
12221227

12231228
Assert(table_rv!=NULL);
12241229

12251230
morestmts=expandTableLikeClause(table_rv,like);
1226-
stmts=list_concat(stmts,morestmts);
1227-
1228-
/*
1229-
* We don't need a CCI now, besides which the "l"
1230-
* list pointer is now possibly invalid, so just
1231-
* skip the CCI test below.
1232-
*/
1233-
continue;
1231+
stmts=list_concat(morestmts,stmts);
12341232
}
12351233
else
12361234
{
@@ -1258,7 +1256,7 @@ ProcessUtilitySlow(ParseState *pstate,
12581256
}
12591257

12601258
/* Need CCI between commands */
1261-
if (lnext(stmts,l)!=NULL)
1259+
if (stmts!=NIL)
12621260
CommandCounterIncrement();
12631261
}
12641262

‎src/test/regress/expected/create_table_like.out

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -282,6 +282,22 @@ INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
282282
ERROR: duplicate key value violates unique constraint "inhg_x_key"
283283
DETAIL: Key (x)=(15) already exists.
284284
DROP TABLE inhg;
285+
DROP TABLE inhz;
286+
/* Use primary key imported by LIKE for self-referential FK constraint */
287+
CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
288+
\d inhz
289+
Table "public.inhz"
290+
Column | Type | Collation | Nullable | Default
291+
--------+------+-----------+----------+---------
292+
x | text | | |
293+
xx | text | | not null |
294+
Indexes:
295+
"inhz_pkey" PRIMARY KEY, btree (xx)
296+
Foreign-key constraints:
297+
"inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
298+
Referenced by:
299+
TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
300+
285301
DROP TABLE inhz;
286302
-- including storage and comments
287303
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);

‎src/test/regress/sql/create_table_like.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,11 @@ INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
114114
DROPTABLE inhg;
115115
DROPTABLE inhz;
116116

117+
/* Use primary key imported by LIKE for self-referential FK constraint*/
118+
CREATETABLEinhz (xtextREFERENCES inhz,LIKE inhx INCLUDING INDEXES);
119+
\d inhz
120+
DROPTABLE inhz;
121+
117122
-- including storage and comments
118123
CREATETABLEctlt1 (atextCHECK (length(a)>2)PRIMARY KEY, btext);
119124
CREATEINDEXctlt1_b_keyON ctlt1 (b);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp