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

Commitd31e2a4

Browse files
committed
Teach ALTER TABLE .. SET DATA TYPE to avoid some table rewrites.
When the old type is binary coercible to the new type and the usingclause does not change the column contents, we can avoid a full tablerewrite, though any indexes on the affected columns will still needto be rebuilt. This applies, for example, when changing a varcharcolumn to be of type text.The prior coding assumed that the set of operations that force arewrite is identical to the set of operations that must be propagatedto tables making use of the affected table's rowtype. This isno longer true: even though the tuples in those tables wouldn'tneed to be modified, the data type change invalidate indexes builtusing those composite type columns. Indexes on the table we'reactually modifying can be invalidated too, of course, but theexisting machinery is sufficient to handle that case.Along the way, add some debugging messages that make it possibleto understand what operations ALTER TABLE is actually performingin these cases.Noah Misch and Robert Haas
1 parent24d1280 commitd31e2a4

File tree

3 files changed

+83
-28
lines changed

3 files changed

+83
-28
lines changed

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -766,9 +766,13 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
766766
<para>
767767
Adding a column with a non-null default or changing the type of an
768768
existing column will require the entire table and indexes to be rewritten.
769-
This might take a significant amount of time for a large table; and it will
770-
temporarily require double the disk space. Adding or removing a system
771-
<literal>oid</> column likewise requires rewriting the entire table.
769+
As an exception, if the old type type is binary coercible to the new
770+
type and the <literal>USING</> clause does not change the column contents,
771+
a table rewrite is not needed, but any indexes on the affected columns
772+
must still be rebuilt. Adding or removing a system <literal>oid</> column
773+
also requires rewriting the entire table. Table and/or index rebuilds may
774+
take a significant amount of time for a large table; and will temporarily
775+
require as much as double the disk space.
772776
</para>
773777

774778
<para>
@@ -797,9 +801,9 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
797801
<para>
798802
To force an immediate rewrite of the table, you can use
799803
<link linkend="SQL-VACUUM">VACUUM FULL</>, <xref linkend="SQL-CLUSTER">
800-
or one of the forms of ALTER TABLE that forces a rewrite, such as
801-
SET DATA TYPE. This results inno semantically-visible change in the
802-
table, but gets rid ofno-longer-useful data.
804+
or one of the forms of ALTER TABLE that forces a rewrite. This results in
805+
no semantically-visible change in the table, but gets rid of
806+
no-longer-useful data.
803807
</para>
804808

805809
<para>

‎src/backend/catalog/index.c‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1685,6 +1685,11 @@ index_build(Relation heapRelation,
16851685
procedure=indexRelation->rd_am->ambuild;
16861686
Assert(RegProcedureIsValid(procedure));
16871687

1688+
ereport(DEBUG1,
1689+
(errmsg("building index \"%s\" on table \"%s\"",
1690+
RelationGetRelationName(indexRelation),
1691+
RelationGetRelationName(heapRelation))));
1692+
16881693
/*
16891694
* Switch to the table owner's userid, so that any index functions are run
16901695
* as that user. Also lock down security-restricted operations and

‎src/backend/commands/tablecmds.c‎

Lines changed: 68 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -141,7 +141,7 @@ typedef struct AlteredTableInfo
141141
List*constraints;/* List of NewConstraint */
142142
List*newvals;/* List of NewColumnValue */
143143
boolnew_notnull;/* T if we added new NOT NULL constraints */
144-
boolnew_changeoids;/* T if weadded/dropped the OID column */
144+
boolrewrite;/* T if wea rewrite is forced */
145145
OidnewTableSpace;/* new tablespace; 0 means no change */
146146
/* Objects to rebuild after completing ALTER TYPE operations */
147147
List*changedConstraintOids;/* OIDs of constraints to rebuild */
@@ -336,6 +336,7 @@ static void ATPrepAlterColumnType(List **wqueue,
336336
AlteredTableInfo*tab,Relationrel,
337337
boolrecurse,boolrecursing,
338338
AlterTableCmd*cmd,LOCKMODElockmode);
339+
staticboolATColumnChangeRequiresRewrite(Node*expr,AttrNumbervarattno);
339340
staticvoidATExecAlterColumnType(AlteredTableInfo*tab,Relationrel,
340341
constchar*colName,TypeName*typeName,LOCKMODElockmode);
341342
staticvoidATPostAlterTypeCleanup(List**wqueue,AlteredTableInfo*tab,LOCKMODElockmode);
@@ -3218,11 +3219,34 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
32183219
if (tab->relkind==RELKIND_FOREIGN_TABLE)
32193220
continue;
32203221

3222+
/*
3223+
* If we change column data types or add/remove OIDs, the operation
3224+
* has to be propagated to tables that use this table's rowtype as a
3225+
* column type. tab->newvals will also be non-NULL in the case where
3226+
* we're adding a column with a default. We choose to forbid that
3227+
* case as well, since composite types might eventually support
3228+
* defaults.
3229+
*
3230+
* (Eventually we'll probably need to check for composite type
3231+
* dependencies even when we're just scanning the table without a
3232+
* rewrite, but at the moment a composite type does not enforce any
3233+
* constraints, so it's not necessary/appropriate to enforce them
3234+
* just during ALTER.)
3235+
*/
3236+
if (tab->newvals!=NIL||tab->rewrite)
3237+
{
3238+
Relationrel;
3239+
3240+
rel=heap_open(tab->relid,NoLock);
3241+
find_composite_type_dependencies(rel->rd_rel->reltype,rel,NULL);
3242+
heap_close(rel,NoLock);
3243+
}
3244+
32213245
/*
32223246
* We only need to rewrite the table if at least one column needs to
32233247
* be recomputed, or we are adding/removing the OID column.
32243248
*/
3225-
if (tab->newvals!=NIL||tab->new_changeoids)
3249+
if (tab->rewrite)
32263250
{
32273251
/* Build a temporary relation and copy data */
32283252
RelationOldHeap;
@@ -3408,22 +3432,6 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
34083432
hi_options=0;
34093433
}
34103434

3411-
/*
3412-
* If we change column data types or add/remove OIDs, the operation has to
3413-
* be propagated to tables that use this table's rowtype as a column type.
3414-
* newrel will also be non-NULL in the case where we're adding a column
3415-
* with a default. We choose to forbid that case as well, since composite
3416-
* types might eventually support defaults.
3417-
*
3418-
* (Eventually we'll probably need to check for composite type
3419-
* dependencies even when we're just scanning the table without a rewrite,
3420-
* but at the moment a composite type does not enforce any constraints,
3421-
* so it's not necessary/appropriate to enforce them just during ALTER.)
3422-
*/
3423-
if (newrel)
3424-
find_composite_type_dependencies(oldrel->rd_rel->reltype,
3425-
oldrel,NULL);
3426-
34273435
/*
34283436
* Generate the constraint and default execution states
34293437
*/
@@ -3490,6 +3498,15 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
34903498
List*dropped_attrs=NIL;
34913499
ListCell*lc;
34923500

3501+
if (newrel)
3502+
ereport(DEBUG1,
3503+
(errmsg("rewriting table \"%s\"",
3504+
RelationGetRelationName(oldrel))));
3505+
else
3506+
ereport(DEBUG1,
3507+
(errmsg("verifying table \"%s\"",
3508+
RelationGetRelationName(oldrel))));
3509+
34933510
econtext=GetPerTupleExprContext(estate);
34943511

34953512
/*
@@ -3532,7 +3549,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
35323549

35333550
while ((tuple=heap_getnext(scan,ForwardScanDirection))!=NULL)
35343551
{
3535-
if (newrel)
3552+
if (tab->rewrite)
35363553
{
35373554
OidtupOid=InvalidOid;
35383555

@@ -4330,6 +4347,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
43304347
newval->expr=defval;
43314348

43324349
tab->newvals=lappend(tab->newvals,newval);
4350+
tab->rewrite= true;
43334351
}
43344352

43354353
/*
@@ -4346,7 +4364,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
43464364
* table to fix that.
43474365
*/
43484366
if (isOid)
4349-
tab->new_changeoids= true;
4367+
tab->rewrite= true;
43504368

43514369
/*
43524370
* Add needed dependency entries for the new column.
@@ -5019,7 +5037,7 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
50195037
tab=ATGetQueueEntry(wqueue,rel);
50205038

50215039
/* Tell Phase 3 to physically remove the OID column */
5022-
tab->new_changeoids= true;
5040+
tab->rewrite= true;
50235041
}
50245042
}
50255043

