Hi. Because of https://cfbot.cputube.org/patch_6088.log A trivial rebase is attached.
-- jian https://www.enterprisedb.com/
From e61c5e2ec1b04cb3f38f57bd903bc41d3be04a3d Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Fri, 3 Apr 2026 19:49:50 +0800 Subject: [PATCH v9 1/1] Add INCLUDING PARAMETERS to CREATE TABLE LIKE Introduce the PARAMETERS option to copy parameters when using CREATE TABLE LIKE. Currently, this primarily applies to the storage parameters, but in the future, it may encompass other kind parameters in the table. Since storage parameters are not in the standard, INCLUDING PARAMETERS does not comply with the standard. Also because foreign tables cannot specify storage parameters, CREATE FOREIGN TABLE LIKE will silently ignore INCLUDING PARAMETERS. This makes creating a new table via CREATE TABLE LIKE more convenient, as the user would otherwise have to manually specify every storage parameter. It is a further step toward making a new table created with CREATE TABLE LIKE more identical to the original table. Author: jian he <[email protected]> Reviewed-by: Euler Taveira <[email protected]> Reviewed-by: Nathan Bossart <[email protected]> Reviewed-by: David G. Johnston <[email protected]> Discussion: https://postgr.es/m/CACJufxHr=nKEsS66M7rTHgB+mXdQ948=_eJ1jztAc7PT-eJefA@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/6088 --- doc/src/sgml/ref/create_table.sgml | 14 +++- src/backend/access/common/reloptions.c | 17 +++- src/backend/parser/gram.y | 5 +- src/backend/parser/parse_utilcmd.c | 78 ++++++++++++++++++- src/include/access/reloptions.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 1 + .../regress/expected/create_table_like.out | 66 +++++++++++++++- src/test/regress/sql/create_table_like.sql | 44 ++++++++++- 9 files changed, 218 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 80829b23945..8bb84d7377d 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -100,7 +100,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> @@ -774,6 +774,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="sql-createtable-parms-like-opt-paramters"> + <term><literal>INCLUDING PARAMETERS</literal></term> + <listitem> + <para> + All table parameters, such as the storage parameter + 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 b41eafd7691..c8396c8a56a 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -1439,6 +1439,15 @@ transformRelOptions(Datum oldOptions, List *defList, const char *nameSpace, */ List * untransformRelOptions(Datum options) +{ + return untransformRelOptionsExtended(options, NULL); +} + +/* + * Same as untransformRelOptions() but includes a namespace into DefElem. + */ +List * +untransformRelOptionsExtended(Datum options, char *nameSpace) { List *result = NIL; ArrayType *array; @@ -1467,7 +1476,13 @@ untransformRelOptions(Datum options) *p++ = '\0'; val = (Node *) makeString(p); } - result = lappend(result, makeDefElem(s, val, -1)); + + if (nameSpace == NULL) + result = lappend(result, makeDefElem(s, val, -1)); + else + result = lappend(result, makeDefElemExtended(nameSpace, s, val, + DEFELEM_UNSPEC, + -1)); } return result; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index f4a08baa95a..d09f4c8f99d 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -801,7 +801,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 PARTITIONS PASSING PASSWORD PATH + PARALLEL PARAMETER PARAMETERS PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH PERIOD PLACING PLAN PLANS POLICY PORTION POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PROPERTIES PROPERTY PUBLICATION @@ -4350,6 +4350,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; } ; @@ -19020,6 +19021,7 @@ unreserved_keyword: | OWNER | PARALLEL | PARAMETER + | PARAMETERS | PARSER | PARTIAL | PARTITION @@ -19664,6 +19666,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 9a918e14aa7..82cfc5e29f7 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -89,6 +89,8 @@ 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 @@ -249,6 +251,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; @@ -373,6 +376,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); @@ -1123,8 +1127,8 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) * table has been created. * * Some options are ignored. For example, as foreign tables have no storage, - * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY - * and INDEXES. Similarly, INCLUDING INDEXES is ignored from a view. + * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY, + * INDEXES and PARAMETERS. Similarly, INCLUDING INDEXES is ignored from a view. */ static void transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause) @@ -1272,6 +1276,75 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla } } + /* Likewise, copy storage parameters if requested */ + if ((table_like_clause->options & CREATE_TABLE_LIKE_PARAMETERS) && + !cxt->isforeign && + relation->rd_rel->relkind != RELKIND_VIEW) + { + 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's reloptions */ + if (OidIsValid(relation->rd_rel->reltoastrelid)) + { + Relation toastrel; + Oid toastid = relation->rd_rel->reltoastrelid; + + /* + * The referenced table is already locked; acquire the lock on its + * associated TOAST relation now. + */ + toastrel = table_open(toastid, AccessShareLock); + + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(toastid)); + + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", toastid); + + 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); + + /* + * Close the toast relation, but keep our AccessShareLock on it + * until xact commit. That will prevent someone else from + * deleting or ALTERing the parent TOAST before we can run + * expandTableLikeClause. + */ + table_close(toastrel, NoLock); + } + } + /* * Reproduce not-null constraints, if any, by copying them. We do this * regardless of options given. @@ -3867,6 +3940,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 e8cb7f7a627..0154921cf72 100644 --- a/src/include/access/reloptions.h +++ b/src/include/access/reloptions.h @@ -235,6 +235,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 91377a6cde3..7ea0306e9fc 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -811,6 +811,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 51ead54f015..2e416cd2d1f 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -341,6 +341,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 5720d160f05..a13a15e4319 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -609,7 +609,8 @@ CREATE TABLE ctl_table(a int PRIMARY KEY, b varchar COMPRESSION pglz, c int GENERATED ALWAYS AS (a * 2) STORED, d bigint GENERATED ALWAYS AS IDENTITY, - e int DEFAULT 1); + e int DEFAULT 1) + WITH (fillfactor = 100); CREATE INDEX ctl_table_a_key ON ctl_table(a); COMMENT ON COLUMN ctl_table.b IS 'Column b'; CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; @@ -634,6 +635,7 @@ Statistics objects: Not-null constraints: "ctl_table_a_not_null" NOT NULL "a" "ctl_table_d_not_null" NOT NULL "d" +Options: fillfactor=100 -- Test EXCLUDING ALL CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0; @@ -665,7 +667,7 @@ SELECT attname, attcompression FROM pg_attribute (5 rows) -- Test INCLUDING ALL --- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +-- INDEXES, IDENTITY, COMPRESSION, STORAGE, PARAMETERS are not copied. CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; \d+ ctl_foreign_table2 Foreign table "public.ctl_foreign_table2" @@ -703,3 +705,63 @@ 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_sp (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 t_sp1 (LIKE t_sp INCLUDING PARAMETERS) WITH (fillfactor = 100); -- fail +ERROR: parameter "fillfactor" specified more than once +CREATE TABLE t_sp1 (LIKE t_sp EXCLUDING PARAMETERS) WITH (fillfactor = 100); -- ok +\d+ t_sp1 + Table "public.t_sp1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Options: fillfactor=100 + +CREATE TABLE t_sp2 (LIKE t_sp INCLUDING PARAMETERS) WITH ( + parallel_workers = 3, + toast.autovacuum_vacuum_threshold = 101, + toast.autovacuum_vacuum_scale_factor = 0.3); +SELECT c.relname, c.reloptions, t.reloptions as toast_reloptions + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class t ON (c.reltoastrelid = t.oid) + WHERE c.relname IN ('t_sp1', 't_sp2') + ORDER BY c.relname \gx +-[ RECORD 1 ]----+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t_sp1 +reloptions | {fillfactor=100} +toast_reloptions | +-[ RECORD 2 ]----+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t_sp2 +reloptions | {parallel_workers=3,fillfactor=100,toast_tuple_target=128,vacuum_index_cleanup=auto,vacuum_truncate=true,log_autovacuum_min_duration=100} +toast_reloptions | {autovacuum_vacuum_threshold=101,autovacuum_vacuum_scale_factor=0.3,vacuum_index_cleanup=auto,vacuum_truncate=false,log_autovacuum_min_duration=100} + +CREATE MATERIALIZED VIEW mv_dummy WITH (fillfactor = 10) AS SELECT 1 AS a; +CREATE TABLE t_sp3 (LIKE mv_dummy INCLUDING PARAMETERS); +\d+ t_sp3 + Table "public.t_sp3" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | +Options: fillfactor=10 + +CREATE VIEW t_spv1 WITH (check_option=local) AS SELECT a FROM t_sp; +CREATE TABLE t_sp4 (LIKE t_spv1 INCLUDING PARAMETERS) WITH (fillfactor = 10); +-- view option (check_option=local) will not copied to new table +\d+ t_sp4 + Table "public.t_sp4" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Options: fillfactor=10 + +DROP VIEW t_spv1; +DROP MATERIALIZED VIEW mv_dummy; +DROP TABLE t_sp, t_sp1, t_sp2, t_sp3; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 93389b57dbf..07a6b50f7e0 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -249,7 +249,8 @@ CREATE TABLE ctl_table(a int PRIMARY KEY, b varchar COMPRESSION pglz, c int GENERATED ALWAYS AS (a * 2) STORED, d bigint GENERATED ALWAYS AS IDENTITY, - e int DEFAULT 1); + e int DEFAULT 1) + WITH (fillfactor = 100); CREATE INDEX ctl_table_a_key ON ctl_table(a); COMMENT ON COLUMN ctl_table.b IS 'Column b'; @@ -268,7 +269,7 @@ SELECT attname, attcompression FROM pg_attribute WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; -- Test INCLUDING ALL --- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +-- INDEXES, IDENTITY, COMPRESSION, STORAGE, PARAMETERS are not copied. CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; \d+ ctl_foreign_table2 -- \d+ does not report the value of attcompression for a foreign table, so @@ -280,3 +281,42 @@ 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_sp (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 t_sp1 (LIKE t_sp INCLUDING PARAMETERS) WITH (fillfactor = 100); -- fail +CREATE TABLE t_sp1 (LIKE t_sp EXCLUDING PARAMETERS) WITH (fillfactor = 100); -- ok +\d+ t_sp1 + +CREATE TABLE t_sp2 (LIKE t_sp INCLUDING PARAMETERS) WITH ( + parallel_workers = 3, + toast.autovacuum_vacuum_threshold = 101, + toast.autovacuum_vacuum_scale_factor = 0.3); + +SELECT c.relname, c.reloptions, t.reloptions as toast_reloptions + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_class t ON (c.reltoastrelid = t.oid) + WHERE c.relname IN ('t_sp1', 't_sp2') + ORDER BY c.relname \gx + +CREATE MATERIALIZED VIEW mv_dummy WITH (fillfactor = 10) AS SELECT 1 AS a; +CREATE TABLE t_sp3 (LIKE mv_dummy INCLUDING PARAMETERS); +\d+ t_sp3 + +CREATE VIEW t_spv1 WITH (check_option=local) AS SELECT a FROM t_sp; +CREATE TABLE t_sp4 (LIKE t_spv1 INCLUDING PARAMETERS) WITH (fillfactor = 10); +-- view option (check_option=local) will not copied to new table +\d+ t_sp4 + +DROP VIEW t_spv1; +DROP MATERIALIZED VIEW mv_dummy; +DROP TABLE t_sp, t_sp1, t_sp2, t_sp3; -- 2.34.1
