public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: create table like including storage parameter
Date: Thu, 30 Oct 2025 09:57:24 +0800
Message-ID: <CACJufxFoU12ysn3YFr+vL30-s25iESmGyy=bN3WhhkB=pK1NSg@mail.gmail.com> (raw)
In-Reply-To: <aPu77xiPRJaIlc49@nathan>
References: <CACJufxHr=nKEsS66M7rTHgB+mXdQ948=_eJ1jztAc7PT-eJefA@mail.gmail.com>
<CAKFQuwbFTgLv4t7f63Xsco2r_8qjW9bdpi5vg1nq0D-o=X_6GQ@mail.gmail.com>
<aPu77xiPRJaIlc49@nathan>
On Sat, Oct 25, 2025 at 1:48 AM Nathan Bossart <[email protected]> wrote:
>
> On Sun, Sep 28, 2025 at 08:28:45PM -0500, David G. Johnston 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.
>
> +1
>
> --
> nathan
hi.
attached patch using syntax:
CREATE TABLE LIKE INCLUDING PARAMETERS.
Attachments:
[text/x-patch] v4-0001-create-table-like-incluing-parameters.patch (15.0K, 2-v4-0001-create-table-like-incluing-parameters.patch)
download | inline diff:
From 16dd551ad89c999d5e4ec654e20df7ac62df6a2d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 30 Oct 2025 09:53:22 +0800
Subject: [PATCH v4 1/1] create table like incluing parameters
demo:
create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true);
create table t2(like t including parameters);
\d+ t2
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
---
doc/src/sgml/ref/create_table.sgml | 12 +++-
src/backend/access/common/reloptions.c | 50 +++++++++++++++
src/backend/parser/gram.y | 5 +-
src/backend/parser/parse_utilcmd.c | 64 +++++++++++++++++++
src/include/access/reloptions.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 39 +++++++++++
src/test/regress/sql/create_table_like.sql | 28 ++++++++
9 files changed, 199 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index a157a244e4e..cad839b452d 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 | PARAMETERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -753,6 +753,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-storage-paramter">
+ <term><literal>INCLUDING PARAMETERS</literal></term>
+ <listitem>
+ <para>
+ All parameters, such as the <literal>STORAGE PARAMETER</literal> settings of the source table, will be copied.
+ For table storage parameters, see <xref linkend="sql-createtable-storage-parameters"/> below for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-statistics">
<term><literal>INCLUDING STATISTICS</literal></term>
<listitem>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 9e288dfecbf..365f30402f0 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -1392,6 +1392,56 @@ 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, otherwise 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 a4b29c822e8..fe4b035fe34 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -762,7 +762,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
+ PARALLEL PARAMETER PARAMETERS PARSER PARTIAL PARTITION PASSING PASSWORD PATH
PERIOD PLACING PLAN PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -4223,6 +4223,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | PARAMETERS { $$ = CREATE_TABLE_LIKE_PARAMETERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -17994,6 +17995,7 @@ unreserved_keyword:
| OWNER
| PARALLEL
| PARAMETER
+ | PARAMETERS
| PARSER
| PARTIAL
| PARTITION
@@ -18622,6 +18624,7 @@ bare_label_keyword:
| OWNER
| PARALLEL
| PARAMETER
+ | PARAMETERS
| PARSER
| PARTIAL
| PARTITION
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..791ffc5150c 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,66 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
}
+ /* Likewise, copy paramters if requested */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_PARAMETERS) &&
+ !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);
+
+ /* get the 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 +3638,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 ecbddd12e1b..a8e5538119c 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_PARAMETERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 84182eaaae2..d132eca0068 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -335,6 +335,7 @@ PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("parameter", PARAMETER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("parameters", PARAMETERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..2e8962590a0 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 PARAMETERS
+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 parameters) with (fillfactor = 100); --error
+ERROR: parameter "fillfactor" specified more than once
+create table t_storage1(like t_storage excluding parameters) 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 parameters) 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..e1abf5a4262 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 PARAMETERS
+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 parameters) with (fillfactor = 100); --error
+create table t_storage1(like t_storage excluding parameters) with (fillfactor = 100); --ok
+\d+ t_storage1
+
+create table t_storage2(like t_storage including parameters) 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
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: create table like including storage parameter
In-Reply-To: <CACJufxFoU12ysn3YFr+vL30-s25iESmGyy=bN3WhhkB=pK1NSg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox