On Wed, Oct 1, 2025 at 2:42 PM jian he <[email protected]> wrote: > > “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.
hi. https://api.cirrus-ci.com/v1/artifact/task/5202996067303424/testrun/build/testrun/regress/regress/regression.diffs +ERROR: relation "t" already exists I made the same mistake, regress test using a simple name like "t" will conflict with other regress tests. please check the attached, mainly regress tests changes.
From 58e527e08e0673683295465e07e049d41b29d79e Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sat, 11 Oct 2025 11:03:51 +0800 Subject: [PATCH v3 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-ej...@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 21caf2d43bf..ef8498ddb23 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4223,6 +4223,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 dc09d1a3f03..4f9b8e3e381 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -795,6 +795,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..b8336de3343 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_storage(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_storage including storage parameter) with (fillfactor = 100); --error +ERROR: parameter "fillfactor" specified more than once +create table t_storage1(like t_storage excluding storage parameter) with (fillfactor = 100); --ok +\d+ t_storage1 + Table "public.t_storage1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Options: fillfactor=100 + +create table t_storage2(like t_storage 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_storage', 't_storage2') +order by c.relname \gx +-[ RECORD 1 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t_storage +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 | t_storage2 +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_storage, t_storage1, t_storage2; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 93389b57dbf..dddaa80b846 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_storage(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_storage including storage parameter) with (fillfactor = 100); --error +create table t_storage1(like t_storage excluding storage parameter) with (fillfactor = 100); --ok +\d+ t_storage1 + +create table t_storage2(like t_storage 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_storage', 't_storage2') +order by c.relname \gx + +drop table t_storage, t_storage1, t_storage2; -- 2.34.1
