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

Commitb048326

Browse files
committed
Add support for SET ACCESS METHOD in ALTER TABLE
The logic used to support a change of access method for a table issimilar to changes for tablespace or relation persistence, requiring atable rewrite with an exclusive lock of the relation changed. Tablerewrites done in ALTER TABLE already go through the table AM layer whenscanning tuples from the old relation and inserting them into the newone, making this implementation straight-forward.Note that partitioned tables are not supported as these have no accessmethods defined.Author: Justin Pryzby, Jeff DavisReviewed-by: Michael Paquier, Vignesh CDiscussion:https://postgr.es/m/20210228222530.GD20769@telsasoft.com
1 parent4b763ff commitb048326

File tree

11 files changed

+173
-15
lines changed

11 files changed

+173
-15
lines changed

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
7575
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
7676
SET WITHOUT CLUSTER
7777
SET WITHOUT OIDS
78+
SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
7879
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
7980
SET { LOGGED | UNLOGGED }
8081
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -692,6 +693,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
692693
</listitem>
693694
</varlistentry>
694695

696+
<varlistentry>
697+
<term><literal>SET ACCESS METHOD</literal></term>
698+
<listitem>
699+
<para>
700+
This form changes the access method of the table by rewriting it. See
701+
<xref linkend="tableam"/> for more information.
702+
</para>
703+
</listitem>
704+
</varlistentry>
705+
695706
<varlistentry>
696707
<term><literal>SET TABLESPACE</literal></term>
697708
<listitem>
@@ -1228,6 +1239,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12281239
</listitem>
12291240
</varlistentry>
12301241

1242+
<varlistentry>
1243+
<term><replaceable class="parameter">new_access_method</replaceable></term>
1244+
<listitem>
1245+
<para>
1246+
The name of the access method to which the table will be converted.
1247+
</para>
1248+
</listitem>
1249+
</varlistentry>
1250+
12311251
<varlistentry>
12321252
<term><replaceable class="parameter">new_tablespace</replaceable></term>
12331253
<listitem>

‎src/backend/commands/cluster.c

