On Mon, Mar 01, 2021 at 11:16:36AM +0900, Michael Paquier wrote:
> On Sun, Feb 28, 2021 at 04:25:30PM -0600, Justin Pryzby wrote:
> > I called this "set TABLE access method" rather than just "set access method"
> > for the reasons given on the LIKE thread:
> > https://www.postgresql.org/message-id/20210119210331.gn8...@telsasoft.com
>
> ALTER TABLE applies to a table (or perhaps a sequence, still..), so
> that sounds a bit weird to me to add again the keyword "TABLE" for
> that.
This renames to use SET ACCESS METHOD (resolving a silly typo);
And handles the tuple slots more directly;
And adds docs and tab completion;
Also, since 8586bf7ed8889f39a59dd99b292014b73be85342:
| For now it's not allowed to set a table AM for a partitioned table, as
| we've not resolved how partitions would inherit that. Disallowing
| allows us to introduce, if we decide that's the way forward, such a
| behaviour without a compatibility break.
I propose that it should behave like tablespace for partitioned rels:
ca4103025dfe, 33e6c34c3267
--
Justin
>From ee9610626927a8438ca1278a891321dc585e9401 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
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 ] <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>] [, ... ] )
@@ -658,6 +659,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 table's access method to the specified table AM and
+ rewrites the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET TABLESPACE</literal></term>
<listitem>
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 <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 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 <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 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
>From 80de05d23f964365312c1704dc69e15725e7c26d Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 7 Mar 2021 00:11:38 -0600
Subject: [PATCH v2 2/3] Allow specifying acccess method of partitioned
tables..
..to be inherited by partitions
See also:
ca4103025dfe26eaaf6a500dec9170fbb176eebc
8586bf7ed8889f39a59dd99b292014b73be85342
ebfe2dbd6b624e2a428e14b7ee9322cc096f63f7 - prevent DROP AM
---
src/backend/catalog/heap.c | 3 +-
src/backend/commands/tablecmds.c | 86 +++++++++++++++++++++----
src/backend/utils/cache/relcache.c | 4 +-
src/test/regress/expected/create_am.out | 20 +++---
src/test/regress/sql/create_am.sql | 6 +-
5 files changed, 93 insertions(+), 26 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 9abc4a1f55..693a94107d 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1462,12 +1462,13 @@ heap_create_with_catalog(const char *relname,
/*
* Make a dependency link to force the relation to be deleted if its
- * access method is. Do this only for relation and materialized views.
+ * access method is. Do this only for relevant types.
*
* No need to add an explicit dependency for the toast table, as the
* main table depends on it.
*/
if (relkind == RELKIND_RELATION ||
+ relkind == RELKIND_PARTITIONED_TABLE ||
relkind == RELKIND_MATVIEW)
{
ObjectAddressSet(referenced, AccessMethodRelationId, accessmtd);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4f62f062fa..73cd6e311b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -510,6 +510,7 @@ 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 ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
static void ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace);
static void ATExecSetRelOptions(Relation rel, List *defList,
@@ -604,7 +605,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
Oid ofTypeId;
ObjectAddress address;
LOCKMODE parentLockmode;
- const char *accessMethod = NULL;
Oid accessMethodId = InvalidOid;
/*
@@ -862,23 +862,33 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* a type of relation that needs one, use the default.
*/
if (stmt->accessMethod != NULL)
+ accessMethodId = get_table_am_oid(stmt->accessMethod, false);
+ else if (stmt->partbound && relkind == RELKIND_RELATION)
{
- accessMethod = stmt->accessMethod;
+ HeapTuple tup;
+ Oid relid;
- if (partitioned)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("specifying a table access method is not supported on a partitioned table")));
+ /*
+ * For partitioned tables, when no access method is specified, we
+ * default to the parent table's AM.
+ */
+ Assert(list_length(inheritOids) == 1);
+ /* XXX: should implement get_rel_relam? */
+ relid = linitial_oid(inheritOids);
+ tup = SearchSysCache1(RELOID, ObjectIdDatumGet(relid));
+ accessMethodId = ((Form_pg_class) GETSTRUCT(tup))->relam;
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+ ReleaseSysCache(tup);
+ if (!OidIsValid(accessMethodId))
+ accessMethodId = get_table_am_oid(default_table_access_method, false);
}
else if (relkind == RELKIND_RELATION ||
relkind == RELKIND_TOASTVALUE ||
+ relkind == RELKIND_PARTITIONED_TABLE ||
relkind == RELKIND_MATVIEW)
- accessMethod = default_table_access_method;
-
- /* look up the access method, verify it is for a table */
- if (accessMethod != NULL)
- accessMethodId = get_table_am_oid(accessMethod, false);
+ accessMethodId = get_table_am_oid(default_table_access_method, false);
/*
* Create the relation. Inherited defaults and constraints are passed in
@@ -4751,6 +4761,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* nothing to do here, oid columns don't exist anymore */
break;
case AT_SetAccessMethod: /* SET ACCESS METHOD */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod);
/* Handled specially in Phase 3 */
break;
case AT_SetTableSpace: /* SET TABLESPACE */
@@ -13407,6 +13419,58 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
list_free(reltoastidxids);
}
+/*
+ * Special handling of ALTER TABLE SET ACCESS METHOD for relations with no
+ * storage that have an interest in preserving AM.
+ *
+ * Since these have no storage the tablespace can be updated with a simple
+ * metadata only operation to update the tablespace.
+ */
+static void
+ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod)
+{
+ Relation pg_class;
+ Oid relid;
+ Oid oldrelam;
+ HeapTuple tuple;
+
+ /*
+ * Shouldn't be called on relations having storage; these are processed in
+ * phase 3.
+ */
+ Assert(!RELKIND_HAS_STORAGE(rel->rd_rel->relkind));
+
+ relid = RelationGetRelid(rel);
+
+ /* Pull the record for this relation and update it */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
+
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+
+ oldrelam = ((Form_pg_class) GETSTRUCT(tuple))->relam;
+ ((Form_pg_class) GETSTRUCT(tuple))->relam = newAccessMethod;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+ /*
+ * Record dependency on AM. This is only required for relations
+ * that have no physical storage.
+ */
+ changeDependencyFor(RelationRelationId, RelationGetRelid(rel),
+ AccessMethodRelationId, oldrelam,
+ newAccessMethod);
+
+ InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0);
+
+ heap_freetuple(tuple);
+ table_close(pg_class, RowExclusiveLock);
+
+ /* Make sure the relam change is visible */
+ CommandCounterIncrement();
+}
+
/*
* Special handling of ALTER TABLE SET TABLESPACE for relations with no
* storage that have an interest in preserving tablespace.
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..70aa2d93bd 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1212,9 +1212,11 @@ RelationBuildDesc(Oid targetRelId, bool insertIt)
case RELKIND_VIEW:
case RELKIND_COMPOSITE_TYPE:
case RELKIND_FOREIGN_TABLE:
- case RELKIND_PARTITIONED_TABLE:
Assert(relation->rd_rel->relam == InvalidOid);
break;
+ case RELKIND_PARTITIONED_TABLE:
+ /* Do nothing: it's a catalog settings for partitions to inherit */
+ break;
}
/* extract reloptions if any */
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index 7ebdd5ded6..3d520c3149 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,11 +176,9 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
1
(1 row)
--- CREATE TABLE .. PARTITION BY doesn't not support USING
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-ERROR: specifying a table access method is not supported on a partitioned table
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
+-- CREATE TABLE .. PARTITION BY supports USING
-- new partitions will inherit from the current default, rather the partition root
+CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
SET default_table_access_method = 'heap';
CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
SET default_table_access_method = 'heap2';
@@ -205,14 +203,17 @@ WHERE pa.oid = pc.relam
ORDER BY 3, 1, 2;
relkind | amname | relname
---------+--------+----------------------------------
+ r | heap2 | tableam_parted_a_heap2
r | heap2 | tableam_parted_b_heap2
r | heap2 | tableam_parted_d_heap2
+ p | heap2 | tableam_parted_heap2
r | heap2 | tableam_tbl_heap2
r | heap2 | tableam_tblas_heap2
m | heap2 | tableam_tblmv_heap2
+ t | heap2 | toast for tableam_parted_a_heap2
t | heap2 | toast for tableam_parted_b_heap2
t | heap2 | toast for tableam_parted_d_heap2
-(7 rows)
+(10 rows)
-- Show dependencies onto AM - there shouldn't be any for toast
SELECT pg_describe_object(classid,objid,objsubid) AS obj
@@ -226,9 +227,11 @@ ORDER BY classid, objid, objsubid;
table tableam_tbl_heap2
table tableam_tblas_heap2
materialized view tableam_tblmv_heap2
+ table tableam_parted_heap2
+ table tableam_parted_a_heap2
table tableam_parted_b_heap2
table tableam_parted_d_heap2
-(5 rows)
+(7 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;
@@ -282,7 +285,7 @@ ORDER BY 3, 1, 2;
f | | tableam_fdw_heapx
r | heap2 | tableam_parted_1_heapx
r | heap | tableam_parted_2_heapx
- p | | tableam_parted_heapx
+ p | heap2 | tableam_parted_heapx
S | | tableam_seq_heapx
r | heap2 | tableam_tbl_heapx
r | heap2 | tableam_tblas_heapx
@@ -311,7 +314,6 @@ ERROR: cannot drop access method heap2 because other objects depend on it
DETAIL: table tableam_tbl_heap2 depends on access method heap2
table tableam_tblas_heap2 depends on access method heap2
materialized view tableam_tblmv_heap2 depends on access method heap2
-table tableam_parted_b_heap2 depends on access method heap2
-table tableam_parted_d_heap2 depends on access method heap2
+table tableam_parted_heap2 depends on access method heap2
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- we intentionally leave the objects created above alive, to verify pg_dump support
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 677a851cd3..4eaa058164 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -124,11 +124,9 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2;
CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
--- CREATE TABLE .. PARTITION BY doesn't not support USING
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
+-- CREATE TABLE .. PARTITION BY supports USING
-- new partitions will inherit from the current default, rather the partition root
+CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
SET default_table_access_method = 'heap';
CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
SET default_table_access_method = 'heap2';
--
2.17.0
>From fc6b1552b1473bda344b5ed178247864f227c4d3 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 7 Mar 2021 02:05:23 -0600
Subject: [PATCH v2 3/3] Implement lsyscache get_rel_relam()
---
src/backend/commands/tablecmds.c | 12 +-----------
src/backend/utils/cache/lsyscache.c | 22 ++++++++++++++++++++++
src/include/utils/lsyscache.h | 1 +
3 files changed, 24 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 73cd6e311b..21eaeec170 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -865,22 +865,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
accessMethodId = get_table_am_oid(stmt->accessMethod, false);
else if (stmt->partbound && relkind == RELKIND_RELATION)
{
- HeapTuple tup;
- Oid relid;
-
/*
* For partitioned tables, when no access method is specified, we
* default to the parent table's AM.
*/
Assert(list_length(inheritOids) == 1);
- /* XXX: should implement get_rel_relam? */
- relid = linitial_oid(inheritOids);
- tup = SearchSysCache1(RELOID, ObjectIdDatumGet(relid));
- accessMethodId = ((Form_pg_class) GETSTRUCT(tup))->relam;
- if (!HeapTupleIsValid(tup))
- elog(ERROR, "cache lookup failed for relation %u", relid);
- ReleaseSysCache(tup);
-
+ accessMethodId = get_rel_relam(linitial_oid(inheritOids));
if (!OidIsValid(accessMethodId))
accessMethodId = get_table_am_oid(default_table_access_method, false);
}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 6bba5f8ec4..703d8d06be 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2062,6 +2062,28 @@ get_rel_persistence(Oid relid)
return result;
}
+/*
+ * get_rel_relam
+ *
+ * Returns the relam associated with a given relation.
+ */
+Oid
+get_rel_relam(Oid relid)
+{
+ HeapTuple tp;
+ Form_pg_class reltup;
+ Oid result;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ result = reltup->relam;
+ ReleaseSysCache(tp);
+
+ return result;
+}
+
/* ---------- TRANSFORM CACHE ---------- */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 77871aaefc..10145570fd 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid);
extern bool get_rel_relispartition(Oid relid);
extern Oid get_rel_tablespace(Oid relid);
extern char get_rel_persistence(Oid relid);
+extern Oid get_rel_relam(Oid relid);
extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes);
extern bool get_typisdefined(Oid typid);
--
2.17.0
