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