On Mon, Sep 29, 2025 at 9:28 AM David G. Johnston <[email protected]> wrote: > > On Sunday, September 28, 2025, jian he <[email protected]> wrote: >> >> Since we already support INCLUDING STORAGE, I’m not sure that >> INCLUDING STORAGE PARAMETER is the right syntax to go with. > > I’d just call it “including parameters”; sure, all of the existing ones are > storage related, but if there were non-storage ones we’d include those as > well, so qualifying with a parameter type doesn’t seem correct. >
“including parameters” would add another keyword "PARAMETERS''. currently "INCLUDING STORAGE PARAMETER" no need to add a new keyword. If other people favor “including parameters”, then we can do it that way in the future. attached is just a simple rebase of v1.
From e3f87ac6080a229e33aa631d1adb9c24c3dfcf79 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 1 Oct 2025 14:29:19 +0800 Subject: [PATCH v2 1/1] create table like incluing storage parameter demo: create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true); create table t2(like t including storage parameter); Table "public.t2" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+------+-----------+----------+---------+----------+-------------+--------------+------------- a | text | | | | extended | | | Access method: heap Options: fillfactor=100, toast.vacuum_truncate=true discussion: https://postgr.es/m/CACJufxHr=nKEsS66M7rTHgB+mXdQ948=_eJ1jztAc7PT-eJefA@mail.gmail.com commitfest entry: https://commitfest.postgresql.org/patch/6088 --- doc/src/sgml/ref/create_table.sgml | 19 +++++- src/backend/access/common/reloptions.c | 49 +++++++++++++++ src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 63 +++++++++++++++++++ src/include/access/reloptions.h | 1 + src/include/nodes/parsenodes.h | 1 + .../regress/expected/create_table_like.out | 39 ++++++++++++ src/test/regress/sql/create_table_like.sql | 28 +++++++++ 8 files changed, 200 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index dc000e913c1..a389ce597a9 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl <phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase> -{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } +{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | STORAGE PARAMETER | ALL } <phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> @@ -776,6 +776,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="sql-createtable-parms-like-opt-storage-paramter"> + <term><literal>INCLUDING STORAGE PARAMETER</literal></term> + <listitem> + <para> + <literal>STORAGE PARAMETER</literal> settings for the copied table will be copied. + For table storage parameters, see <xref linkend="sql-createtable-storage-parameters"/> below for more information. + </para> + + <para> + Do not confuse this with <literal>INCLUDING STORAGE</literal>. + <literal>INCLUDING STORAGE</literal> is for copying table column storage property, + this is for copying table storage parameters. + </para> + + </listitem> + </varlistentry> + <varlistentry id="sql-createtable-parms-like-opt-all"> <term><literal>INCLUDING ALL</literal></term> <listitem> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 35150bf237b..6032cd5813e 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -1383,6 +1383,55 @@ untransformRelOptions(Datum options) return result; } +/* + * Convert the reloptions from text-array format into a List of DefElem. This + * is the reverse operation of transformRelOptions(). If any option includes a + * namespace qualification, create the DefElem in that namespace. + * If nameSpace is NULL, this behave the same as untransformRelOptions. + */ +List * +untransformRelOptionsExtended(Datum options, char* nameSpace) +{ + List *result = NIL; + ArrayType *array; + Datum *optiondatums; + int noptions; + int i; + + /* Nothing to do if no options */ + if (DatumGetPointer(options) == NULL) + return result; + + array = DatumGetArrayTypeP(options); + + deconstruct_array_builtin(array, TEXTOID, &optiondatums, NULL, &noptions); + + for (i = 0; i < noptions; i++) + { + char *s; + char *p; + Node *val = NULL; + + s = TextDatumGetCString(optiondatums[i]); + p = strchr(s, '='); + if (p) + { + *p++ = '\0'; + val = (Node *) makeString(p); + } + + if (nameSpace == NULL) + result = lappend(result, makeDefElem(s, val, -1)); + else + result = lappend(result, makeDefElemExtended(nameSpace, s, val, + DEFELEM_UNSPEC, + -1)); + } + + return result; +} + + /* * Extract and parse reloptions from a pg_class tuple. * diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 664b0483dbd..fc59268a076 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4225,6 +4225,7 @@ TableLikeOption: | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } + | STORAGE PARAMETER { $$ = CREATE_TABLE_LIKE_STORAGE_PARAMETER; } | ALL { $$ = CREATE_TABLE_LIKE_ALL; } ; diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index e96b38a59d5..bd401cba5e4 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -85,6 +85,7 @@ typedef struct List *fkconstraints; /* FOREIGN KEY constraints */ List *ixconstraints; /* index-creating constraints */ List *likeclauses; /* LIKE clauses that need post-processing */ + List *options; /* options from WITH clause, table AM specific parameters */ List *blist; /* "before list" of things to do before * creating the table */ List *alist; /* "after list" of things to do after creating @@ -245,6 +246,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; cxt.likeclauses = NIL; + cxt.options = stmt->options; cxt.blist = NIL; cxt.alist = NIL; cxt.pkey = NULL; @@ -369,6 +371,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) stmt->tableElts = cxt.columns; stmt->constraints = cxt.ckconstraints; stmt->nnconstraints = cxt.nnconstraints; + stmt->options = cxt.options; result = lappend(cxt.blist, stmt); result = list_concat(result, cxt.alist); @@ -1268,6 +1271,65 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla } } + /* Likewise, copy storage paramter if requested */ + if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE_PARAMETER) && + !cxt->isforeign) + { + HeapTuple tuple; + Datum reloptions; + bool isnull; + Oid relid; + List *oldoptions = NIL; + List *oldtoastoptions = NIL; + + relid = RelationGetRelid(relation); + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", relid); + + reloptions = SysCacheGetAttr(RELOID, tuple, + Anum_pg_class_reloptions, &isnull); + + if (!isnull) + { + oldoptions = untransformRelOptions(reloptions); + + foreach_node(DefElem, option, oldoptions) + cxt->options = lappend(cxt->options, option); + } + + ReleaseSysCache(tuple); + + /* now get the source relation toast relation reloptions */ + if (OidIsValid(relation->rd_rel->reltoastrelid)) + { + Relation toastrel; + + Oid toastid = relation->rd_rel->reltoastrelid; + + toastrel = table_open(toastid, AccessShareLock); + + /* Fetch heap tuple */ + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(toastid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for toast relation %u", toastid); + + /* Get the toast reloptions */ + reloptions = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions, + &isnull); + if (!isnull) + { + oldtoastoptions = untransformRelOptionsExtended(reloptions, "toast"); + foreach_node(DefElem, option, oldtoastoptions) + cxt->options = lappend(cxt->options, option); + } + + ReleaseSysCache(tuple); + + table_close(toastrel, NoLock); + } + } + /* * Reproduce not-null constraints, if any, by copying them. We do this * regardless of options given. @@ -3575,6 +3637,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; cxt.likeclauses = NIL; + cxt.options = NIL; cxt.blist = NIL; cxt.alist = NIL; cxt.pkey = NULL; diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h index a604a4702c3..8589616d08b 100644 --- a/src/include/access/reloptions.h +++ b/src/include/access/reloptions.h @@ -236,6 +236,7 @@ extern Datum transformRelOptions(Datum oldOptions, List *defList, const char *nameSpace, const char *const validnsps[], bool acceptOidsOff, bool isReset); extern List *untransformRelOptions(Datum options); +extern List *untransformRelOptionsExtended(Datum options, char* nameSpace); extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, amoptions_function amoptions); extern void *build_reloptions(Datum reloptions, bool validate, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ac0e02a1db7..4839f579cf6 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -794,6 +794,7 @@ typedef enum TableLikeOption CREATE_TABLE_LIKE_INDEXES = 1 << 6, CREATE_TABLE_LIKE_STATISTICS = 1 << 7, CREATE_TABLE_LIKE_STORAGE = 1 << 8, + CREATE_TABLE_LIKE_STORAGE_PARAMETER = 1 << 9, 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 d3c35c14847..193771f3408 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -699,3 +699,42 @@ DROP FOREIGN TABLE ctl_foreign_table1; DROP FOREIGN TABLE ctl_foreign_table2; DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; NOTICE: drop cascades to server ctl_s0 +--CREATE TABLE LIKE with STORAGE PARAMETER +create table t(a text) with ( + fillfactor = 100, + toast_tuple_target = 128, + vacuum_index_cleanup = auto, + toast.vacuum_index_cleanup = auto, + vacuum_truncate = true, + toast.vacuum_truncate = false, + log_autovacuum_min_duration = 100, + toast.log_autovacuum_min_duration = 100); +create table t1(like t including storage parameter) with (fillfactor = 100); --error +ERROR: parameter "fillfactor" specified more than once +create table t1(like t excluding storage parameter) with (fillfactor = 100); --ok +\d+ t1 + Table "public.t1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Options: fillfactor=100 + +create table t2(like t including storage parameter) with ( + parallel_workers = 3, + toast.autovacuum_vacuum_threshold = 101, + toast.autovacuum_vacuum_scale_factor = 0.3); +select c.relname, c.reloptions, tc.reloptions as toast_options +from pg_catalog.pg_class c +left join pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) +where c.relname in ('t', 't2') +order by c.relname \gx +-[ RECORD 1 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t +reloptions | {fillfactor=100,toast_tuple_target=128,vacuum_index_cleanup=auto,vacuum_truncate=true,log_autovacuum_min_duration=100} +toast_options | {vacuum_index_cleanup=auto,vacuum_truncate=false,log_autovacuum_min_duration=100} +-[ RECORD 2 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t2 +reloptions | {parallel_workers=3,fillfactor=100,toast_tuple_target=128,vacuum_index_cleanup=auto,vacuum_truncate=true,log_autovacuum_min_duration=100} +toast_options | {autovacuum_vacuum_threshold=101,autovacuum_vacuum_scale_factor=0.3,vacuum_index_cleanup=auto,vacuum_truncate=false,log_autovacuum_min_duration=100} + +drop table t, t1, t2; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 93389b57dbf..47f67d244e2 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -280,3 +280,31 @@ DROP TABLE ctl_table; DROP FOREIGN TABLE ctl_foreign_table1; DROP FOREIGN TABLE ctl_foreign_table2; DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; + +--CREATE TABLE LIKE with STORAGE PARAMETER +create table t(a text) with ( + fillfactor = 100, + toast_tuple_target = 128, + vacuum_index_cleanup = auto, + toast.vacuum_index_cleanup = auto, + vacuum_truncate = true, + toast.vacuum_truncate = false, + log_autovacuum_min_duration = 100, + toast.log_autovacuum_min_duration = 100); + +create table t1(like t including storage parameter) with (fillfactor = 100); --error +create table t1(like t excluding storage parameter) with (fillfactor = 100); --ok +\d+ t1 + +create table t2(like t including storage parameter) with ( + parallel_workers = 3, + toast.autovacuum_vacuum_threshold = 101, + toast.autovacuum_vacuum_scale_factor = 0.3); + +select c.relname, c.reloptions, tc.reloptions as toast_options +from pg_catalog.pg_class c +left join pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) +where c.relname in ('t', 't2') +order by c.relname \gx + +drop table t, t1, t2; -- 2.34.1
