I thought this was a good idea, but didn't hear back when I raised it before.
Failing to preserve access method is arguably a bug, reminiscent of CREATE
STATISTICS and 5564c1181. But maybe it's not important to backpatch a fix in
this case, since access methods are still evolving.
https://www.postgresql.org/message-id/20190818193533.gl11...@telsasoft.com
On Sun, Aug 18, 2019 at 02:35:33PM -0500, Justin Pryzby wrote:
> . What do you think about pg_restore --no-tableam; similar to
> --no-tablespaces, it would allow restoring a table to a different AM:
> PGOPTIONS='-c default_table_access_method=zedstore' pg_restore
> --no-tableam ./pg_dump.dat -d postgres
> Otherwise, the dump says "SET default_table_access_method=heap", which
> overrides any value from PGOPTIONS and precludes restoring to new AM.
...
> . it'd be nice if there was an ALTER TABLE SET ACCESS METHOD, to allow
> migrating data. Otherwise I think the alternative is:
> begin; lock t;
> CREATE TABLE new_t LIKE (t INCLUDING ALL) USING (zedstore);
> INSERT INTO new_t SELECT * FROM t;
> for index; do CREATE INDEX...; done
> DROP t; RENAME new_t (and all its indices). attach/inherit, etc.
> commit;
>
> . Speaking of which, I think LIKE needs a new option for ACCESS METHOD, which
> is otherwise lost.
>From 3df7de7f3f2b15c447534bcd7e05c5be79030404 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 15 Nov 2020 16:54:53 -0600
Subject: [PATCH v1] create table (like .. including ACCESS METHOD)
---
doc/src/sgml/ref/create_table.sgml | 12 +++++++++++-
src/backend/parser/gram.y | 3 ++-
src/backend/parser/parse_utilcmd.c | 7 +++++++
src/include/nodes/parsenodes.h | 17 +++++++++--------
src/test/regress/expected/create_table_like.out | 2 +-
src/test/regress/sql/create_table_like.sql | 2 +-
6 files changed, 31 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9da7..cb95177e92 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -87,7 +87,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { ACCESS METHOD | COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -593,6 +593,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
available options are:
<variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING ACCESS METHOD</literal></term>
+ <listitem>
+ <para>
+ The table's access method will be copied. By default, the
+ <literal>default_table_access_method</literal> is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8f341ac006..b32861a04e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3651,7 +3651,8 @@ TableLikeOptionList:
;
TableLikeOption:
- COMMENTS { $$ = CREATE_TABLE_LIKE_COMMENTS; }
+ ACCESS METHOD { $$ = CREATE_TABLE_LIKE_ACCESSMETHOD; }
+ | COMMENTS { $$ = CREATE_TABLE_LIKE_COMMENTS; }
| CONSTRAINTS { $$ = CREATE_TABLE_LIKE_CONSTRAINTS; }
| DEFAULTS { $$ = CREATE_TABLE_LIKE_DEFAULTS; }
| IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; }
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 89ee990599..3507fd4738 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -96,6 +96,7 @@ typedef struct
bool ispartitioned; /* true if table is partitioned */
PartitionBoundSpec *partbound; /* transformed FOR VALUES */
bool ofType; /* true if statement contains OF typename */
+ char *accessMethod; /* table access method */
} CreateStmtContext;
/* State shared by transformCreateSchemaStmt and its subroutines */
@@ -252,6 +253,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
cxt.ispartitioned = stmt->partspec != NULL;
cxt.partbound = stmt->partbound;
cxt.ofType = (stmt->ofTypename != NULL);
+ cxt.accessMethod = NULL;
Assert(!stmt->ofTypename || !stmt->inhRelations); /* grammar enforces */
@@ -345,6 +347,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
*/
stmt->tableElts = cxt.columns;
stmt->constraints = cxt.ckconstraints;
+ if (cxt.accessMethod != NULL)
+ stmt->accessMethod = cxt.accessMethod;
result = lappend(cxt.blist, stmt);
result = list_concat(result, cxt.alist);
@@ -1118,6 +1122,9 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
}
+ if (table_like_clause->options & CREATE_TABLE_LIKE_ACCESSMETHOD)
+ cxt->accessMethod = get_am_name(relation->rd_rel->relam);
+
/*
* We may copy extended statistics if requested, since the representation
* of CreateStatsStmt doesn't depend on column numbers.
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48a79a7657..589fd0cab0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -677,14 +677,15 @@ typedef struct TableLikeClause
typedef enum TableLikeOption
{
- CREATE_TABLE_LIKE_COMMENTS = 1 << 0,
- CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
- CREATE_TABLE_LIKE_DEFAULTS = 1 << 2,
- CREATE_TABLE_LIKE_GENERATED = 1 << 3,
- CREATE_TABLE_LIKE_IDENTITY = 1 << 4,
- CREATE_TABLE_LIKE_INDEXES = 1 << 5,
- CREATE_TABLE_LIKE_STATISTICS = 1 << 6,
- CREATE_TABLE_LIKE_STORAGE = 1 << 7,
+ CREATE_TABLE_LIKE_ACCESSMETHOD = 1 << 0,
+ CREATE_TABLE_LIKE_COMMENTS = 1 << 1,
+ CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 2,
+ CREATE_TABLE_LIKE_DEFAULTS = 1 << 3,
+ CREATE_TABLE_LIKE_GENERATED = 1 << 4,
+ CREATE_TABLE_LIKE_IDENTITY = 1 << 5,
+ CREATE_TABLE_LIKE_INDEXES = 1 << 6,
+ CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
+ CREATE_TABLE_LIKE_STORAGE = 1 << 8,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 10d17be23c..cac3c57c76 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -490,7 +490,7 @@ Check constraints:
"noinh_con_copy_a_check" CHECK (a > 0) NO INHERIT
-- fail, as partitioned tables don't allow NO INHERIT constraints
-CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL EXCLUDING ACCESS METHOD)
PARTITION BY LIST (a);
ERROR: cannot add NO INHERIT constraint to partitioned table "noinh_con_copy1_parted"
DROP TABLE noinh_con_copy, noinh_con_copy1;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 06b76f949d..eda9f39078 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -189,7 +189,7 @@ CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS);
\d noinh_con_copy1
-- fail, as partitioned tables don't allow NO INHERIT constraints
-CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL EXCLUDING ACCESS METHOD)
PARTITION BY LIST (a);
DROP TABLE noinh_con_copy, noinh_con_copy1;
--
2.17.0