Lines changed: 15 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -576,6 +576,7 @@ static void
576576
rebuild_relation(RelationOldHeap,OidindexOid,boolverbose)
577577
{
578578
OidtableOid=RelationGetRelid(OldHeap);
579+
OidaccessMethod=OldHeap->rd_rel->relam;
579580
OidtableSpace=OldHeap->rd_rel->reltablespace;
580581
OidOIDNewHeap;
581582
charrelpersistence;
@@ -597,6 +598,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
597598

598599
/* Create the transient table that will receive the re-ordered data */
599600
OIDNewHeap=make_new_heap(tableOid,tableSpace,
601+
accessMethod,
600602
relpersistence,
601603
AccessExclusiveLock);
602604

@@ -618,16 +620,16 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
618620
/*
619621
* Create the transient table that will be filled with new data during
620622
* CLUSTER, ALTER TABLE, and similar operations. The transient table
621-
* duplicates the logical structure of the OldHeap, butis placed in
622-
*NewTableSpace which might be different from OldHeap's. Also, it's built
623-
*with the specified persistence, which might differ from the original's.
623+
* duplicates the logical structure of the OldHeap; butwill have the
624+
*specified physical storage properties NewTableSpace, NewAccessMethod, and
625+
*relpersistence.
624626
*
625627
* After this, the caller should load the new heap with transferred/modified
626628
* data, then call finish_heap_swap to complete the operation.
627629
*/
628630
Oid
629-
make_new_heap(OidOIDOldHeap,OidNewTableSpace,charrelpersistence,
630-
LOCKMODElockmode)
631+
make_new_heap(OidOIDOldHeap,OidNewTableSpace,OidNewAccessMethod,
632+
charrelpersistence,LOCKMODElockmode)
631633
{
632634
TupleDescOldHeapDesc;
633635
charNewHeapName[NAMEDATALEN];
@@ -686,7 +688,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
686688
InvalidOid,
687689
InvalidOid,
688690
OldHeap->rd_rel->relowner,
689-
OldHeap->rd_rel->relam,
691+
NewAccessMethod,
690692
OldHeapDesc,
691693
NIL,
692694
RELKIND_RELATION,
@@ -1036,6 +1038,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
10361038
relform1->reltablespace=relform2->reltablespace;
10371039
relform2->reltablespace=swaptemp;
10381040

1041+
swaptemp=relform1->relam;
1042+
relform1->relam=relform2->relam;
1043+
relform2->relam=swaptemp;
1044+
10391045
swptmpchr=relform1->relpersistence;
10401046
relform1->relpersistence=relform2->relpersistence;
10411047
relform2->relpersistence=swptmpchr;
@@ -1071,6 +1077,9 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
10711077
if (relform1->relpersistence!=relform2->relpersistence)
10721078
elog(ERROR,"cannot change persistence of mapped relation \"%s\"",
10731079
NameStr(relform1->relname));
1080+
if (relform1->relam!=relform2->relam)
1081+
elog(ERROR,"cannot change access method of mapped relation \"%s\"",
1082+
NameStr(relform1->relname));
10741083
if (!swap_toast_by_content&&
10751084
(relform1->reltoastrelid||relform2->reltoastrelid))
10761085
elog(ERROR,"cannot swap toast by links for mapped relation \"%s\"",

‎src/backend/commands/matview.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -298,8 +298,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
298298
* it against access by any other process until commit (by which time it
299299
* will be gone).
300300
*/
301-
OIDNewHeap=make_new_heap(matviewOid,tableSpace,relpersistence,
302-
ExclusiveLock);
301+
OIDNewHeap=make_new_heap(matviewOid,tableSpace,
302+
matviewRel->rd_rel->relam,
303+
relpersistence,ExclusiveLock);
303304
LockRelationOid(OIDNewHeap,AccessExclusiveLock);
304305
dest=CreateTransientRelDestReceiver(OIDNewHeap);
305306

‎src/backend/commands/tablecmds.c

Lines changed: 63 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -176,6 +176,7 @@ typedef struct AlteredTableInfo
176176
List *afterStmts;/* List of utility command parsetrees */
177177
boolverify_new_notnull; /* T if we should recheck NOT NULL */
178178
intrewrite;/* Reason for forced rewrite, if any */
179+
OidnewAccessMethod;/* new access method; 0 means no change */
179180
OidnewTableSpace;/* new tablespace; 0 means no change */
180181
boolchgPersistence; /* T if SET LOGGED/UNLOGGED is used */
181182
charnewrelpersistence;/* if above is true */
@@ -538,6 +539,7 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
538539
static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
539540
LOCKMODE lockmode);
540541
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
542+
static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
541543
static bool ATPrepChangePersistence(Relation rel, bool toLogged);
542544
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
543545
const char *tablespacename, LOCKMODE lockmode);
@@ -4096,6 +4098,7 @@ AlterTableGetLockLevel(List *cmds)
40964098
*/
40974099
case AT_AddColumn:/* may rewrite heap, in some cases and visible
40984100
* to SELECT */
4101+
case AT_SetAccessMethod:/* must rewrite heap */
40994102
case AT_SetTableSpace:/* must rewrite heap */
41004103
case AT_AlterColumnType:/* must rewrite heap */
41014104
cmd_lockmode = AccessExclusiveLock;
@@ -4622,6 +4625,24 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
46224625
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
46234626
pass = AT_PASS_DROP;
46244627
break;
4628+
case AT_SetAccessMethod:/* SET ACCESS METHOD */
4629+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
4630+
4631+
/* partitioned tables don't have an access method */
4632+
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
4633+
ereport(ERROR,
4634+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
4635+
errmsg("cannot change access method of a partitioned table")));
4636+
4637+
/* check if another access method change was already requested */
4638+
if (OidIsValid(tab->newAccessMethod))
4639+
ereport(ERROR,
4640+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4641+
errmsg("cannot have multiple SET ACCESS METHOD subcommands")));
4642+
4643+
ATPrepSetAccessMethod(tab, rel, cmd->name);
4644+
pass = AT_PASS_MISC;/* does not matter; no work in Phase 2 */
4645+
break;
46254646
case AT_SetTableSpace:/* SET TABLESPACE */
46264647
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX |
46274648
ATT_PARTITIONED_INDEX);
@@ -4997,6 +5018,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
49975018
case AT_DropOids:/* SET WITHOUT OIDS */
49985019
/* nothing to do here, oid columns don't exist anymore */
49995020
break;
5021+
case AT_SetAccessMethod:/* SET ACCESS METHOD */
5022+
/* handled specially in Phase 3 */
5023+
break;
50005024
case AT_SetTableSpace:/* SET TABLESPACE */
50015025

50025026
/*
@@ -5324,7 +5348,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
53245348

53255349
/*
53265350
* We only need to rewrite the table if at least one column needs to
5327-
* be recomputed, or we are changing its persistence.
5351+
* be recomputed, or we are changing its persistence or access method.
53285352
*
53295353
* There are two reasons for requiring a rewrite when changing
53305354
* persistence: on one hand, we need to ensure that the buffers
@@ -5338,6 +5362,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
53385362
/* Build a temporary relation and copy data */
53395363
RelationOldHeap;
53405364
OidOIDNewHeap;
5365+
OidNewAccessMethod;
53415366
OidNewTableSpace;
53425367
charpersistence;
53435368

