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

Commit9dee48c

Browse files
committed
Retain comments on indexes and constraints at ALTER TABLE ... TYPE ...
When a column's datatype is changed, ATExecAlterColumnType() rebuilds allthe affected indexes and constraints, and the comments from the oldindexes/constraints were not carried over.To fix, create a synthetic COMMENT ON command in the work queue, to re-addany comments on constraints. For indexes, there's a comment field inIndexStmt that is used.This fixes bug #13126, reported by Kirill Simonov. Original patch byMichael Paquier, reviewed by Petr Jelinek and me. This bug is present inall versions, but only backpatch to 9.5. Given how minor the issue is, itdoesn't seem worth the work and risk to backpatch further than that.
1 parent6d5031e commit9dee48c

File tree

4 files changed

+163
-2
lines changed

4 files changed

+163
-2
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 63 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -386,6 +386,8 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
386386
staticvoidATPostAlterTypeParse(OidoldId,OidoldRelId,OidrefRelId,
387387
char*cmd,List**wqueue,LOCKMODElockmode,
388388
boolrewrite);
389+
staticvoidRebuildConstraintComment(AlteredTableInfo*tab,intpass,
390+
Oidobjid,Relationrel,char*conname);
389391
staticvoidTryReuseIndex(OidoldId,IndexStmt*stmt);
390392
staticvoidTryReuseForeignKey(OidoldId,Constraint*con);
391393
staticvoidchange_owner_fix_column_acls(OidrelationOid,
@@ -3514,6 +3516,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
35143516
ATExecAddConstraint(wqueue,tab,rel, (Constraint*)cmd->def,
35153517
false, true,lockmode);
35163518
break;
3519+
caseAT_ReAddComment:/* Re-add existing comment */
3520+
address=CommentObject((CommentStmt*)cmd->def);
3521+
break;
35173522
caseAT_AddIndexConstraint:/* ADD CONSTRAINT USING INDEX */
35183523
address=ATExecAddIndexConstraint(tab,rel, (IndexStmt*)cmd->def,
35193524
lockmode);
@@ -8654,6 +8659,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
86548659

86558660
if (!rewrite)
86568661
TryReuseIndex(oldId,stmt);
8662+
/* keep the index's comment */
8663+
stmt->idxcomment=GetComment(oldId,RelationRelationId,0);
86578664

86588665
newcmd=makeNode(AlterTableCmd);
86598666
newcmd->subtype=AT_ReAddIndex;
@@ -8672,15 +8679,29 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
86728679

86738680
if (cmd->subtype==AT_AddIndex)
86748681
{
8682+
IndexStmt*indstmt;
8683+
Oidindoid;
8684+
86758685
Assert(IsA(cmd->def,IndexStmt));
86768686

8687+
indstmt= (IndexStmt*)cmd->def;
8688+
indoid=get_constraint_index(oldId);
8689+
86778690
if (!rewrite)
8678-
TryReuseIndex(get_constraint_index(oldId),
8679-
(IndexStmt*)cmd->def);
8691+
TryReuseIndex(indoid,indstmt);
8692+
/* keep any comment on the index */
8693+
indstmt->idxcomment=GetComment(indoid,
8694+
RelationRelationId,0);
86808695

86818696
cmd->subtype=AT_ReAddIndex;
86828697
tab->subcmds[AT_PASS_OLD_INDEX]=
86838698
lappend(tab->subcmds[AT_PASS_OLD_INDEX],cmd);
8699+
8700+
/* recreate any comment on the constraint */
8701+
RebuildConstraintComment(tab,
8702+
AT_PASS_OLD_INDEX,
8703+
oldId,
8704+
rel,indstmt->idxname);
86848705
}
86858706
elseif (cmd->subtype==AT_AddConstraint)
86868707
{
@@ -8697,6 +8718,12 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
86978718
cmd->subtype=AT_ReAddConstraint;
86988719
tab->subcmds[AT_PASS_OLD_CONSTR]=
86998720
lappend(tab->subcmds[AT_PASS_OLD_CONSTR],cmd);
8721+
8722+
/* recreate any comment on the constraint */
8723+
RebuildConstraintComment(tab,
8724+
AT_PASS_OLD_CONSTR,
8725+
oldId,
8726+
rel,con->conname);
87008727
}
87018728
else
87028729
elog(ERROR,"unexpected statement type: %d",
@@ -8711,6 +8738,40 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
87118738
relation_close(rel,NoLock);
87128739
}
87138740

8741+
/*
8742+
* Subroutine for ATPostAlterTypeParse() to recreate a comment entry for
8743+
* a constraint that is being re-added.
8744+
*/
8745+
staticvoid
8746+
RebuildConstraintComment(AlteredTableInfo*tab,intpass,Oidobjid,
8747+
Relationrel,char*conname)
8748+
{
8749+
CommentStmt*cmd;
8750+
char*comment_str;
8751+
AlterTableCmd*newcmd;
8752+
8753+
/* Look for comment for object wanted, and leave if none */
8754+
comment_str=GetComment(objid,ConstraintRelationId,0);
8755+
if (comment_str==NULL)
8756+
return;
8757+
8758+
/* Build node CommentStmt */
8759+
cmd=makeNode(CommentStmt);
8760+
cmd->objtype=OBJECT_TABCONSTRAINT;
8761+
cmd->objname=list_make3(
8762+
makeString(get_namespace_name(RelationGetNamespace(rel))),
8763+
makeString(RelationGetRelationName(rel)),
8764+
makeString(conname));
8765+
cmd->objargs=NIL;
8766+
cmd->comment=comment_str;
8767+
8768+
/* Append it to list of commands */
8769+
newcmd=makeNode(AlterTableCmd);
8770+
newcmd->subtype=AT_ReAddComment;
8771+
newcmd->def= (Node*)cmd;
8772+
tab->subcmds[pass]=lappend(tab->subcmds[pass],newcmd);
8773+
}
8774+
87148775
/*
87158776
* Subroutine for ATPostAlterTypeParse(). Calls out to CheckIndexCompatible()
87168777
* for the real analysis, then mutates the IndexStmt based on that verdict.

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1474,6 +1474,7 @@ typedef enum AlterTableType
14741474
AT_AddConstraint,/* add constraint */
14751475
AT_AddConstraintRecurse,/* internal to commands/tablecmds.c */
14761476
AT_ReAddConstraint,/* internal to commands/tablecmds.c */
1477+
AT_ReAddComment,/* internal to commands/tablecmds.c */
14771478
AT_AlterConstraint,/* alter constraint */
14781479
AT_ValidateConstraint,/* validate constraint */
14791480
AT_ValidateConstraintRecurse,/* internal to commands/tablecmds.c */

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

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2400,6 +2400,69 @@ Check constraints:
24002400

24012401
DROP TABLE alter2.tt8;
24022402
DROP SCHEMA alter2;
2403+
-- Check that comments on constraints and indexes are not lost at ALTER TABLE.
2404+
CREATE TABLE comment_test (
2405+
id int,
2406+
positive_col int CHECK (positive_col > 0),
2407+
indexed_col int,
2408+
CONSTRAINT comment_test_pk PRIMARY KEY (id));
2409+
CREATE INDEX comment_test_index ON comment_test(indexed_col);
2410+
COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
2411+
COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
2412+
COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
2413+
COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
2414+
COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
2415+
SELECT col_description('comment_test'::regclass, 1) as comment;
2416+
comment
2417+
-----------------------------
2418+
Column 'id' on comment_test
2419+
(1 row)
2420+
2421+
SELECT indexrelid::regclass as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass;
2422+
index | comment
2423+
--------------------+-----------------------------------------------
2424+
comment_test_index | Simple index on comment_test
2425+
comment_test_pk | Index backing the PRIMARY KEY of comment_test
2426+
(2 rows)
2427+
2428+
SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass;
2429+
constraint | comment
2430+
---------------------------------+-----------------------------------------------
2431+
comment_test_pk | PRIMARY KEY constraint of comment_test
2432+
comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
2433+
(2 rows)
2434+
2435+
-- Change the datatype of all the columns. ALTER TABLE is optimized to not
2436+
-- rebuild an index if the new data type is binary compatible with the old
2437+
-- one. Check do a dummy ALTER TABLE that doesn't change the datatype
2438+
-- first, to test that no-op codepath, and another one that does.
2439+
ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
2440+
ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
2441+
ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
2442+
ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
2443+
ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
2444+
ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
2445+
-- Check that the comments are intact.
2446+
SELECT col_description('comment_test'::regclass, 1) as comment;
2447+
comment
2448+
-----------------------------
2449+
Column 'id' on comment_test
2450+
(1 row)
2451+
2452+
SELECT indexrelid::regclass as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass;
2453+
index | comment
2454+
--------------------+-----------------------------------------------
2455+
comment_test_pk | Index backing the PRIMARY KEY of comment_test
2456+
comment_test_index | Simple index on comment_test
2457+
(2 rows)
2458+
2459+
SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass;
2460+
constraint | comment
2461+
---------------------------------+-----------------------------------------------
2462+
comment_test_positive_col_check | CHECK constraint on comment_test.positive_col
2463+
comment_test_pk | PRIMARY KEY constraint of comment_test
2464+
(2 rows)
2465+
24032466
-- Check that we map relation oids to filenodes and back correctly. Only
24042467
-- display bad mappings so the test output doesn't change all the time. A
24052468
-- filenode function call can return NULL for a relation dropped concurrently

‎src/test/regress/sql/alter_table.sql

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1594,6 +1594,42 @@ ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
15941594
DROPTABLEalter2.tt8;
15951595
DROPSCHEMA alter2;
15961596

1597+
1598+
-- Check that comments on constraints and indexes are not lost at ALTER TABLE.
1599+
CREATETABLEcomment_test (
1600+
idint,
1601+
positive_colintCHECK (positive_col>0),
1602+
indexed_colint,
1603+
CONSTRAINT comment_test_pkPRIMARY KEY (id));
1604+
CREATEINDEXcomment_test_indexON comment_test(indexed_col);
1605+
1606+
COMMENT ON COLUMN comment_test.id IS'Column''id'' on comment_test';
1607+
COMMENT ON INDEX comment_test_index IS'Simple index on comment_test';
1608+
COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS'CHECK constraint on comment_test.positive_col';
1609+
COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS'PRIMARY KEY constraint of comment_test';
1610+
COMMENT ON INDEX comment_test_pk IS'Index backing the PRIMARY KEY of comment_test';
1611+
1612+
SELECT col_description('comment_test'::regclass,1)as comment;
1613+
SELECT indexrelid::regclassas index, obj_description(indexrelid,'pg_class')as commentFROM pg_indexwhere indrelid='comment_test'::regclass;
1614+
SELECT connameasconstraint, obj_description(oid,'pg_constraint')as commentFROM pg_constraintwhere conrelid='comment_test'::regclass;
1615+
1616+
-- Change the datatype of all the columns. ALTER TABLE is optimized to not
1617+
-- rebuild an index if the new data type is binary compatible with the old
1618+
-- one. Check do a dummy ALTER TABLE that doesn't change the datatype
1619+
-- first, to test that no-op codepath, and another one that does.
1620+
ALTERTABLE comment_test ALTER COLUMN indexed_colSET DATA TYPEint;
1621+
ALTERTABLE comment_test ALTER COLUMN indexed_colSET DATA TYPEtext;
1622+
ALTERTABLE comment_test ALTER COLUMN idSET DATA TYPEint;
1623+
ALTERTABLE comment_test ALTER COLUMN idSET DATA TYPEtext;
1624+
ALTERTABLE comment_test ALTER COLUMN positive_colSET DATA TYPEint;
1625+
ALTERTABLE comment_test ALTER COLUMN positive_colSET DATA TYPEbigint;
1626+
1627+
-- Check that the comments are intact.
1628+
SELECT col_description('comment_test'::regclass,1)as comment;
1629+
SELECT indexrelid::regclassas index, obj_description(indexrelid,'pg_class')as commentFROM pg_indexwhere indrelid='comment_test'::regclass;
1630+
SELECT connameasconstraint, obj_description(oid,'pg_constraint')as commentFROM pg_constraintwhere conrelid='comment_test'::regclass;
1631+
1632+
15971633
-- Check that we map relation oids to filenodes and back correctly. Only
15981634
-- display bad mappings so the test output doesn't change all the time. A
15991635
-- filenode function call can return NULL for a relation dropped concurrently

0 commit comments

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