pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH] ALTER TABLE SET ACCESS METHOD
6+ messages / 5 participants
[nested] [flat]
Related threads (commitfest entry ALTER TABLE ... SET ACCESS METHOD/3110):
alter table set TABLE ACCESS METHOD
* [PATCH] ALTER TABLE SET ACCESS METHOD
@ 2021-02-28 02:40 Justin Pryzby <pryzbyj@telsasoft.com>
0 siblings, 0 replies; 6+ messages in thread
From: Justin Pryzby @ 2021-02-28 02:40 UTC (permalink / raw)
This does a tuple-level rewrite to the new AM
---
src/backend/catalog/heap.c | 1 +
src/backend/commands/cluster.c | 17 +++--
src/backend/commands/matview.c | 1 +
src/backend/commands/tablecmds.c | 86 ++++++++++++++++++++++---
src/backend/parser/gram.y | 10 +++
src/include/commands/cluster.h | 2 +-
src/include/commands/event_trigger.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/create_am.out | 10 +++
src/test/regress/sql/create_am.sql | 5 ++
10 files changed, 119 insertions(+), 15 deletions(-)
inline patch 1: src/backend/catalog/heap.c, src/backend/commands/cluster.c, src/backend/commands/matview.c and 7 more (+119/-15 lines) download inline-1.patch
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 9abc4a1f55..9d5a9240e9 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1117,6 +1117,7 @@ AddNewRelationType(const char *typeName,
* reltypeid: OID to assign to rel's rowtype, or InvalidOid to select one
* reloftypeid: if a typed table, OID of underlying type; else InvalidOid
* ownerid: OID of new rel's owner
+ * accessmtd: OID of new rel's access method
* tupdesc: tuple descriptor (source of column definitions)
* cooked_constraints: list of precooked check constraints and defaults
* relkind: relkind for new rel
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 096a06f7b3..426a5b83ba 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -577,6 +577,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
+ Oid accessMethod = OldHeap->rd_rel->relam;
Oid OIDNewHeap;
char relpersistence;
bool is_system_catalog;
@@ -598,6 +599,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
/* Create the transient table that will receive the re-ordered data */
OIDNewHeap = make_new_heap(tableOid, tableSpace,
relpersistence,
+ accessMethod,
AccessExclusiveLock);
/* Copy the heap data into the new table in the desired order */
@@ -619,15 +621,15 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* Create the transient table that will be filled with new data during
* CLUSTER, ALTER TABLE, and similar operations. The transient table
* duplicates the logical structure of the OldHeap, but is placed in
- * NewTableSpace which might be different from OldHeap's. Also, it's built
- * with the specified persistence, which might differ from the original's.
+ * NewTableSpace/accessMethod/persistence, which might differ from those
+ * of the OldHeap.
*
* After this, the caller should load the new heap with transferred/modified
* data, then call finish_heap_swap to complete the operation.
*/
Oid
make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode)
+ Oid relam, LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
char NewHeapName[NAMEDATALEN];
@@ -686,7 +688,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
InvalidOid,
InvalidOid,
OldHeap->rd_rel->relowner,
- OldHeap->rd_rel->relam,
+ relam,
OldHeapDesc,
NIL,
RELKIND_RELATION,
@@ -1036,6 +1038,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaptemp = relform1->relam;
+ relform1->relam = relform2->relam;
+ relform2->relam = swaptemp;
+
swptmpchr = relform1->relpersistence;
relform1->relpersistence = relform2->relpersistence;
relform2->relpersistence = swptmpchr;
@@ -1071,6 +1077,9 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
if (relform1->relpersistence != relform2->relpersistence)
elog(ERROR, "cannot change persistence of mapped relation \"%s\"",
NameStr(relform1->relname));
+ if (relform1->relam != relform2->relam)
+ elog(ERROR, "cannot change access method of mapped relation \"%s\"",
+ NameStr(relform1->relname));
if (!swap_toast_by_content &&
(relform1->reltoastrelid || relform2->reltoastrelid))
elog(ERROR, "cannot swap toast by links for mapped relation \"%s\"",
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index c5c25ce11d..c206eaab7c 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -299,6 +299,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
* will be gone).
*/
OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
+ matviewRel->rd_rel->relam,
ExclusiveLock);
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
dest = CreateTransientRelDestReceiver(OIDNewHeap);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b2457a6924..6384e7f715 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18,6 +18,7 @@
#include "access/genam.h"
#include "access/heapam.h"
#include "access/heapam_xlog.h"
+#include "access/heaptoast.h"
#include "access/multixact.h"
#include "access/reloptions.h"
#include "access/relscan.h"
@@ -165,6 +166,7 @@ typedef struct AlteredTableInfo
bool verify_new_notnull; /* T if we should recheck NOT NULL */
int rewrite; /* Reason for forced rewrite, if any */
Oid newTableSpace; /* new tablespace; 0 means no change */
+ Oid newAccessMethod; /* new table access method; 0 means no change */
bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
char newrelpersistence; /* if above is true */
Expr *partition_constraint; /* for attach partition validation */
@@ -508,6 +510,7 @@ static bool ATPrepChangePersistence(Relation rel, bool toLogged);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
const char *tablespacename, LOCKMODE lockmode);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
+static void ATPrepSeTabletAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname, LOCKMODE lockmode);
static void ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace);
static void ATExecSetRelOptions(Relation rel, List *defList,
AlterTableType operation,
@@ -3876,6 +3879,7 @@ AlterTableGetLockLevel(List *cmds)
case AT_AddColumn: /* may rewrite heap, in some cases and visible
* to SELECT */
case AT_SetTableSpace: /* must rewrite heap */
+ case AT_SetTableAccessMethod: /* must rewrite heap */
case AT_AlterColumnType: /* must rewrite heap */
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4392,6 +4396,15 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATPrepSetTableSpace(tab, rel, cmd->name, lockmode);
pass = AT_PASS_MISC; /* doesn't actually matter */
break;
+
+ case AT_SetTableAccessMethod: /* SET ACCESS METHOD */
+ ATSimplePermissions(rel, ATT_TABLE);
+ /* This command never recurses */
+ tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
+ ATPrepSeTabletAccessMethod(tab, rel, cmd->name, lockmode);
+ pass = AT_PASS_MISC; /* doesn't actually matter */
+ break;
+
case AT_SetRelOptions: /* SET (...) */
case AT_ResetRelOptions: /* RESET (...) */
case AT_ReplaceRelOptions: /* reset them all, then set just these */
@@ -4752,6 +4765,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
ATExecSetTableSpaceNoStorage(rel, tab->newTableSpace);
break;
+ case AT_SetTableAccessMethod: /* SET TABLE ACCESS METHOD */
+ /* Handled specially in Phase 3 */
+ break;
+
case AT_SetRelOptions: /* SET (...) */
case AT_ResetRelOptions: /* RESET (...) */
case AT_ReplaceRelOptions: /* replace entire option list */
@@ -5067,7 +5084,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
/*
* We only need to rewrite the table if at least one column needs to
- * be recomputed, or we are changing its persistence.
+ * be recomputed, or we are changing its persistence or access method.
*
* There are two reasons for requiring a rewrite when changing
* persistence: on one hand, we need to ensure that the buffers
@@ -5082,6 +5099,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
Relation OldHeap;
Oid OIDNewHeap;
Oid NewTableSpace;
+ Oid NewAccessMethod;
char persistence;
OldHeap = table_open(tab->relid, NoLock);
@@ -5116,11 +5134,16 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* Select destination tablespace (same as original unless user
* requested a change)
*/
- if (tab->newTableSpace)
+ if (OidIsValid(tab->newTableSpace))
NewTableSpace = tab->newTableSpace;
else
NewTableSpace = OldHeap->rd_rel->reltablespace;
+ if (OidIsValid(tab->newAccessMethod))
+ NewAccessMethod = tab->newAccessMethod;
+ else
+ NewAccessMethod = OldHeap->rd_rel->relam;
+
/*
* Select persistence of transient table (same as original unless
* user requested a change)
@@ -5161,7 +5184,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* unlogged anyway.
*/
OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, persistence,
- lockmode);
+ NewAccessMethod, lockmode);
/*
* Copy the heap data into the new table with the desired
@@ -5483,13 +5506,35 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
{
/* Extract data from old tuple */
slot_getallattrs(oldslot);
- ExecClearTuple(newslot);
- /* copy attributes */
- memcpy(newslot->tts_values, oldslot->tts_values,
- sizeof(Datum) * oldslot->tts_nvalid);
- memcpy(newslot->tts_isnull, oldslot->tts_isnull,
- sizeof(bool) * oldslot->tts_nvalid);
+ /* Need to detoast tuples when changing AM XXX: should check if one AM is heap and one isn't? */
+ if (newrel->rd_rel->relam != oldrel->rd_rel->relam)
+ {
+ HeapTuple htup = toast_build_flattened_tuple(oldTupDesc,
+ oldslot->tts_values,
+ oldslot->tts_isnull);
+
+ /*
+ * Copy the value/null array to the new slot and materialize it,
+ * before clearing the tuple from the old slot.
+ */
+ ExecClearTuple(newslot);
+ ExecForceStoreHeapTuple(htup, oldslot, true);
+ slot_getallattrs(oldslot); /* again */
+
+ memcpy(newslot->tts_values, oldslot->tts_values, oldslot->tts_nvalid * sizeof(Datum));
+ memcpy(newslot->tts_isnull, oldslot->tts_isnull, oldslot->tts_nvalid * sizeof(bool));
+ ExecStoreVirtualTuple(newslot);
+ ExecMaterializeSlot(newslot);
+ ExecClearTuple(oldslot);
+ } else {
+ ExecClearTuple(newslot);
+ /* copy attributes */
+ memcpy(newslot->tts_values, oldslot->tts_values,
+ sizeof(Datum) * oldslot->tts_nvalid);
+ memcpy(newslot->tts_isnull, oldslot->tts_isnull,
+ sizeof(bool) * oldslot->tts_nvalid);
+ }
/* Set dropped attributes to null in new tuple */
foreach(lc, dropped_attrs)
@@ -5516,7 +5561,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
&newslot->tts_isnull[ex->attnum - 1]);
}
- ExecStoreVirtualTuple(newslot);
+ if (newrel->rd_rel->relam == oldrel->rd_rel->relam)
+ ExecStoreVirtualTuple(newslot);
/*
* Now, evaluate any expressions whose inputs come from the
@@ -13057,6 +13103,26 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacen
tab->newTableSpace = tablespaceId;
}
+/*
+ * ALTER TABLE SET ACCESS METHOD
+ */
+static void
+ATPrepSeTabletAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname, LOCKMODE lockmode)
+{
+ Oid amoid;
+
+ /* Check that the AM exists */
+ amoid = get_table_am_oid(amname, false);
+
+ /* Save info for Phase 3 to do the real work */
+ if (OidIsValid(tab->newAccessMethod))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot have multiple SET ACCESS METHOD subcommands")));
+
+ tab->newAccessMethod = amoid;
+}
+
/*
* Set, reset, or replace reloptions.
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 652be0b96d..258bad6d39 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2582,6 +2582,16 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *)n;
}
+
+ /* ALTER TABLE <name> SET TABLE ACCESS METHOD <amname> */
+ | SET TABLE ACCESS METHOD name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetTableAccessMethod;
+ n->name = $5;
+ $$ = (Node *)n;
+ }
+
/* ALTER TABLE <name> SET (...) */
| SET reloptions
{
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index a941f2accd..8c04466887 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -36,7 +36,7 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode);
+ Oid relam, LOCKMODE lockmode);
extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_system_catalog,
bool swap_toast_by_content,
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index c11bf2d781..e765e67fd1 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -32,6 +32,7 @@ typedef struct EventTriggerData
#define AT_REWRITE_ALTER_PERSISTENCE 0x01
#define AT_REWRITE_DEFAULT_VAL 0x02
#define AT_REWRITE_COLUMN_REWRITE 0x04
+#define AT_REWRITE_ACCESS_METHOD 0x08
/*
* EventTriggerData is the node type that is passed as fmgr "context" info
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 236832a2ca..81223af20c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1869,6 +1869,7 @@ typedef enum AlterTableType
AT_SetUnLogged, /* SET UNLOGGED */
AT_DropOids, /* SET WITHOUT OIDS */
AT_SetTableSpace, /* SET TABLESPACE */
+ AT_SetTableAccessMethod, /* SET TABLE ACCESS METHOD */
AT_SetRelOptions, /* SET (...) -- AM specific parameters */
AT_ResetRelOptions, /* RESET (...) -- AM specific parameters */
AT_ReplaceRelOptions, /* replace reloption list in its entirety */
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index 0dfb26c301..74b53e6d63 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -230,6 +230,16 @@ ORDER BY classid, objid, objsubid;
table tableam_parted_d_heap2
(5 rows)
+-- ALTER TABLE SET ACCESS METHOD
+CREATE TABLE heaptable USING heap AS SELECT a, repeat(a::text,9999) FROM generate_series(1,9) AS a;
+ALTER TABLE heaptable SET TABLE ACCESS METHOD heap2;
+explain (analyze, costs off, summary off, timing off) SELECT * FROM heaptable;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on heaptable (actual rows=9 loops=1)
+(1 row)
+
+DROP TABLE heaptable;
-- Second, create objects in the new AM by changing the default AM
BEGIN;
SET LOCAL default_table_access_method = 'heap2';
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 9a359466ce..630df182c3 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -161,6 +161,11 @@ WHERE pg_depend.refclassid = 'pg_am'::regclass
AND pg_am.amname = 'heap2'
ORDER BY classid, objid, objsubid;
+-- ALTER TABLE SET ACCESS METHOD
+CREATE TABLE heaptable USING heap AS SELECT a, repeat(a::text,9999) FROM generate_series(1,9) AS a;
+ALTER TABLE heaptable SET TABLE ACCESS METHOD heap2;
+explain (analyze, costs off, summary off, timing off) SELECT * FROM heaptable;
+DROP TABLE heaptable;
-- Second, create objects in the new AM by changing the default AM
BEGIN;
--
2.17.0
* [PATCH 1/2] ALTER TABLE ... SET ACCESS METHOD
@ 2021-05-05 21:28 Jeff Davis <jeff@j-davis.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Davis @ 2021-05-05 21:28 UTC (permalink / raw)
Adds support for changing the access method of a table with a
rewrite.
Author: Justin Pryzby, Jeff Davis
---
doc/src/sgml/ref/alter_table.sgml | 20 ++++++++
src/backend/commands/cluster.c | 21 +++++---
src/backend/commands/matview.c | 5 +-
src/backend/commands/tablecmds.c | 68 +++++++++++++++++++++++--
src/backend/parser/gram.y | 8 +++
src/bin/psql/tab-complete.c | 10 ++--
src/include/commands/cluster.h | 4 +-
src/include/commands/event_trigger.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/create_am.out | 34 +++++++++++++
src/test/regress/sql/create_am.sql | 21 ++++++++
11 files changed, 175 insertions(+), 18 deletions(-)
inline patch 1: doc/src/sgml/ref/alter_table.sgml, src/backend/commands/cluster.c, src/backend/commands/matview.c and 8 more (+175/-18 lines) download inline-1.patch
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c5e5e84e06..1d0f30d366 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -75,6 +75,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
SET WITHOUT CLUSTER
SET WITHOUT OIDS
+ SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
SET { LOGGED | UNLOGGED }
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
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET ACCESS METHOD</literal></term>
+ <listitem>
+ <para>
+ This form changes the access method of the table by rewriting it. See
+ <xref linkend="tableam"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET TABLESPACE</literal></term>
<listitem>
@@ -1228,6 +1239,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_access_method</replaceable></term>
+ <listitem>
+ <para>
+ The name of the access method to which the table will be converted.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 2912b4c257..45bf1276a5 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -641,6 +641,7 @@ static void
rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
+ Oid accessMethod = OldHeap->rd_rel->relam;
Oid tableSpace = OldHeap->rd_rel->reltablespace;
Oid OIDNewHeap;
char relpersistence;
@@ -661,6 +662,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
/* Create the transient table that will receive the re-ordered data */
OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ accessMethod,
relpersistence,
AccessExclusiveLock);
@@ -682,16 +684,16 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
/*
* Create the transient table that will be filled with new data during
* CLUSTER, ALTER TABLE, and similar operations. The transient table
- * duplicates the logical structure of the OldHeap, but is placed in
- * NewTableSpace which might be different from OldHeap's. Also, it's built
- * with the specified persistence, which might differ from the original's.
+ * duplicates the logical structure of the OldHeap; but will have the
+ * specified physical storage properties NewTableSpace, NewAccessMethod, and
+ * relpersistence.
*
* After this, the caller should load the new heap with transferred/modified
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode)
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewAccessMethod,
+ char relpersistence, LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
char NewHeapName[NAMEDATALEN];
@@ -750,7 +752,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
InvalidOid,
InvalidOid,
OldHeap->rd_rel->relowner,
- OldHeap->rd_rel->relam,
+ NewAccessMethod,
OldHeapDesc,
NIL,
RELKIND_RELATION,
@@ -1100,6 +1102,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaptemp = relform1->relam;
+ relform1->relam = relform2->relam;
+ relform2->relam = swaptemp;
+
swptmpchr = relform1->relpersistence;
relform1->relpersistence = relform2->relpersistence;
relform2->relpersistence = swptmpchr;
@@ -1135,6 +1141,9 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
if (relform1->relpersistence != relform2->relpersistence)
elog(ERROR, "cannot change persistence of mapped relation \"%s\"",
NameStr(relform1->relname));
+ if (relform1->relam != relform2->relam)
+ elog(ERROR, "cannot change access method of mapped relation \"%s\"",
+ NameStr(relform1->relname));
if (!swap_toast_by_content &&
(relform1->reltoastrelid || relform2->reltoastrelid))
elog(ERROR, "cannot swap toast by links for mapped relation \"%s\"",
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 25bbd8a5c1..9493b227b4 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -298,8 +298,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
* it against access by any other process until commit (by which time it
* will be gone).
*/
- OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
- ExclusiveLock);
+ OIDNewHeap = make_new_heap(matviewOid, tableSpace,
+ matviewRel->rd_rel->relam,
+ relpersistence, ExclusiveLock);
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
dest = CreateTransientRelDestReceiver(OIDNewHeap);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3ba2d57ec7..6e394550a9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -176,6 +176,7 @@ typedef struct AlteredTableInfo
List *afterStmts; /* List of utility command parsetrees */
bool verify_new_notnull; /* T if we should recheck NOT NULL */
int rewrite; /* Reason for forced rewrite, if any */
+ Oid newAccessMethod; /* new access method; 0 means no change */
Oid newTableSpace; /* new tablespace; 0 means no change */
bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
char newrelpersistence; /* if above is true */
@@ -538,6 +539,7 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
+static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
static bool ATPrepChangePersistence(Relation rel, bool toLogged);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
const char *tablespacename, LOCKMODE lockmode);
@@ -4096,6 +4098,7 @@ AlterTableGetLockLevel(List *cmds)
*/
case AT_AddColumn: /* may rewrite heap, in some cases and visible
* to SELECT */
+ case AT_SetAccessMethod: /* must rewrite heap */
case AT_SetTableSpace: /* must rewrite heap */
case AT_AlterColumnType: /* must rewrite heap */
cmd_lockmode = AccessExclusiveLock;
@@ -4622,6 +4625,24 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
pass = AT_PASS_DROP;
break;
+ case AT_SetAccessMethod: /* SET ACCESS METHOD */
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
+
+ /* partitioned tables don't have an access method */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot change access method of a partitioned table")));
+
+ /* check if another access method change was already requested */
+ if (OidIsValid(tab->newAccessMethod))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot have multiple SET ACCESS METHOD subcommands")));
+
+ ATPrepSetAccessMethod(tab, rel, cmd->name);
+ pass = AT_PASS_MISC; /* does not matter; no work in Phase 2 */
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX |
ATT_PARTITIONED_INDEX);
@@ -4997,6 +5018,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_DropOids: /* SET WITHOUT OIDS */
/* nothing to do here, oid columns don't exist anymore */
break;
+ case AT_SetAccessMethod: /* SET ACCESS METHOD */
+ /* handled specially in Phase 3 */
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/*
@@ -5324,7 +5348,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
/*
* We only need to rewrite the table if at least one column needs to
- * be recomputed, or we are changing its persistence.
+ * be recomputed, or we are changing its persistence or access method.
*
* There are two reasons for requiring a rewrite when changing
* persistence: on one hand, we need to ensure that the buffers
@@ -5338,6 +5362,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
/* Build a temporary relation and copy data */
Relation OldHeap;
Oid OIDNewHeap;
+ Oid NewAccessMethod;
Oid NewTableSpace;
char persistence;
@@ -5373,11 +5398,20 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* Select destination tablespace (same as original unless user
* requested a change)
*/
- if (tab->newTableSpace)
+ if (OidIsValid(tab->newTableSpace))
NewTableSpace = tab->newTableSpace;
else
NewTableSpace = OldHeap->rd_rel->reltablespace;
+ /*
+ * Select destination access method (same as original unless user
+ * requested a change)
+ */
+ if (OidIsValid(tab->newAccessMethod))
+ NewAccessMethod = tab->newAccessMethod;
+ else
+ NewAccessMethod = OldHeap->rd_rel->relam;
+
/*
* Select persistence of transient table (same as original unless
* user requested a change)
@@ -5417,8 +5451,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* persistence. That wouldn't work for pg_class, but that can't be
* unlogged anyway.
*/
- OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, persistence,
- lockmode);
+ OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, NewAccessMethod,
+ persistence, lockmode);
/*
* Copy the heap data into the new table with the desired
@@ -5933,6 +5967,8 @@ ATGetQueueEntry(List **wqueue, Relation rel)
tab->rel = NULL; /* set later */
tab->relkind = rel->rd_rel->relkind;
tab->oldDesc = CreateTupleDescCopyConstr(RelationGetDescr(rel));
+ tab->newTableSpace = InvalidOid;
+ tab->newAccessMethod = InvalidOid;
tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
tab->chgPersistence = false;
@@ -6003,6 +6039,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "CLUSTER ON";
case AT_DropCluster:
return "SET WITHOUT CLUSTER";
+ case AT_SetAccessMethod:
+ return "SET ACCESS METHOD";
case AT_SetLogged:
return "SET LOGGED";
case AT_SetUnLogged:
@@ -13613,6 +13651,28 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
mark_index_clustered(rel, InvalidOid, false);
}
+/*
+ * Preparation phase for SET ACCESS METHOD
+ *
+ * Check that access method exists. If it's the same as the table's current
+ * access method, it's a no-op. Otherwise, a table rewrite is necessary.
+ */
+static void
+ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
+{
+ Oid amoid;
+
+ /* Check that the table access method exists */
+ amoid = get_table_am_oid(amname, false);
+
+ if (rel->rd_rel->relam == amoid)
+ return;
+
+ /* Save info for Phase 3 to do the real work */
+ tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
+ tab->newAccessMethod = amoid;
+}
+
/*
* ALTER TABLE SET TABLESPACE
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 10da5c5c51..39a2849eba 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2623,6 +2623,14 @@ alter_table_cmd:
n->newowner = $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> SET ACCESS METHOD <amname> */
+ | SET ACCESS METHOD name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetAccessMethod;
+ n->name = $4;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> SET TABLESPACE <tablespacename> */
| SET TABLESPACE name
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8a44575b26..46e22411f8 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2142,13 +2142,15 @@ psql_completion(const char *text, int start, int end)
}
/* If we have ALTER TABLE <sth> SET, provide list of attributes and '(' */
else if (Matches("ALTER", "TABLE", MatchAny, "SET"))
- COMPLETE_WITH("(", "LOGGED", "SCHEMA", "TABLESPACE", "UNLOGGED",
- "WITH", "WITHOUT");
+ COMPLETE_WITH("(", "ACCESS METHOD", "LOGGED", "SCHEMA",
+ "TABLESPACE", "UNLOGGED", "WITH", "WITHOUT");
/*
- * If we have ALTER TABLE <sth> SET TABLESPACE provide a list of
- * tablespaces
+ * Complete with list of tablespaces (for SET TABLESPACE) or table AMs (for
+ * SET ACCESS METHOD).
*/
+ else if (Matches("ALTER", "TABLE", MatchAny, "SET", "ACCESS", "METHOD"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
else if (Matches("ALTER", "TABLE", MatchAny, "SET", "TABLESPACE"))
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
/* If we have ALTER TABLE <sth> SET WITHOUT provide CLUSTER or OIDS */
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index c30ca01726..d400b6e937 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -36,8 +36,8 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
-extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode);
+extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewAccessMethod,
+ char relpersistence, LOCKMODE lockmode);
extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_system_catalog,
bool swap_toast_by_content,
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index c11bf2d781..e765e67fd1 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -32,6 +32,7 @@ typedef struct EventTriggerData
#define AT_REWRITE_ALTER_PERSISTENCE 0x01
#define AT_REWRITE_DEFAULT_VAL 0x02
#define AT_REWRITE_COLUMN_REWRITE 0x04
+#define AT_REWRITE_ACCESS_METHOD 0x08
/*
* EventTriggerData is the node type that is passed as fmgr "context" info
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index def9651b34..55d388485d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1901,6 +1901,7 @@ typedef enum AlterTableType
AT_SetLogged, /* SET LOGGED */
AT_SetUnLogged, /* SET UNLOGGED */
AT_DropOids, /* SET WITHOUT OIDS */
+ AT_SetAccessMethod, /* SET ACCESS METHOD */
AT_SetTableSpace, /* SET TABLESPACE */
AT_SetRelOptions, /* SET (...) -- AM specific parameters */
AT_ResetRelOptions, /* RESET (...) -- AM specific parameters */
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index 0dfb26c301..22d6a06910 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -230,6 +230,40 @@ ORDER BY classid, objid, objsubid;
table tableam_parted_d_heap2
(5 rows)
+-- ALTER TABLE SET ACCESS METHOD
+CREATE TABLE heaptable USING heap AS
+ SELECT a, repeat(a::text,9999) FROM generate_series(1,9) AS a;
+SELECT amname FROM pg_class c, pg_am am
+ WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ amname
+--------
+ heap
+(1 row)
+
+ALTER TABLE heaptable SET ACCESS METHOD heap2;
+SELECT amname FROM pg_class c, pg_am am
+ WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ amname
+--------
+ heap2
+(1 row)
+
+SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
+ count | count
+-------+-------
+ 9 | 1
+(1 row)
+
+-- negative test
+ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
+ERROR: cannot have multiple SET ACCESS METHOD subcommands
+DROP TABLE heaptable;
+CREATE TABLE am_partitioned(x INT, y INT)
+ PARTITION BY hash (x);
+-- negative test
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+ERROR: cannot change access method of a partitioned table
+DROP TABLE am_partitioned;
-- Second, create objects in the new AM by changing the default AM
BEGIN;
SET LOCAL default_table_access_method = 'heap2';
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 9a359466ce..d0d39d71ec 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -161,6 +161,27 @@ WHERE pg_depend.refclassid = 'pg_am'::regclass
AND pg_am.amname = 'heap2'
ORDER BY classid, objid, objsubid;
+-- ALTER TABLE SET ACCESS METHOD
+CREATE TABLE heaptable USING heap AS
+ SELECT a, repeat(a::text,9999) FROM generate_series(1,9) AS a;
+SELECT amname FROM pg_class c, pg_am am
+ WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ALTER TABLE heaptable SET ACCESS METHOD heap2;
+SELECT amname FROM pg_class c, pg_am am
+ WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
+
+-- negative test
+ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
+
+DROP TABLE heaptable;
+
+CREATE TABLE am_partitioned(x INT, y INT)
+ PARTITION BY hash (x);
+
+-- negative test
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+DROP TABLE am_partitioned;
-- Second, create objects in the new AM by changing the default AM
BEGIN;
--
2.17.0
* [v15] ALTER TABLE ... SET ACCESS METHOD
@ 2021-05-06 03:45 Jeff Davis <pgsql@j-davis.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jeff Davis @ 2021-05-06 03:45 UTC (permalink / raw)
To: pgsql-hackers
The attached patch implements ALTER TABLE ... SET ACCESS METHOD.
For simplicity, I used the normal alter table path, ATRewriteTable(),
which does not follow the stricter isolation semantics that VACUUM FULL
follows. If someone thinks that's unacceptable, please let me know.
Regards,
Jeff Davis
Attachments:
alter-access-method.diff (text/x-patch, 10.8K) download
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 6166b263345..255317883bc 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -76,6 +76,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
SET WITHOUT CLUSTER
SET WITHOUT OIDS
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
SET { LOGGED | UNLOGGED }
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
@@ -706,6 +707,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET ACCESS METHOD</literal></term>
+ <listitem>
+ <para>
+ This form changes the access method of the table. See <xref
+ linkend="tableam"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET { LOGGED | UNLOGGED }</literal></term>
<listitem>
@@ -1219,6 +1230,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_access_method</replaceable></term>
+ <listitem>
+ <para>
+ The name of the access method to which the table will be converted.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">new_schema</replaceable></term>
<listitem>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 6487a9e3fcb..e0c84d649ee 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -597,6 +597,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
/* Create the transient table that will receive the re-ordered data */
OIDNewHeap = make_new_heap(tableOid, tableSpace,
+ OldHeap->rd_rel->relam,
relpersistence,
AccessExclusiveLock);
@@ -626,8 +627,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode)
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewAccessMethod,
+ char relpersistence, LOCKMODE lockmode)
{
TupleDesc OldHeapDesc;
char NewHeapName[NAMEDATALEN];
@@ -686,7 +687,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
InvalidOid,
InvalidOid,
OldHeap->rd_rel->relowner,
- OldHeap->rd_rel->relam,
+ NewAccessMethod,
OldHeapDesc,
NIL,
RELKIND_RELATION,
@@ -1036,6 +1037,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
relform1->reltablespace = relform2->reltablespace;
relform2->reltablespace = swaptemp;
+ swaptemp = relform1->relam;
+ relform1->relam = relform2->relam;
+ relform2->relam = swaptemp;
+
swptmpchr = relform1->relpersistence;
relform1->relpersistence = relform2->relpersistence;
relform2->relpersistence = swptmpchr;
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 172ec6e9828..1ebd02fd684 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -298,8 +298,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
* it against access by any other process until commit (by which time it
* will be gone).
*/
- OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
- ExclusiveLock);
+ OIDNewHeap = make_new_heap(matviewOid, tableSpace,
+ matviewRel->rd_rel->relam,
+ relpersistence, ExclusiveLock);
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
dest = CreateTransientRelDestReceiver(OIDNewHeap);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0da61784d7a..be91a00a170 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -177,6 +177,7 @@ typedef struct AlteredTableInfo
List *afterStmts; /* List of utility command parsetrees */
bool verify_new_notnull; /* T if we should recheck NOT NULL */
int rewrite; /* Reason for forced rewrite, if any */
+ Oid newAccessMethod; /* new access method; 0 means no change */
Oid newTableSpace; /* new tablespace; 0 means no change */
bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
char newrelpersistence; /* if above is true */
@@ -540,6 +541,7 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
+static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
static bool ATPrepChangePersistence(Relation rel, bool toLogged);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
const char *tablespacename, LOCKMODE lockmode);
@@ -4287,6 +4289,7 @@ AlterTableGetLockLevel(List *cmds)
cmd_lockmode = ShareUpdateExclusiveLock;
break;
+ case AT_SetAccessMethod:
case AT_SetLogged:
case AT_SetUnLogged:
cmd_lockmode = AccessExclusiveLock;
@@ -4609,6 +4612,15 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
+ case AT_SetAccessMethod: /* SET ACCESS METHOD */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
+ if (tab->newAccessMethod)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change access method setting twice")));
+ ATPrepSetAccessMethod(tab, rel, cmd->name);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetLogged: /* SET LOGGED */
ATSimplePermissions(rel, ATT_TABLE);
if (tab->chgPersistence)
@@ -5012,6 +5024,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_DropCluster: /* SET WITHOUT CLUSTER */
ATExecDropCluster(rel, lockmode);
break;
+ case AT_SetAccessMethod: /* SET ACCESS METHOD */
+ break;
case AT_SetLogged: /* SET LOGGED */
case AT_SetUnLogged: /* SET UNLOGGED */
break;
@@ -5365,6 +5379,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
Relation OldHeap;
Oid OIDNewHeap;
Oid NewTableSpace;
+ Oid accessmethod;
char persistence;
OldHeap = table_open(tab->relid, NoLock);
@@ -5411,6 +5426,13 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
persistence = tab->chgPersistence ?
tab->newrelpersistence : OldHeap->rd_rel->relpersistence;
+ /*
+ * Select access method of transient table (same as original
+ * unless user requested a change)
+ */
+ accessmethod = OidIsValid(tab->newAccessMethod) ?
+ tab->newAccessMethod : OldHeap->rd_rel->relam;
+
table_close(OldHeap, NoLock);
/*
@@ -5443,8 +5465,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* persistence. That wouldn't work for pg_class, but that can't be
* unlogged anyway.
*/
- OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, persistence,
- lockmode);
+ OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, accessmethod,
+ persistence, lockmode);
/*
* Copy the heap data into the new table with the desired
@@ -15695,6 +15717,34 @@ ATExecSetCompression(AlteredTableInfo *tab,
}
+/*
+ * Preparation phase for SET TABLESPACE
+ *
+ * Check that access method exists. If it's the same as the table's current
+ * access method, it's a no-op. Otherwise, a table rewrite is necessary.
+ */
+static void
+ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
+{
+ Oid amoid;
+
+ /* Check that the table access method exists */
+ amoid = get_table_am_oid(amname, false);
+
+ if (rel->rd_rel->relam == amoid)
+ return;
+
+ /* Save info for Phase 3 to do the real work */
+ if (OidIsValid(tab->newAccessMethod))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot have multiple SET ACCESS METHOD subcommands")));
+
+ tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
+ tab->newAccessMethod = amoid;
+}
+
+
/*
* Preparation phase for SET LOGGED/UNLOGGED
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b4ab4014c87..9e41e84b64c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2475,6 +2475,14 @@ alter_table_cmd:
n->name = NULL;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> SET ACCESS METHOD <tablespacename> */
+ | SET ACCESS METHOD name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetAccessMethod;
+ n->name = $4;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> SET LOGGED */
| SET LOGGED
{
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index a941f2accda..b64d3bc2040 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -35,8 +35,8 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
-extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
- LOCKMODE lockmode);
+extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewAccessMethod,
+ char relpersistence, LOCKMODE lockmode);
extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_system_catalog,
bool swap_toast_by_content,
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index c11bf2d7810..e765e67fd10 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -32,6 +32,7 @@ typedef struct EventTriggerData
#define AT_REWRITE_ALTER_PERSISTENCE 0x01
#define AT_REWRITE_DEFAULT_VAL 0x02
#define AT_REWRITE_COLUMN_REWRITE 0x04
+#define AT_REWRITE_ACCESS_METHOD 0x08
/*
* EventTriggerData is the node type that is passed as fmgr "context" info
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 615dfa26aa2..cce5c0834ee 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1898,6 +1898,7 @@ typedef enum AlterTableType
AT_ChangeOwner, /* change owner */
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
+ AT_SetAccessMethod, /* SET ACCESS METHOD */
AT_SetLogged, /* SET LOGGED */
AT_SetUnLogged, /* SET UNLOGGED */
AT_DropOids, /* SET WITHOUT OIDS */
* Re: [v15] ALTER TABLE ... SET ACCESS METHOD
@ 2021-05-06 03:59 Andres Freund <andres@anarazel.de>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andres Freund @ 2021-05-06 03:59 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-hackers
Hi,
On 2021-05-05 20:45:50 -0700, Jeff Davis wrote:
> The attached patch implements ALTER TABLE ... SET ACCESS METHOD.
I think it'd be good to have a basic test for this. See create_am.sql
for how to do so without actually needing a second AM implementation.
> For simplicity, I used the normal alter table path, ATRewriteTable(),
> which does not follow the stricter isolation semantics that VACUUM FULL
> follows. If someone thinks that's unacceptable, please let me know.
I think it'd be good to have something better for tablecmd.c table
rewrites - but I don't think it's this patch's responsibility.
Regards,
Andres
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [v15] ALTER TABLE ... SET ACCESS METHOD
@ 2021-05-06 04:40 Justin Pryzby <pryzby@telsasoft.com>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 1 reply; 6+ messages in thread
From: Justin Pryzby @ 2021-05-06 04:40 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-hackers
On Wed, May 05, 2021 at 08:45:50PM -0700, Jeff Davis wrote:
> The attached patch implements ALTER TABLE ... SET ACCESS METHOD.
>
> For simplicity, I used the normal alter table path, ATRewriteTable(),
> which does not follow the stricter isolation semantics that VACUUM FULL
> follows. If someone thinks that's unacceptable, please let me know.
Why doees your patch say v15?
It's nearly the same as my pre-existing patch, so should merge them.
https://commitfest.postgresql.org/33/3020/
--
Justin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [v15] ALTER TABLE ... SET ACCESS METHOD
@ 2021-05-06 04:56 Jeff Davis <pgsql@j-davis.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Davis @ 2021-05-06 04:56 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-hackers
On Wed, 2021-05-05 at 23:40 -0500, Justin Pryzby wrote:
> Why doees your patch say v15?
> It's nearly the same as my pre-existing patch, so should merge them.
Sorry, I completely missed your patch. I retract mine and we'll
continue with yours.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-05-06 04:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-28 02:40 [PATCH] ALTER TABLE SET ACCESS METHOD Justin Pryzby <pryzbyj@telsasoft.com>
2021-05-05 21:28 [PATCH 1/2] ALTER TABLE ... SET ACCESS METHOD Jeff Davis <jeff@j-davis.com>
2021-05-06 03:45 [v15] ALTER TABLE ... SET ACCESS METHOD Jeff Davis <pgsql@j-davis.com>
2021-05-06 03:59 ` Re: [v15] ALTER TABLE ... SET ACCESS METHOD Andres Freund <andres@anarazel.de>
2021-05-06 04:40 ` Re: [v15] ALTER TABLE ... SET ACCESS METHOD Justin Pryzby <pryzby@telsasoft.com>
2021-05-06 04:56 ` Re: [v15] ALTER TABLE ... SET ACCESS METHOD Jeff Davis <pgsql@j-davis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox