public inbox for [email protected]
help / color / mirror / Atom feedFrom: Zhang Mingli <[email protected]>
To: Sami Imseih <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Proposal to CREATE FOREIGN TABLE LIKE
Date: Sun, 9 Feb 2025 21:16:53 +0800
Message-ID: <3904c689-2801-4916-99ed-7a7e1d86da3e@Spark> (raw)
In-Reply-To: <CAA5RZ0uR9Km4qB9JMhbyi2iMc3E0PdCRz=GWn==_5t=Q2Sxosw@mail.gmail.com>
References: <db68bd6f-6085-4152-8799-82ac929b256d@Spark>
<[email protected]>
<CANG7CA+W=KeYFGqGEtoaZy7iV6VHWnrhbBnWrbNt_vgmAuw3Rg@mail.gmail.com>
<[email protected]>
<9324b4d3-d49f-463e-984a-2725fe36cb2b@Spark>
<3ded6d8c-c417-4812-8861-12cbb7f8e182@Spark>
<c5dc00e9-3f19-47fc-8409-47581020d09e@Spark>
<CAA5RZ0uR9Km4qB9JMhbyi2iMc3E0PdCRz=GWn==_5t=Q2Sxosw@mail.gmail.com>
On Feb 8, 2025 at 12:55 +0800, Sami Imseih <[email protected]>, wrote:
> > Fix CI failure of doc build in v1 patch.
> >
> > Thanks for the patch! I am +1 for this, but I have a few comments:
Hi, tanks for review.
> >
> > 1/ In the IDENTITY case, the remote side may not be
> > able to handle the DEFAULT value.
Yes, and done.
> > 2/ As IDENTITY, STORAGE is also allowed on foreign tables, which
> > does not make much sense either, as the fdw may not be Postgres,
> > or if it is Postgres, it may have a different STORAGE setting. This is
> > also not documented. I am inclined to say to not include it either.
Done.
> >
> > I think we should not allow IDENTITY and STORAGE in this patch
> > as they are not documented [1] as is, and perhaps a separate discussion
> > to correct the behavior for the CREATE FOREIGN TABLE case.
Yes, however, I found we have doc to tell users and they actually could ALTER FOREIGN TABLE to SET STORAGE...
There are several inconsistencies, I have a summary in Inconsistency between Compression and Storage for Foreign Tables[0]
We could discuss and fix them there.
> >
> > 3/ a minor nit on the comments.
> >
> > instead of
> >
> > + Foreign tables have no real storage in PostgreSQL.
> >
> > Can it just be
> >
> > Foreign tables have no storage in PostgreSQL.
> >
> > the "real" is not needed.
Done.
Patch V2 addressed the comments.
[0] https://www.postgresql.org/message-id/6cecef0e-ee14-473c-bb0a-6aa61f539a66%40Spark
--
Zhang Mingli
HashData
Attachments:
[application/octet-stream] v2-0001-CREATE-FOREIGN-TABLE-LIKE.patch (22.9K, ../3904c689-2801-4916-99ed-7a7e1d86da3e@Spark/3-v2-0001-CREATE-FOREIGN-TABLE-LIKE.patch)
download | inline diff:
From 9ca09b68bade7b5f97009cd47c99d6634ef475ad Mon Sep 17 00:00:00 2001
From: Zhang Mingli <[email protected]>
Date: Sat, 8 Feb 2025 00:46:17 +0800
Subject: [PATCH] CREATE FOREIGN TABLE LIKE
Added support for CREATE FOREIGN TABLE LIKE to enable the creation of
foreign tables based on the column definitions, constraints of existing
source tables.
This feature mirrors the behavior of CREATE TABLE LIKE,
but ignores inapplicable options such as INCLUDING INDEXES,
INCLUDING COMPRESSION, INCLUDING IDENTITY, INCLUDING STORAGE
for foreign tables.
Authored-by: Zhang Mingli [email protected]
---
doc/src/sgml/ref/create_foreign_table.sgml | 118 ++++++++++++-
src/backend/parser/parse_utilcmd.c | 28 +--
.../regress/expected/create_table_like.out | 165 ++++++++++++++++++
src/test/regress/sql/create_table_like.sql | 57 ++++++
4 files changed, 356 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f8..e9ab0a2b53 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -23,7 +23,8 @@ PostgreSQL documentation
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
- | <replaceable>table_constraint</replaceable> }
+ | <replaceable>table_constraint</replaceable>
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] }
[ ENFORCED | NOT ENFORCED ]
+<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
+
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) |
@@ -191,6 +196,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a table from which
+ the new table automatically copies all column names, their data types,
+ and their not-null constraints.
+ </para>
+ <para>
+ Unlike <literal>INHERITS</literal>, the new table and original table
+ are completely decoupled after creation is complete. Changes to the
+ original table will not be applied to the new table, and it is not
+ possible to include data of the new table in scans of the original
+ table.
+ </para>
+ <para>
+ Also unlike <literal>INHERITS</literal>, columns and
+ constraints copied by <literal>LIKE</literal> are not merged with similarly
+ named columns and constraints.
+ If the same name is specified explicitly or in another
+ <literal>LIKE</literal> clause, an error is signaled.
+ </para>
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original table to copy. Specifying
+ <literal>INCLUDING</literal> copies the property, specifying
+ <literal>EXCLUDING</literal> omits the property.
+ <literal>EXCLUDING</literal> is the default. If multiple specifications
+ are made for the same kind of object, the last one is used. The
+ available options are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING COMMENTS</literal></term>
+ <listitem>
+ <para>
+ Comments for the copied columns, constraints, and indexes will be
+ copied. The default behavior is to exclude comments, resulting in
+ the copied columns and constraints in the new table having no
+ comments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING CONSTRAINTS</literal></term>
+ <listitem>
+ <para>
+ <literal>CHECK</literal> constraints will be copied. No distinction
+ is made between column constraints and table constraints. Not-null
+ constraints are always copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING DEFAULTS</literal></term>
+ <listitem>
+ <para>
+ Default expressions for the copied column definitions will be
+ copied. Otherwise, default expressions are not copied, resulting in
+ the copied columns in the new table having null defaults. Note that
+ copying defaults that call database-modification functions, such as
+ <function>nextval</function>, may create a functional linkage
+ between the original and new tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING GENERATED</literal></term>
+ <listitem>
+ <para>
+ Any generation expressions of copied column definitions will be
+ copied. By default, new columns will be regular base columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING STATISTICS</literal></term>
+ <listitem>
+ <para>
+ Extended statistics are copied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING ALL</literal></term>
+ <listitem>
+ <para>
+ <literal>INCLUDING ALL</literal> is an abbreviated form selecting
+ all the available individual options. (It could be useful to write
+ individual <literal>EXCLUDING</literal> clauses after
+ <literal>INCLUDING ALL</literal> to select all but some specific
+ options.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Foreign tables have no real storage in PostgreSQL.
+ Inapplicable options: <literal>INCLUDING INDEXES</literal>, <literal>INCLUDING STORAGE</literal>,
+ <literal>INCLUDING COMPRESSION</literal>, <literal>INCLUDING IDENTITY</literal> are ignored.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term>
<listitem>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index ca028d2a66..f6db43e034 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1117,6 +1117,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
* process at this point, add the TableLikeClause to cxt->likeclauses, which
* will cause utility.c to call expandTableLikeClause() after the new
* table has been created.
+ *
+ * For foreign tables, they have no storage in Postgres.
+ * Inapplicable options are ignored:
+ * CREATE_TABLE_LIKE_COMPRESSION
+ * CREATE_TABLE_LIKE_IDENTITY
+ * CREATE_TABLE_LIKE_STORAGE
+ * CREATE_TABLE_LIKE_INDEXES
+ * while INDEXES is handled in expandTableLikeClause() later.
*/
static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
@@ -1131,12 +1139,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
setup_parser_errposition_callback(&pcbstate, cxt->pstate,
table_like_clause->relation->location);
- /* we could support LIKE in many cases, but worry about it another day */
- if (cxt->isforeign)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("LIKE is not supported for creating foreign tables")));
-
/* Open the relation referenced by the LIKE clause */
relation = relation_openrv(table_like_clause->relation, AccessShareLock);
@@ -1217,7 +1219,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
* Copy identity if requested
*/
if (attribute->attidentity &&
- (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) &&
+ !cxt->isforeign)
{
Oid seq_relid;
List *seq_options;
@@ -1236,14 +1239,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/* Likewise, copy storage if requested */
- if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) &&
+ !cxt->isforeign)
def->storage = attribute->attstorage;
else
def->storage = 0;
/* Likewise, copy compression if requested */
- if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
- && CompressionMethodIsValid(attribute->attcompression))
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 &&
+ CompressionMethodIsValid(attribute->attcompression) &&
+ !cxt->isforeign)
def->compression =
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
@@ -1522,7 +1527,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
* Process indexes if required.
*/
if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) &&
- relation->rd_rel->relhasindex)
+ relation->rd_rel->relhasindex &&
+ childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
List *parent_indexes;
ListCell *l;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index e061389135..4048cd19db 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -563,3 +563,168 @@ DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+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;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | text | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+ Foreign table "public.ctl_foreign_table1"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+ Foreign table "public.ctl_foreign_table2"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+ Foreign table "public.ctl_foreign_table3"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
+ a | integer | | | 1 | plain | | |
+ b | character varying | | | | extended | pglz | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | | |
+ d | bigint | | not null | generated always as identity | plain | | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+ Foreign table "public.ctl_foreign_table4"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+\set HIDE_TOAST_COMPRESSION true
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+ Table "public.ctl_table"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+----------+--------------+-------------
+ a | integer | | | 1 | plain | |
+ b | character varying | | | | external | | Column b
+ c | integer | | | generated always as (a * 2) stored | plain | |
+ d | bigint | | not null | generated always as identity | plain | |
+Indexes:
+ "ctl_table_a_key" btree (a)
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_table_stat" ON a, b FROM ctl_table
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+ Foreign table "public.ctl_foreign_table5"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | character varying | | | | | extended | |
+ c | integer | | | | | plain | |
+ d | bigint | | not null | | | plain | |
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+ Foreign table "public.ctl_foreign_table6"
+ Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
+--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+-------------
+ a | integer | | | 1 | | plain | |
+ b | character varying | | | | | extended | | Column b
+ c | integer | | | generated always as (a * 2) stored | | plain | |
+ d | bigint | | not null | | | plain | |
+Check constraints:
+ "foo" CHECK (b::text = 'text'::text)
+Statistics objects:
+ "public.ctl_foreign_table6_a_b_stat" ON a, b FROM ctl_foreign_table6
+Not-null constraints:
+ "ctl_table_d_not_null" NOT NULL "d"
+Server: ctl_s0
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+NOTICE: drop cascades to server ctl_s0
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index a41f8b83d7..ea586af438 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -225,3 +225,60 @@ DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+--
+-- CREATE FOREIGN TABLE LIKE
+--
+CREATE FOREIGN DATA WRAPPER ctl_dummy;
+CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy;
+
+CREATE TABLE ctl_table(a int DEFAULT 1, b text,
+ c int GENERATED ALWAYS AS (a * 2) STORED,
+ d bigint GENERATED ALWAYS AS IDENTITY);
+
+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;
+ALTER TABLE ctl_table add constraint foo CHECK (b = 'text');
+\d+ ctl_table
+
+-- Test EXCLUDING ALL by default
+CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table) SERVER ctl_s0;
+\d+ ctl_foreign_table1
+
+-- Test INCLUDING INDEXES does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING INDEXES) SERVER ctl_s0;
+\d+ ctl_foreign_table2
+
+-- Test INCLUDING IDENTITY does't take effect
+CREATE FOREIGN TABLE ctl_foreign_table3(LIKE ctl_table INCLUDING IDENTITY) SERVER ctl_s0;
+\d+ ctl_foreign_table3
+
+-- Test INCLUDING COMPRESSION does't take effect
+\set HIDE_TOAST_COMPRESSION false
+ALTER TABLE ctl_table ALTER COLUMN b TYPE varchar;
+ALTER TABLE ctl_table ALTER COLUMN b SET COMPRESSION pglz;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table4(LIKE ctl_table INCLUDING COMPRESSION) SERVER ctl_s0;
+\d+ ctl_foreign_table4
+\set HIDE_TOAST_COMPRESSION true
+
+-- Test INCLUDING STORAGE does't take effect
+ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE external;
+\d+ ctl_table
+CREATE FOREIGN TABLE ctl_foreign_table5(LIKE ctl_table INCLUDING STORAGE) SERVER ctl_s0;
+\d+ ctl_foreign_table5
+
+
+-- Test valid like_options take effect:
+CREATE FOREIGN TABLE ctl_foreign_table6(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0;
+\d+ ctl_foreign_table6
+
+DROP TABLE ctl_table;
+DROP FOREIGN TABLE ctl_foreign_table1;
+DROP FOREIGN TABLE ctl_foreign_table2;
+DROP FOREIGN TABLE ctl_foreign_table3;
+DROP FOREIGN TABLE ctl_foreign_table4;
+DROP FOREIGN TABLE ctl_foreign_table5;
+DROP FOREIGN TABLE ctl_foreign_table6;
+DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
--
2.34.1
view thread (12+ 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], [email protected], [email protected]
Subject: Re: Proposal to CREATE FOREIGN TABLE LIKE
In-Reply-To: <3904c689-2801-4916-99ed-7a7e1d86da3e@Spark>
* 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