From ee9610626927a8438ca1278a891321dc585e9401 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 27 Feb 2021 20:40:54 -0600 Subject: [PATCH v2 1/3] ALTER TABLE SET ACCESS METHOD This does a tuple-level rewrite to the new AM --- doc/src/sgml/ref/alter_table.sgml | 11 ++++ src/backend/commands/cluster.c | 17 ++++-- src/backend/commands/matview.c | 1 + src/backend/commands/tablecmds.c | 76 ++++++++++++++++++++++--- src/backend/parser/gram.y | 8 +++ src/bin/psql/tab-complete.c | 11 ++-- 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 | 16 ++++++ src/test/regress/sql/create_am.sql | 6 ++ 11 files changed, 131 insertions(+), 19 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index c25ef5abd6..38e416d183 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -74,6 +74,7 @@ ALTER TABLE [ IF EXISTS ] name CLUSTER ON index_name SET WITHOUT CLUSTER SET WITHOUT OIDS + SET ACCESS METHOD new_access_method SET TABLESPACE new_tablespace SET { LOGGED | UNLOGGED } SET ( storage_parameter [= value] [, ... ] ) @@ -658,6 +659,16 @@ WITH ( MODULUS numeric_literal, REM + + SET ACCESS METHOD + + + This form changes the table's access method to the specified table AM and + rewrites the table. + + + + SET TABLESPACE 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 559fa1d2e5..4f62f062fa 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" @@ -164,6 +165,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 table 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 */ @@ -507,6 +509,7 @@ static void ATExecDropCluster(Relation rel, LOCKMODE lockmode); static bool ATPrepChangePersistence(Relation rel, bool toLogged); static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacename, LOCKMODE lockmode); +static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname, LOCKMODE lockmode); static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode); static void ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace); static void ATExecSetRelOptions(Relation rel, List *defList, @@ -3874,6 +3877,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; @@ -4384,6 +4388,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); pass = AT_PASS_DROP; break; + case AT_SetAccessMethod: /* SET ACCESS METHOD */ + ATSimplePermissions(rel, ATT_TABLE); + /* This command never recurses */ + tab->rewrite |= AT_REWRITE_ACCESS_METHOD; + ATPrepSetAccessMethod(tab, rel, cmd->name, lockmode); + pass = AT_PASS_MISC; /* doesn't actually matter */ + break; case AT_SetTableSpace: /* SET TABLESPACE */ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX); @@ -4739,6 +4750,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, 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 */ /* @@ -5066,7 +5080,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 @@ -5080,6 +5094,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; @@ -5115,11 +5130,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) @@ -5160,7 +5180,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 @@ -5484,11 +5504,28 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) 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. + */ + heap_deform_tuple(htup, newslot->tts_tupleDescriptor, + newslot->tts_values, newslot->tts_isnull); + ExecStoreVirtualTuple(newslot); + ExecClearTuple(oldslot); + } else { + /* 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) @@ -5515,7 +5552,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 @@ -13026,6 +13064,26 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode) mark_index_clustered(rel, InvalidOid, false); } +/* + * ALTER TABLE SET ACCESS METHOD + */ +static void +ATPrepSetAccessMethod(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; +} + /* * ALTER TABLE SET TABLESPACE */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 652be0b96d..2ae41f8f4d 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2574,6 +2574,14 @@ alter_table_cmd: n->newowner = $3; $$ = (Node *)n; } + /* ALTER TABLE SET ACCESS METHOD */ + | SET ACCESS METHOD name + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetAccessMethod; + n->name = $4; + $$ = (Node *)n; + } /* ALTER TABLE SET TABLESPACE */ | SET TABLESPACE name { diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 9f0208ac49..4400b2e832 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -2143,13 +2143,14 @@ psql_completion(const char *text, int start, int end) } /* If we have ALTER TABLE 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 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 SET WITHOUT provide CLUSTER or OIDS */ 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..ce54f76610 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1868,6 +1868,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..7ebdd5ded6 100644 --- a/src/test/regress/expected/create_am.out +++ b/src/test/regress/expected/create_am.out @@ -230,6 +230,22 @@ 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 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) + +SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable; + count | count +-------+------- + 9 | 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..677a851cd3 100644 --- a/src/test/regress/sql/create_am.sql +++ b/src/test/regress/sql/create_am.sql @@ -161,6 +161,12 @@ 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 ACCESS METHOD heap2; +explain (analyze, costs off, summary off, timing off) SELECT * FROM heaptable; +SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable; +DROP TABLE heaptable; -- Second, create objects in the new AM by changing the default AM BEGIN; -- 2.17.0