Horton

Thread

  1. create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-09-29T00:00:00Z

    hi.

attached patch is to make CREATE TABLE LIKE copy source relation
storage parameter
demo:

create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true);
create table t2(like t including storage parameter) with (parallel_workers = 3);

                                          Table "public.t2"
 Column | Type | Collation | Nullable | Default | Storage  |
Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | text |           |          |         | extended |
  |              |
Access method: heap
Options: parallel_workers=3, fillfactor=100, toast.vacuum_truncate=true

of course, duplicate storage mention would result error,
for example:
create table t3(like t including storage parameter) with (fillfactor = 100);

Since we already support INCLUDING STORAGE, I’m not sure that
INCLUDING STORAGE PARAMETER is the right syntax to go with.

  2. Re: create table like including storage parameter

    David G. Johnston <david.g.johnston@gmail.com> — 2025-09-29T01:28:45Z

    On Sunday, September 28, 2025, jian he <jian.universality@gmail.com> 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.

David J.

  3. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-10-01T06:42:29Z

    On Mon, Sep 29, 2025 at 9:28 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Sunday, September 28, 2025, jian he <jian.universality@gmail.com> 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.

  4. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-10-11T05:08:08Z

    On Wed, Oct 1, 2025 at 2:42 PM jian he <jian.universality@gmail.com> 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.

  5. Re: create table like including storage parameter

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-24T17:48:31Z

    On Sun, Sep 28, 2025 at 08:28:45PM -0500, David G. Johnston wrote:
> On Sunday, September 28, 2025, jian he <jian.universality@gmail.com> 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

  6. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-10-30T01:57:24Z

    On Sat, Oct 25, 2025 at 1:48 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Sun, Sep 28, 2025 at 08:28:45PM -0500, David G. Johnston wrote:
> > On Sunday, September 28, 2025, jian he <jian.universality@gmail.com> 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.

  7. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-12-15T08:48:12Z

    On Thu, Oct 30, 2025 at 9:57 AM jian he <jian.universality@gmail.com> wrote:
>
> attached patch using syntax:
> CREATE TABLE LIKE INCLUDING PARAMETERS.

hi.
rebased, with minor comment polishing.


--
jian
https://www.enterprisedb.com/

  8. Re: create table like including storage parameter

    Euler Taveira <euler@eulerto.com> — 2025-12-15T17:51:49Z

    On Mon, Dec 15, 2025, at 5:48 AM, jian he wrote:
> rebased, with minor comment polishing.
>

I started reviewing this patch. Why don't you reuse untransformRelOptions()?
Even if this function is used by various extensions [1][2], that's not an
excuse to duplicate code. I adjusted the code in the fixup. I did a bunch of
stylish changes and fixed a few typos. I also ran pgindent. I removed some
superfluous comments ("Fetch heap tuple", "Get the toast reloptions",). Don't
think they are saying something important. Coverage looks good. As a homework,
add a nice commit message. Showing how it works is not a good commit message.

This v6 is your v5 plus my suggestions.

[1] https://codesearch.debian.net/search?q=untransformRelOptions&literal=1
[2] https://github.com/search?q=untransformRelOptions&type=code


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/

  9. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-12-16T02:45:04Z

    On Tue, Dec 16, 2025 at 1:52 AM Euler Taveira <euler@eulerto.com> wrote:
>
> I started reviewing this patch. Why don't you reuse untransformRelOptions()?
> Even if this function is used by various extensions [1][2], that's not an
> excuse to duplicate code.

yech, duplicate code is not good. I encountered the same issue in the CAST
DEFAULT patch.  I guess I was posting it in the "post early post often" spirit
for the rebase.

> This v6 is your v5 plus my suggestions.
>

your suggestions are great.
I have incorporated all the changes you proposed.

in transformTableLikeClause, we have:
    if (relation->rd_rel->relkind != RELKIND_RELATION &&
        relation->rd_rel->relkind != RELKIND_VIEW &&
        relation->rd_rel->relkind != RELKIND_MATVIEW &&
        relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE &&
        relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
        relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
        ereport(ERROR,
                (errcode(ERRCODE_WRONG_OBJECT_TYPE),
                 errmsg("relation \"%s\" is invalid in LIKE clause",
                        RelationGetRelationName(relation)),
                 errdetail_relkind_not_supported(relation->rd_rel->relkind)));

except RELKIND_RELATION, RELKIND_MATVIEW can also specify storage parameters, I
added a test for it too.

to test CREATE FOREIGN LIKE will silently ignore INCLUDING PARAMETERS, i also
slightly modified create_table_like.sql.

The last SELECT query output is too wide, to make it more readable, I
have to use \gx.

Below is the commit message I have drafted:
----------------------------------
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 <jian.universality@gmail.com>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Nathan Bossart <nathandbossart@gmail.com>
Reviewed-by: David G. Johnston <david.g.johnston@gmail.com>

(delete this line later) commitfest:
https://commitfest.postgresql.org/patch/6088
discussion: https://postgr.es/m/CACJufxHr=nKEsS66M7rTHgB+mXdQ948=_eJ1jztAc7PT-eJefA@mail.gmail.com
----------------------------------

--
jian
https://www.enterprisedb.com/

  10. Re: create table like including storage parameter

    jian he <jian.universality@gmail.com> — 2025-12-17T06:41:06Z

    Hi,

current, v7 the DOC changes:
+       <varlistentry id="sql-createtable-parms-like-opt-storage-paramter">
+        <term><literal>INCLUDING PARAMETERS</literal></term>
+        <listitem>
+         <para>
+          All table 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>

https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-ATTRIBUTE-OPTION

Does the "parameter" (INCLUDING PARAMETERS) definition is close to "option"?
This raises the question of whether we should also copy column-level options to
the new table.

What do you think?

--
jian
https://www.enterprisedb.com

  11. Re: create table like including storage parameter

    Euler Taveira <euler@eulerto.com> — 2025-12-18T14:59:39Z

    On Wed, Dec 17, 2025, at 3:41 AM, jian he wrote:
> Does the "parameter" (INCLUDING PARAMETERS) definition is close to "option"?
> This raises the question of whether we should also copy column-level options to
> the new table.
>

I don't think so. The current attribute options (n_distinct and
n_distinct_inherited) are closely tied to the data. I'm afraid that this
decision would impose bad plans in the future.


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/