@@ -5043,7 +5061,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
50435061
/* suppress schema rights check when rebuilding existing index */
50445062
check_rights= !is_rebuild;
50455063
/* skip index build if phase 3 will have to rewrite table anyway */
5046-
skip_build=(tab->newvals!=NIL);
5064+
skip_build=tab->rewrite;
50475065
/* suppress notices when rebuilding existing index */
50485066
quiet=is_rebuild;
50495067

@@ -6002,6 +6020,9 @@ validateForeignKeyConstraint(char *conname,
60026020
HeapTupletuple;
60036021
Triggertrig;
60046022

6023+
ereport(DEBUG1,
6024+
(errmsg("validating foreign key constraint \"%s\"",conname)));
6025+
60056026
/*
60066027
* Build a trigger call structure; we'll need it either way.
60076028
*/
@@ -6560,6 +6581,8 @@ ATPrepAlterColumnType(List **wqueue,
65606581
newval->expr= (Expr*)transform;
65616582

65626583
tab->newvals=lappend(tab->newvals,newval);
6584+
if (ATColumnChangeRequiresRewrite(transform,attnum))
6585+
tab->rewrite= true;
65636586
}
65646587
elseif (tab->relkind==RELKIND_FOREIGN_TABLE)
65656588
{
@@ -6599,6 +6622,29 @@ ATPrepAlterColumnType(List **wqueue,
65996622
ATTypedTableRecursion(wqueue,rel,cmd,lockmode);
66006623
}
66016624

6625+
/*
6626+
* When the data type of a column is changed, a rewrite might not be require
6627+
* if the data type is being changed to its current type, or more interestingly
6628+
* to a type to which it is binary coercible. But we must check carefully that
6629+
* the USING clause isn't trying to insert some other value.
6630+
*/
6631+
staticbool
6632+
ATColumnChangeRequiresRewrite(Node*expr,AttrNumbervarattno)
6633+
{
6634+
Assert(expr!=NULL);
6635+
6636+
for (;;)
6637+
{
6638+
/* only one varno, so no need to check that */
6639+
if (IsA(expr,Var)&& ((Var*)expr)->varattno==varattno)
6640+
return false;
6641+
elseif (IsA(expr,RelabelType))
6642+
expr= (Node*) ((RelabelType*)expr)->arg;
6643+
else
6644+
return true;
6645+
}
6646+
}
6647+
66026648
staticvoid
66036649
ATExecAlterColumnType(AlteredTableInfo*tab,Relationrel,
66046650
constchar*colName,TypeName*typeName,LOCKMODElockmode)

0 commit comments

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