@@ -5378,6 +5403,15 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
53785403
else
53795404
NewTableSpace = OldHeap->rd_rel->reltablespace;
53805405

5406+
/*
5407+
* Select destination access method (same as original unless user
5408+
* requested a change)
5409+
*/
5410+
if (OidIsValid(tab->newAccessMethod))
5411+
NewAccessMethod = tab->newAccessMethod;
5412+
else
5413+
NewAccessMethod = OldHeap->rd_rel->relam;
5414+
53815415
/*
53825416
* Select persistence of transient table (same as original unless
53835417
* user requested a change)
@@ -5417,8 +5451,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
54175451
* persistence. That wouldn't work for pg_class, but that can't be
54185452
* unlogged anyway.
54195453
*/
5420-
OIDNewHeap = make_new_heap(tab->relid, NewTableSpace,persistence,
5421-
lockmode);
5454+
OIDNewHeap = make_new_heap(tab->relid, NewTableSpace,NewAccessMethod,
5455+
persistence,lockmode);
54225456

54235457
/*
54245458
* Copy the heap data into the new table with the desired
@@ -5933,6 +5967,8 @@ ATGetQueueEntry(List **wqueue, Relation rel)
59335967
tab->rel = NULL;/* set later */
59345968
tab->relkind = rel->rd_rel->relkind;
59355969
tab->oldDesc = CreateTupleDescCopyConstr(RelationGetDescr(rel));
5970+
tab->newAccessMethod = InvalidOid;
5971+
tab->newTableSpace = InvalidOid;
59365972
tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
59375973
tab->chgPersistence = false;
59385974

@@ -6003,6 +6039,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
60036039
return "CLUSTER ON";
60046040
case AT_DropCluster:
60056041
return "SET WITHOUT CLUSTER";
6042+
case AT_SetAccessMethod:
6043+
return "SET ACCESS METHOD";
60066044
case AT_SetLogged:
60076045
return "SET LOGGED";
60086046
case AT_SetUnLogged:
@@ -13609,6 +13647,28 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
1360913647
mark_index_clustered(rel, InvalidOid, false);
1361013648
}
1361113649

13650+
/*
13651+
* Preparation phase for SET ACCESS METHOD
13652+
*
13653+
* Check that access method exists. If it is the same as the table's current
13654+
* access method, it is a no-op. Otherwise, a table rewrite is necessary.
13655+
*/
13656+
static void
13657+
ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
13658+
{
13659+
Oidamoid;
13660+
13661+
/* Check that the table access method exists */
13662+
amoid = get_table_am_oid(amname, false);
13663+
13664+
if (rel->rd_rel->relam == amoid)
13665+
return;
13666+
13667+
/* Save info for Phase 3 to do the real work */
13668+
tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
13669+
tab->newAccessMethod = amoid;
13670+
}
13671+
1361213672
/*
1361313673
* ALTER TABLE SET TABLESPACE
1361413674
*/

‎src/backend/parser/gram.y

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2623,6 +2623,14 @@ alter_table_cmd:
26232623
n->newowner =$3;
26242624
$$ = (Node *)n;
26252625
}
2626+
/* ALTER TABLE <name> SET ACCESS METHOD <amname>*/
2627+
|SETACCESSMETHODname
2628+
{
2629+
AlterTableCmd *n = makeNode(AlterTableCmd);
2630+
n->subtype = AT_SetAccessMethod;
2631+
n->name =$4;
2632+
$$ = (Node *)n;
2633+
}
26262634
/* ALTER TABLE <name> SET TABLESPACE <tablespacename>*/
26272635
|SETTABLESPACEname
26282636
{

‎src/bin/psql/tab-complete.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2141,8 +2141,15 @@ psql_completion(const char *text, int start, int end)
21412141
}
21422142
/* If we have ALTER TABLE <sth> SET, provide list of attributes and '(' */
21432143
elseif (Matches("ALTER","TABLE",MatchAny,"SET"))
2144-
COMPLETE_WITH("(","LOGGED","SCHEMA","TABLESPACE","UNLOGGED",
2145-
"WITH","WITHOUT");
2144+
COMPLETE_WITH("(","ACCESS METHOD","LOGGED","SCHEMA",
2145+
"TABLESPACE","UNLOGGED","WITH","WITHOUT");
2146+
2147+
/*
2148+
* If we have ALTER TABLE <smt> SET ACCESS METHOD provide a list of table
2149+
* AMs.
2150+
*/
2151+
elseif (Matches("ALTER","TABLE",MatchAny,"SET","ACCESS","METHOD"))
2152+
COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
21462153

21472154
/*
21482155
* If we have ALTER TABLE <sth> SET TABLESPACE provide a list of

‎src/include/commands/cluster.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -35,8 +35,8 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
3535
boolrecheck,LOCKMODElockmode);
3636
externvoidmark_index_clustered(Relationrel,OidindexOid,boolis_internal);
3737

38-
externOidmake_new_heap(OidOIDOldHeap,OidNewTableSpace,charrelpersistence,
39-
LOCKMODElockmode);
38+
externOidmake_new_heap(OidOIDOldHeap,OidNewTableSpace,OidNewAccessMethod,
39+
charrelpersistence,LOCKMODElockmode);
4040
externvoidfinish_heap_swap(OidOIDOldHeap,OidOIDNewHeap,
4141
boolis_system_catalog,
4242
boolswap_toast_by_content,

‎src/include/commands/event_trigger.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ typedef struct EventTriggerData
3232
#defineAT_REWRITE_ALTER_PERSISTENCE0x01
3333
#defineAT_REWRITE_DEFAULT_VAL0x02
3434
#defineAT_REWRITE_COLUMN_REWRITE0x04
35+
#defineAT_REWRITE_ACCESS_METHOD0x08
3536

3637
/*
3738
* EventTriggerData is the node type that is passed as fmgr "context" info

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1901,6 +1901,7 @@ typedef enum AlterTableType
19011901
AT_SetLogged,/* SET LOGGED */
19021902
AT_SetUnLogged,/* SET UNLOGGED */
19031903
AT_DropOids,/* SET WITHOUT OIDS */
1904+
AT_SetAccessMethod,/* SET ACCESS METHOD */
19041905
AT_SetTableSpace,/* SET TABLESPACE */
19051906
AT_SetRelOptions,/* SET (...) -- AM specific parameters */
19061907
AT_ResetRelOptions,/* RESET (...) -- AM specific parameters */

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -230,6 +230,40 @@ ORDER BY classid, objid, objsubid;
230230
table tableam_parted_d_heap2
231231
(5 rows)
232232

233+
-- ALTER TABLE SET ACCESS METHOD
234+
CREATE TABLE heaptable USING heap AS
235+
SELECT a, repeat(a::text, 100) FROM generate_series(1,9) AS a;
236+
SELECT amname FROM pg_class c, pg_am am
237+
WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
238+
amname
239+
--------
240+
heap
241+
(1 row)
242+
243+
ALTER TABLE heaptable SET ACCESS METHOD heap2;
244+
SELECT amname FROM pg_class c, pg_am am
245+
WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
246+
amname
247+
--------
248+
heap2
249+
(1 row)
250+
251+
SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
252+
count | count
253+
-------+-------
254+
9 | 1
255+
(1 row)
256+
257+
-- No support for multiple subcommands
258+
ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
259+
ERROR: cannot have multiple SET ACCESS METHOD subcommands
260+
DROP TABLE heaptable;
261+
-- No support for partitioned tables.
262+
CREATE TABLE am_partitioned(x INT, y INT)
263+
PARTITION BY hash (x);
264+
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
265+
ERROR: cannot change access method of a partitioned table
266+
DROP TABLE am_partitioned;
233267
-- Second, create objects in the new AM by changing the default AM
234268
BEGIN;
235269
SET LOCAL default_table_access_method = 'heap2';

‎src/test/regress/sql/create_am.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,23 @@ WHERE pg_depend.refclassid = 'pg_am'::regclass
161161
ANDpg_am.amname='heap2'
162162
ORDER BY classid, objid, objsubid;
163163

164+
-- ALTER TABLE SET ACCESS METHOD
165+
CREATETABLEheaptable USING heapAS
166+
SELECT a, repeat(a::text,100)FROM generate_series(1,9)AS a;
167+
SELECT amnameFROM pg_class c, pg_am am
168+
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
169+
ALTERTABLE heaptableSET ACCESS METHOD heap2;
170+
SELECT amnameFROM pg_class c, pg_am am
171+
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
172+
SELECTCOUNT(a),COUNT(1) FILTER(WHERE a=1)FROM heaptable;
173+
-- No support for multiple subcommands
174+
ALTERTABLE heaptableSET ACCESS METHOD heap,SET ACCESS METHOD heap2;
175+
DROPTABLE heaptable;
176+
-- No support for partitioned tables.
177+
CREATETABLEam_partitioned(xINT, yINT)
178+
PARTITION BY hash (x);
179+
ALTERTABLE am_partitionedSET ACCESS METHOD heap2;
180+
DROPTABLE am_partitioned;
164181

165182
-- Second, create objects in the new AM by changing the default AM
166183
BEGIN;

0 commit comments

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