v24-0001-Enable-support-for-include_generated_columns-opt.patch
application/octet-stream
Filename: v24-0001-Enable-support-for-include_generated_columns-opt.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v24-0001
Subject: Enable support for 'include_generated_columns' option
| File | + | − |
|---|---|---|
| contrib/test_decoding/expected/generated_columns.out | 52 | 0 |
| contrib/test_decoding/Makefile | 2 | 1 |
| contrib/test_decoding/meson.build | 1 | 0 |
| contrib/test_decoding/sql/generated_columns.sql | 22 | 0 |
| contrib/test_decoding/test_decoding.c | 21 | 5 |
| doc/src/sgml/ddl.sgml | 4 | 2 |
| doc/src/sgml/protocol.sgml | 15 | 2 |
| doc/src/sgml/ref/create_subscription.sgml | 20 | 0 |
| src/backend/catalog/pg_publication.c | 1 | 8 |
| src/backend/catalog/pg_subscription.c | 1 | 0 |
| src/backend/commands/subscriptioncmds.c | 30 | 1 |
| src/backend/replication/libpqwalreceiver/libpqwalreceiver.c | 4 | 0 |
| src/backend/replication/logical/proto.c | 39 | 17 |
| src/backend/replication/logical/relation.c | 1 | 1 |
| src/backend/replication/logical/worker.c | 1 | 0 |
| src/backend/replication/pgoutput/pgoutput.c | 31 | 10 |
| src/bin/pg_dump/pg_dump.c | 15 | 2 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/psql/describe.c | 7 | 1 |
| src/bin/psql/tab-complete.c | 2 | 1 |
| src/include/catalog/pg_subscription.h | 4 | 0 |
| src/include/replication/logicalproto.h | 9 | 4 |
| src/include/replication/pgoutput.h | 1 | 0 |
| src/include/replication/walreceiver.h | 2 | 0 |
| src/test/regress/expected/publication.out | 2 | 2 |
| src/test/regress/expected/subscription.out | 81 | 76 |
| src/test/regress/sql/publication.sql | 2 | 1 |
| src/test/regress/sql/subscription.sql | 4 | 0 |
| src/test/subscription/t/011_generated.pl | 527 | 4 |
| src/test/subscription/t/031_column_list.pl | 3 | 3 |
From 4413fa5357d52bc6439733bc6b9eac63be4cf47f Mon Sep 17 00:00:00 2001
From: Khanna <Shubham.Khanna@fujitsu.com>
Date: Wed, 24 Jul 2024 11:22:07 +0530
Subject: [PATCH v24 1/2] Enable support for 'include_generated_columns' option
Currently generated column values are not replicated because it is assumed
that the corresponding subscriber-side table will generate its own values
for those columns.
This commit enables support for the 'include_generated_columns' option in
logical replication, allowing the transmission of generated column information
and data alongside regular table changes.
With this enhancement, users can now include the 'include_generated_columns'
option when querying logical replication slots using either the pgoutput
plugin or the test_decoding plugin. This option, when set to 'true' or '1',
instructs the replication system to include generated column information
and data in the replication stream.
When 'include_generated_columns' is false, generated columns are not
replicated, even when present in a PUBLICATION col-list.
Usage from test_decoding plugin:
SELECT data FROM pg_logical_slot_get_changes('slot2', NULL, NULL,
'include-xids', '0','skip-empty-xacts', '1',
'include-generated-columns','1');
Using Create Subscription:
CREATE SUBSCRIPTION regress_sub_combo2 CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_combo WITH (include_generated_columns = true,
copy_data = false)
If the subscriber-side column is also a generated column then this option
has no effect; the replicated data will be ignored and the subscriber
column will be filled as normal with the subscriber-side computed or
default data.
Currently 'copy_data' option with 'include_generated_columns' option is not
supported.
A future patch will remove this limitation.
'include_generated_columns' cannot be altered as it can lead to inconsistency.
---
contrib/test_decoding/Makefile | 3 +-
.../expected/generated_columns.out | 52 ++
contrib/test_decoding/meson.build | 1 +
.../test_decoding/sql/generated_columns.sql | 22 +
contrib/test_decoding/test_decoding.c | 26 +-
doc/src/sgml/ddl.sgml | 6 +-
doc/src/sgml/protocol.sgml | 17 +-
doc/src/sgml/ref/create_subscription.sgml | 20 +
src/backend/catalog/pg_publication.c | 9 +-
src/backend/catalog/pg_subscription.c | 1 +
src/backend/commands/subscriptioncmds.c | 31 +-
.../libpqwalreceiver/libpqwalreceiver.c | 4 +
src/backend/replication/logical/proto.c | 56 +-
src/backend/replication/logical/relation.c | 2 +-
src/backend/replication/logical/worker.c | 1 +
src/backend/replication/pgoutput/pgoutput.c | 41 +-
src/bin/pg_dump/pg_dump.c | 17 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/describe.c | 8 +-
src/bin/psql/tab-complete.c | 3 +-
src/include/catalog/pg_subscription.h | 4 +
src/include/replication/logicalproto.h | 13 +-
src/include/replication/pgoutput.h | 1 +
src/include/replication/walreceiver.h | 2 +
src/test/regress/expected/publication.out | 4 +-
src/test/regress/expected/subscription.out | 157 +++---
src/test/regress/sql/publication.sql | 3 +-
src/test/regress/sql/subscription.sql | 4 +
src/test/subscription/t/011_generated.pl | 531 +++++++++++++++++-
src/test/subscription/t/031_column_list.pl | 6 +-
30 files changed, 905 insertions(+), 141 deletions(-)
create mode 100644 contrib/test_decoding/expected/generated_columns.out
create mode 100644 contrib/test_decoding/sql/generated_columns.sql
diff --git a/contrib/test_decoding/Makefile b/contrib/test_decoding/Makefile
index a4ba1a509a..59f0956e85 100644
--- a/contrib/test_decoding/Makefile
+++ b/contrib/test_decoding/Makefile
@@ -5,7 +5,8 @@ PGFILEDESC = "test_decoding - example of a logical decoding output plugin"
REGRESS = ddl xact rewrite toast permissions decoding_in_xact \
decoding_into_rel binary prepared replorigin time messages \
- spill slot truncate stream stats twophase twophase_stream
+ spill slot truncate stream stats twophase twophase_stream \
+ generated_columns
ISOLATION = mxact delayed_startup ondisk_startup concurrent_ddl_dml \
oldest_xmin snapshot_transfer subxact_without_top concurrent_stream \
twophase_snapshot slot_creation_error catalog_change_snapshot \
diff --git a/contrib/test_decoding/expected/generated_columns.out b/contrib/test_decoding/expected/generated_columns.out
new file mode 100644
index 0000000000..f3b26aa9e1
--- /dev/null
+++ b/contrib/test_decoding/expected/generated_columns.out
@@ -0,0 +1,52 @@
+-- test decoding of generated columns
+SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
+ ?column?
+----------
+ init
+(1 row)
+
+-- column b' is a generated column
+CREATE TABLE gencoltable (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+-- when 'include-generated-columns' is not set the generated column 'b' values will be replicated
+INSERT INTO gencoltable (a) VALUES (1), (2), (3);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+ data
+-------------------------------------------------------------
+ BEGIN
+ table public.gencoltable: INSERT: a[integer]:1 b[integer]:2
+ table public.gencoltable: INSERT: a[integer]:2 b[integer]:4
+ table public.gencoltable: INSERT: a[integer]:3 b[integer]:6
+ COMMIT
+(5 rows)
+
+-- when 'include-generated-columns' = '1' the generated column 'b' values will be replicated
+INSERT INTO gencoltable (a) VALUES (4), (5), (6);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '1');
+ data
+--------------------------------------------------------------
+ BEGIN
+ table public.gencoltable: INSERT: a[integer]:4 b[integer]:8
+ table public.gencoltable: INSERT: a[integer]:5 b[integer]:10
+ table public.gencoltable: INSERT: a[integer]:6 b[integer]:12
+ COMMIT
+(5 rows)
+
+-- when 'include-generated-columns' = '0' the generated column 'b' values will not be replicated
+INSERT INTO gencoltable (a) VALUES (7), (8), (9);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '0');
+ data
+------------------------------------------------
+ BEGIN
+ table public.gencoltable: INSERT: a[integer]:7
+ table public.gencoltable: INSERT: a[integer]:8
+ table public.gencoltable: INSERT: a[integer]:9
+ COMMIT
+(5 rows)
+
+DROP TABLE gencoltable;
+SELECT 'stop' FROM pg_drop_replication_slot('regression_slot');
+ ?column?
+----------
+ stop
+(1 row)
+
diff --git a/contrib/test_decoding/meson.build b/contrib/test_decoding/meson.build
index f643dc81a2..718bf1b2d9 100644
--- a/contrib/test_decoding/meson.build
+++ b/contrib/test_decoding/meson.build
@@ -41,6 +41,7 @@ tests += {
'stats',
'twophase',
'twophase_stream',
+ 'generated_columns',
],
'regress_args': [
'--temp-config', files('logical.conf'),
diff --git a/contrib/test_decoding/sql/generated_columns.sql b/contrib/test_decoding/sql/generated_columns.sql
new file mode 100644
index 0000000000..6d6d1d6564
--- /dev/null
+++ b/contrib/test_decoding/sql/generated_columns.sql
@@ -0,0 +1,22 @@
+-- test decoding of generated columns
+
+SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
+
+-- column b' is a generated column
+CREATE TABLE gencoltable (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+
+-- when 'include-generated-columns' is not set the generated column 'b' values will be replicated
+INSERT INTO gencoltable (a) VALUES (1), (2), (3);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+
+-- when 'include-generated-columns' = '1' the generated column 'b' values will be replicated
+INSERT INTO gencoltable (a) VALUES (4), (5), (6);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '1');
+
+-- when 'include-generated-columns' = '0' the generated column 'b' values will not be replicated
+INSERT INTO gencoltable (a) VALUES (7), (8), (9);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '0');
+
+DROP TABLE gencoltable;
+
+SELECT 'stop' FROM pg_drop_replication_slot('regression_slot');
\ No newline at end of file
diff --git a/contrib/test_decoding/test_decoding.c b/contrib/test_decoding/test_decoding.c
index 7c50d13969..eaa3dbf9db 100644
--- a/contrib/test_decoding/test_decoding.c
+++ b/contrib/test_decoding/test_decoding.c
@@ -31,6 +31,7 @@ typedef struct
bool include_timestamp;
bool skip_empty_xacts;
bool only_local;
+ bool include_generated_columns;
} TestDecodingData;
/*
@@ -168,6 +169,7 @@ pg_decode_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt,
data->include_timestamp = false;
data->skip_empty_xacts = false;
data->only_local = false;
+ data->include_generated_columns = true;
ctx->output_plugin_private = data;
@@ -259,6 +261,16 @@ pg_decode_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt,
errmsg("could not parse value \"%s\" for parameter \"%s\"",
strVal(elem->arg), elem->defname)));
}
+ else if (strcmp(elem->defname, "include-generated-columns") == 0)
+ {
+ if (elem->arg == NULL)
+ data->include_generated_columns = true;
+ else if (!parse_bool(strVal(elem->arg), &data->include_generated_columns))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not parse value \"%s\" for parameter \"%s\"",
+ strVal(elem->arg), elem->defname));
+ }
else
{
ereport(ERROR,
@@ -521,7 +533,8 @@ print_literal(StringInfo s, Oid typid, char *outputstr)
/* print the tuple 'tuple' into the StringInfo s */
static void
-tuple_to_stringinfo(StringInfo s, TupleDesc tupdesc, HeapTuple tuple, bool skip_nulls)
+tuple_to_stringinfo(StringInfo s, TupleDesc tupdesc, HeapTuple tuple,
+ bool skip_nulls, bool include_generated_columns)
{
int natt;
@@ -544,6 +557,9 @@ tuple_to_stringinfo(StringInfo s, TupleDesc tupdesc, HeapTuple tuple, bool skip_
if (attr->attisdropped)
continue;
+ if (attr->attgenerated && !include_generated_columns)
+ continue;
+
/*
* Don't print system columns, oid will already have been printed if
* present.
@@ -641,7 +657,7 @@ pg_decode_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
else
tuple_to_stringinfo(ctx->out, tupdesc,
change->data.tp.newtuple,
- false);
+ false, data->include_generated_columns);
break;
case REORDER_BUFFER_CHANGE_UPDATE:
appendStringInfoString(ctx->out, " UPDATE:");
@@ -650,7 +666,7 @@ pg_decode_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
appendStringInfoString(ctx->out, " old-key:");
tuple_to_stringinfo(ctx->out, tupdesc,
change->data.tp.oldtuple,
- true);
+ true, data->include_generated_columns);
appendStringInfoString(ctx->out, " new-tuple:");
}
@@ -659,7 +675,7 @@ pg_decode_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
else
tuple_to_stringinfo(ctx->out, tupdesc,
change->data.tp.newtuple,
- false);
+ false, data->include_generated_columns);
break;
case REORDER_BUFFER_CHANGE_DELETE:
appendStringInfoString(ctx->out, " DELETE:");
@@ -671,7 +687,7 @@ pg_decode_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
else
tuple_to_stringinfo(ctx->out, tupdesc,
change->data.tp.oldtuple,
- true);
+ true, data->include_generated_columns);
break;
default:
Assert(false);
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 626d35514c..dced1b5026 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -514,8 +514,10 @@ CREATE TABLE people (
</listitem>
<listitem>
<para>
- Generated columns are skipped for logical replication and cannot be
- specified in a <command>CREATE PUBLICATION</command> column list.
+ Generated columns may be skipped during logical replication according to the
+ <command>CREATE SUBSCRIPTION</command> option
+ <link linkend="sql-createsubscription-params-with-include-generated-columns">
+ <literal>include_generated_columns</literal></link>.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 79cd599692..3320c25a60 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3322,6 +3322,17 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>include_generated_columns</term>
+ <listitem>
+ <para>
+ Boolean option to enable generated columns. This option controls
+ whether generated columns should be included in the string
+ representation of tuples during logical decoding in PostgreSQL.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
origin
@@ -6540,8 +6551,10 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
</variablelist>
<para>
- Next, the following message part appears for each column included in
- the publication (except generated columns):
+ Next, the following message parts appear for each column included in
+ the publication (generated columns are excluded unless the parameter
+ <link linkend="protocol-logical-replication-params">
+ <literal>include_generated_columns</literal></link> specifies otherwise):
</para>
<variablelist>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 740b7d9421..ee27a5873a 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -428,6 +428,26 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
</para>
</listitem>
</varlistentry>
+
+ <varlistentry id="sql-createsubscription-params-with-include-generated-columns">
+ <term><literal>include_generated_columns</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Specifies whether the generated columns present in the tables
+ associated with the subscription should be replicated.
+ The default is <literal>false</literal>.
+ </para>
+ <para>
+ If the subscriber-side column is also a generated column then this option
+ has no effect; the subscriber column will be filled as normal with the
+ subscriber-side computed or default data.
+ </para>
+ <para>
+ This parameter can only be set <literal>true</literal> if <literal>copy_data</literal> is
+ set to <literal>false</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist></para>
</listitem>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 0602398a54..f611148472 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -506,7 +506,6 @@ publication_translate_columns(Relation targetrel, List *columns,
Bitmapset *set = NULL;
ListCell *lc;
int n = 0;
- TupleDesc tupdesc = RelationGetDescr(targetrel);
/* Bail out when no column list defined. */
if (!columns)
@@ -534,12 +533,6 @@ publication_translate_columns(Relation targetrel, List *columns,
errmsg("cannot use system column \"%s\" in publication column list",
colname));
- if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
- errmsg("cannot use generated column \"%s\" in publication column list",
- colname));
-
if (bms_is_member(attnum, set))
ereport(ERROR,
errcode(ERRCODE_DUPLICATE_OBJECT),
@@ -1232,7 +1225,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
continue;
attnums[nattnums++] = att->attnum;
diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 9efc9159f2..3803ce5459 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -72,6 +72,7 @@ GetSubscription(Oid subid, bool missing_ok)
sub->passwordrequired = subform->subpasswordrequired;
sub->runasowner = subform->subrunasowner;
sub->failover = subform->subfailover;
+ sub->includegencols = subform->subincludegencols;
/* Get conninfo */
datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index d124bfe55c..819a124c63 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -73,6 +73,7 @@
#define SUBOPT_FAILOVER 0x00002000
#define SUBOPT_LSN 0x00004000
#define SUBOPT_ORIGIN 0x00008000
+#define SUBOPT_INCLUDE_GENERATED_COLUMNS 0x00010000
/* check if the 'val' has 'bits' set */
#define IsSet(val, bits) (((val) & (bits)) == (bits))
@@ -100,6 +101,7 @@ typedef struct SubOpts
bool failover;
char *origin;
XLogRecPtr lsn;
+ bool include_generated_columns;
} SubOpts;
static List *fetch_table_list(WalReceiverConn *wrconn, List *publications);
@@ -164,6 +166,8 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
opts->failover = false;
if (IsSet(supported_opts, SUBOPT_ORIGIN))
opts->origin = pstrdup(LOGICALREP_ORIGIN_ANY);
+ if (IsSet(supported_opts, SUBOPT_INCLUDE_GENERATED_COLUMNS))
+ opts->include_generated_columns = false;
/* Parse options */
foreach(lc, stmt_options)
@@ -357,6 +361,15 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
opts->specified_opts |= SUBOPT_LSN;
opts->lsn = lsn;
}
+ else if (IsSet(supported_opts, SUBOPT_INCLUDE_GENERATED_COLUMNS) &&
+ strcmp(defel->defname, "include_generated_columns") == 0)
+ {
+ if (IsSet(opts->specified_opts, SUBOPT_INCLUDE_GENERATED_COLUMNS))
+ errorConflictingDefElem(defel, pstate);
+
+ opts->specified_opts |= SUBOPT_INCLUDE_GENERATED_COLUMNS;
+ opts->include_generated_columns = defGetBoolean(defel);
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -437,6 +450,20 @@ parse_subscription_options(ParseState *pstate, List *stmt_options,
"slot_name = NONE", "create_slot = false")));
}
}
+
+ /*
+ * Do additional checking for disallowed combination when copy_data and
+ * include_generated_columns are true. COPY of generated columns is not
+ * supported yet.
+ */
+ if (opts->copy_data && opts->include_generated_columns)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ /*- translator: both %s are strings of the form "option = value" */
+ errmsg("%s and %s are mutually exclusive options",
+ "copy_data = true", "include_generated_columns = true"));
+ }
}
/*
@@ -594,7 +621,8 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
SUBOPT_SYNCHRONOUS_COMMIT | SUBOPT_BINARY |
SUBOPT_STREAMING | SUBOPT_TWOPHASE_COMMIT |
SUBOPT_DISABLE_ON_ERR | SUBOPT_PASSWORD_REQUIRED |
- SUBOPT_RUN_AS_OWNER | SUBOPT_FAILOVER | SUBOPT_ORIGIN);
+ SUBOPT_RUN_AS_OWNER | SUBOPT_FAILOVER | SUBOPT_ORIGIN |
+ SUBOPT_INCLUDE_GENERATED_COLUMNS);
parse_subscription_options(pstate, stmt->options, supported_opts, &opts);
/*
@@ -714,6 +742,7 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
publicationListToArray(publications);
values[Anum_pg_subscription_suborigin - 1] =
CStringGetTextDatum(opts.origin);
+ values[Anum_pg_subscription_subincludegencols - 1] = BoolGetDatum(opts.include_generated_columns);
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 97f957cd87..dc317b501a 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -598,6 +598,10 @@ libpqrcv_startstreaming(WalReceiverConn *conn,
appendStringInfo(&cmd, ", origin '%s'",
options->proto.logical.origin);
+ if (options->proto.logical.include_generated_columns &&
+ PQserverVersion(conn->streamConn) >= 180000)
+ appendStringInfoString(&cmd, ", include_generated_columns 'true'");
+
pubnames = options->proto.logical.publication_names;
pubnames_str = stringlist_to_identifierstr(conn->streamConn, pubnames);
if (!pubnames_str)
diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c
index 980f6e2741..e694baca0a 100644
--- a/src/backend/replication/logical/proto.c
+++ b/src/backend/replication/logical/proto.c
@@ -30,10 +30,12 @@
#define TRUNCATE_RESTART_SEQS (1<<1)
static void logicalrep_write_attrs(StringInfo out, Relation rel,
- Bitmapset *columns);
+ Bitmapset *columns,
+ bool include_generated_columns);
static void logicalrep_write_tuple(StringInfo out, Relation rel,
TupleTableSlot *slot,
- bool binary, Bitmapset *columns);
+ bool binary, Bitmapset *columns,
+ bool include_generated_columns);
static void logicalrep_read_attrs(StringInfo in, LogicalRepRelation *rel);
static void logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple);
@@ -412,7 +414,8 @@ logicalrep_read_origin(StringInfo in, XLogRecPtr *origin_lsn)
*/
void
logicalrep_write_insert(StringInfo out, TransactionId xid, Relation rel,
- TupleTableSlot *newslot, bool binary, Bitmapset *columns)
+ TupleTableSlot *newslot, bool binary, Bitmapset *columns,
+ bool include_generated_columns)
{
pq_sendbyte(out, LOGICAL_REP_MSG_INSERT);
@@ -424,7 +427,8 @@ logicalrep_write_insert(StringInfo out, TransactionId xid, Relation rel,
pq_sendint32(out, RelationGetRelid(rel));
pq_sendbyte(out, 'N'); /* new tuple follows */
- logicalrep_write_tuple(out, rel, newslot, binary, columns);
+ logicalrep_write_tuple(out, rel, newslot, binary, columns,
+ include_generated_columns);
}
/*
@@ -457,7 +461,8 @@ logicalrep_read_insert(StringInfo in, LogicalRepTupleData *newtup)
void
logicalrep_write_update(StringInfo out, TransactionId xid, Relation rel,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
- bool binary, Bitmapset *columns)
+ bool binary, Bitmapset *columns,
+ bool include_generated_columns)
{
pq_sendbyte(out, LOGICAL_REP_MSG_UPDATE);
@@ -478,11 +483,13 @@ logicalrep_write_update(StringInfo out, TransactionId xid, Relation rel,
pq_sendbyte(out, 'O'); /* old tuple follows */
else
pq_sendbyte(out, 'K'); /* old key follows */
- logicalrep_write_tuple(out, rel, oldslot, binary, columns);
+ logicalrep_write_tuple(out, rel, oldslot, binary, columns,
+ include_generated_columns);
}
pq_sendbyte(out, 'N'); /* new tuple follows */
- logicalrep_write_tuple(out, rel, newslot, binary, columns);
+ logicalrep_write_tuple(out, rel, newslot, binary, columns,
+ include_generated_columns);
}
/*
@@ -532,7 +539,7 @@ logicalrep_read_update(StringInfo in, bool *has_oldtuple,
void
logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel,
TupleTableSlot *oldslot, bool binary,
- Bitmapset *columns)
+ Bitmapset *columns, bool include_generated_columns)
{
Assert(rel->rd_rel->relreplident == REPLICA_IDENTITY_DEFAULT ||
rel->rd_rel->relreplident == REPLICA_IDENTITY_FULL ||
@@ -552,7 +559,8 @@ logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel,
else
pq_sendbyte(out, 'K'); /* old key follows */
- logicalrep_write_tuple(out, rel, oldslot, binary, columns);
+ logicalrep_write_tuple(out, rel, oldslot, binary, columns,
+ include_generated_columns);
}
/*
@@ -668,7 +676,7 @@ logicalrep_write_message(StringInfo out, TransactionId xid, XLogRecPtr lsn,
*/
void
logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
- Bitmapset *columns)
+ Bitmapset *columns, bool include_generated_columns)
{
char *relname;
@@ -690,7 +698,7 @@ logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
pq_sendbyte(out, rel->rd_rel->relreplident);
/* send the attribute info */
- logicalrep_write_attrs(out, rel, columns);
+ logicalrep_write_attrs(out, rel, columns, include_generated_columns);
}
/*
@@ -767,7 +775,8 @@ logicalrep_read_typ(StringInfo in, LogicalRepTyp *ltyp)
*/
static void
logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot,
- bool binary, Bitmapset *columns)
+ bool binary, Bitmapset *columns,
+ bool include_generated_columns)
{
TupleDesc desc;
Datum *values;
@@ -781,7 +790,10 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot,
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !include_generated_columns)
continue;
if (!column_in_column_list(att->attnum, columns))
@@ -802,7 +814,10 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot,
Form_pg_type typclass;
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !include_generated_columns)
continue;
if (!column_in_column_list(att->attnum, columns))
@@ -923,7 +938,8 @@ logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple)
* Write relation attribute metadata to the stream.
*/
static void
-logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns)
+logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns,
+ bool include_generated_columns)
{
TupleDesc desc;
int i;
@@ -938,7 +954,10 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !include_generated_columns)
continue;
if (!column_in_column_list(att->attnum, columns))
@@ -959,7 +978,10 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns)
Form_pg_attribute att = TupleDescAttr(desc, i);
uint8 flags = 0;
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !include_generated_columns)
continue;
if (!column_in_column_list(att->attnum, columns))
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index f139e7b01e..5de1531567 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -421,7 +421,7 @@ logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE lockmode)
int attnum;
Form_pg_attribute attr = TupleDescAttr(desc, i);
- if (attr->attisdropped || attr->attgenerated)
+ if (attr->attisdropped)
{
entry->attrmap->attnums[i] = -1;
continue;
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 6dc54c7283..f40f61ed7a 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -4376,6 +4376,7 @@ set_stream_options(WalRcvStreamOptions *options,
options->proto.logical.twophase = false;
options->proto.logical.origin = pstrdup(MySubscription->origin);
+ options->proto.logical.include_generated_columns = MySubscription->includegencols;
}
/*
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index abef4eaf68..4624649cd7 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -86,7 +86,8 @@ static void publication_invalidation_cb(Datum arg, int cacheid,
uint32 hashvalue);
static void send_relation_and_attrs(Relation relation, TransactionId xid,
LogicalDecodingContext *ctx,
- Bitmapset *columns);
+ Bitmapset *columns,
+ bool include_generated_columns);
static void send_repl_origin(LogicalDecodingContext *ctx,
RepOriginId origin_id, XLogRecPtr origin_lsn,
bool send_origin);
@@ -283,11 +284,13 @@ parse_output_parameters(List *options, PGOutputData *data)
bool streaming_given = false;
bool two_phase_option_given = false;
bool origin_option_given = false;
+ bool include_generated_columns_option_given = false;
data->binary = false;
data->streaming = LOGICALREP_STREAM_OFF;
data->messages = false;
data->two_phase = false;
+ data->include_generated_columns = false;
foreach(lc, options)
{
@@ -396,6 +399,16 @@ parse_output_parameters(List *options, PGOutputData *data)
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized origin value: \"%s\"", origin));
}
+ else if (strcmp(defel->defname, "include_generated_columns") == 0)
+ {
+ if (include_generated_columns_option_given)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"));
+ include_generated_columns_option_given = true;
+
+ data->include_generated_columns = defGetBoolean(defel);
+ }
else
elog(ERROR, "unrecognized pgoutput option: %s", defel->defname);
}
@@ -731,11 +744,13 @@ maybe_send_schema(LogicalDecodingContext *ctx,
{
Relation ancestor = RelationIdGetRelation(relentry->publish_as_relid);
- send_relation_and_attrs(ancestor, xid, ctx, relentry->columns);
+ send_relation_and_attrs(ancestor, xid, ctx, relentry->columns,
+ data->include_generated_columns);
RelationClose(ancestor);
}
- send_relation_and_attrs(relation, xid, ctx, relentry->columns);
+ send_relation_and_attrs(relation, xid, ctx, relentry->columns,
+ data->include_generated_columns);
if (data->in_streaming)
set_schema_sent_in_streamed_txn(relentry, topxid);
@@ -749,7 +764,7 @@ maybe_send_schema(LogicalDecodingContext *ctx,
static void
send_relation_and_attrs(Relation relation, TransactionId xid,
LogicalDecodingContext *ctx,
- Bitmapset *columns)
+ Bitmapset *columns, bool include_generated_columns)
{
TupleDesc desc = RelationGetDescr(relation);
int i;
@@ -766,7 +781,10 @@ send_relation_and_attrs(Relation relation, TransactionId xid,
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !include_generated_columns)
continue;
if (att->atttypid < FirstGenbkiObjectId)
@@ -782,7 +800,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid,
}
OutputPluginPrepareWrite(ctx, false);
- logicalrep_write_rel(ctx->out, xid, relation, columns);
+ logicalrep_write_rel(ctx->out, xid, relation, columns, include_generated_columns);
OutputPluginWrite(ctx, false);
}
@@ -1085,7 +1103,7 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
continue;
nliveatts++;
@@ -1531,15 +1549,18 @@ pgoutput_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
{
case REORDER_BUFFER_CHANGE_INSERT:
logicalrep_write_insert(ctx->out, xid, targetrel, new_slot,
- data->binary, relentry->columns);
+ data->binary, relentry->columns,
+ data->include_generated_columns);
break;
case REORDER_BUFFER_CHANGE_UPDATE:
logicalrep_write_update(ctx->out, xid, targetrel, old_slot,
- new_slot, data->binary, relentry->columns);
+ new_slot, data->binary, relentry->columns,
+ data->include_generated_columns);
break;
case REORDER_BUFFER_CHANGE_DELETE:
logicalrep_write_delete(ctx->out, xid, targetrel, old_slot,
- data->binary, relentry->columns);
+ data->binary, relentry->columns,
+ data->include_generated_columns);
break;
default:
Assert(false);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 79190470f7..106f313fb1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4800,6 +4800,7 @@ getSubscriptions(Archive *fout)
int i_suboriginremotelsn;
int i_subenabled;
int i_subfailover;
+ int i_subincludegencols;
int i,
ntups;
@@ -4872,11 +4873,17 @@ getSubscriptions(Archive *fout)
if (fout->remoteVersion >= 170000)
appendPQExpBufferStr(query,
- " s.subfailover\n");
+ " s.subfailover,\n");
else
appendPQExpBuffer(query,
- " false AS subfailover\n");
+ " false AS subfailover,\n");
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBufferStr(query,
+ " s.subincludegencols\n");
+ else
+ appendPQExpBufferStr(query,
+ " false AS subincludegencols\n");
appendPQExpBufferStr(query,
"FROM pg_subscription s\n");
@@ -4915,6 +4922,7 @@ getSubscriptions(Archive *fout)
i_suboriginremotelsn = PQfnumber(res, "suboriginremotelsn");
i_subenabled = PQfnumber(res, "subenabled");
i_subfailover = PQfnumber(res, "subfailover");
+ i_subincludegencols = PQfnumber(res, "subincludegencols");
subinfo = pg_malloc(ntups * sizeof(SubscriptionInfo));
@@ -4961,6 +4969,8 @@ getSubscriptions(Archive *fout)
pg_strdup(PQgetvalue(res, i, i_subenabled));
subinfo[i].subfailover =
pg_strdup(PQgetvalue(res, i, i_subfailover));
+ subinfo[i].subincludegencols =
+ pg_strdup(PQgetvalue(res, i, i_subincludegencols));
/* Decide whether we want to dump it */
selectDumpableObject(&(subinfo[i].dobj), fout);
@@ -5207,6 +5217,9 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo)
if (pg_strcasecmp(subinfo->suborigin, LOGICALREP_ORIGIN_ANY) != 0)
appendPQExpBuffer(query, ", origin = %s", subinfo->suborigin);
+ if (strcmp(subinfo->subincludegencols, "t") == 0)
+ appendPQExpBufferStr(query, ", include_generated_columns = true");
+
appendPQExpBufferStr(query, ");\n");
/*
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 4b2e5870a9..28752ade7e 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -671,6 +671,7 @@ typedef struct _SubscriptionInfo
char *suborigin;
char *suboriginremotelsn;
char *subfailover;
+ char *subincludegencols;
} SubscriptionInfo;
/*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 7c9a1f234c..2e8e70d4d6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6539,7 +6539,7 @@ describeSubscriptions(const char *pattern, bool verbose)
printQueryOpt myopt = pset.popt;
static const bool translate_columns[] = {false, false, false, false,
false, false, false, false, false, false, false, false, false, false,
- false};
+ false, false};
if (pset.sversion < 100000)
{
@@ -6608,6 +6608,12 @@ describeSubscriptions(const char *pattern, bool verbose)
", subfailover AS \"%s\"\n",
gettext_noop("Failover"));
+ /* include_generated_columns is only supported in v18 and higher */
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf,
+ ", subincludegencols AS \"%s\"\n",
+ gettext_noop("Include generated columns"));
+
appendPQExpBuffer(&buf,
", subsynccommit AS \"%s\"\n"
", subconninfo AS \"%s\"\n",
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 024469474d..3c7e563807 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3357,7 +3357,8 @@ psql_completion(const char *text, int start, int end)
/* Complete "CREATE SUBSCRIPTION <name> ... WITH ( <opt>" */
else if (HeadMatches("CREATE", "SUBSCRIPTION") && TailMatches("WITH", "("))
COMPLETE_WITH("binary", "connect", "copy_data", "create_slot",
- "disable_on_error", "enabled", "failover", "origin",
+ "disable_on_error", "enabled", "failover",
+ "include_generated_columns", "origin",
"password_required", "run_as_owner", "slot_name",
"streaming", "synchronous_commit", "two_phase");
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index 0aa14ec4a2..37e6dd9898 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -98,6 +98,9 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW
* slots) in the upstream database are enabled
* to be synchronized to the standbys. */
+ bool subincludegencols; /* True if generated columns should be
+ * published */
+
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/* Connection string to the publisher */
text subconninfo BKI_FORCE_NOT_NULL;
@@ -157,6 +160,7 @@ typedef struct Subscription
List *publications; /* List of publication names to subscribe to */
char *origin; /* Only publish data originating from the
* specified origin */
+ bool includegencols; /* Publish generated columns */
} Subscription;
/* Disallow streaming in-progress transactions. */
diff --git a/src/include/replication/logicalproto.h b/src/include/replication/logicalproto.h
index c409638a2e..34ec40b07e 100644
--- a/src/include/replication/logicalproto.h
+++ b/src/include/replication/logicalproto.h
@@ -225,18 +225,22 @@ extern char *logicalrep_read_origin(StringInfo in, XLogRecPtr *origin_lsn);
extern void logicalrep_write_insert(StringInfo out, TransactionId xid,
Relation rel,
TupleTableSlot *newslot,
- bool binary, Bitmapset *columns);
+ bool binary, Bitmapset *columns,
+ bool include_generated_columns);
extern LogicalRepRelId logicalrep_read_insert(StringInfo in, LogicalRepTupleData *newtup);
extern void logicalrep_write_update(StringInfo out, TransactionId xid,
Relation rel,
TupleTableSlot *oldslot,
- TupleTableSlot *newslot, bool binary, Bitmapset *columns);
+ TupleTableSlot *newslot, bool binary,
+ Bitmapset *columns,
+ bool include_generated_columns);
extern LogicalRepRelId logicalrep_read_update(StringInfo in,
bool *has_oldtuple, LogicalRepTupleData *oldtup,
LogicalRepTupleData *newtup);
extern void logicalrep_write_delete(StringInfo out, TransactionId xid,
Relation rel, TupleTableSlot *oldslot,
- bool binary, Bitmapset *columns);
+ bool binary, Bitmapset *columns,
+ bool include_generated_columns);
extern LogicalRepRelId logicalrep_read_delete(StringInfo in,
LogicalRepTupleData *oldtup);
extern void logicalrep_write_truncate(StringInfo out, TransactionId xid,
@@ -247,7 +251,8 @@ extern List *logicalrep_read_truncate(StringInfo in,
extern void logicalrep_write_message(StringInfo out, TransactionId xid, XLogRecPtr lsn,
bool transactional, const char *prefix, Size sz, const char *message);
extern void logicalrep_write_rel(StringInfo out, TransactionId xid,
- Relation rel, Bitmapset *columns);
+ Relation rel, Bitmapset *columns,
+ bool include_generated_columns);
extern LogicalRepRelation *logicalrep_read_rel(StringInfo in);
extern void logicalrep_write_typ(StringInfo out, TransactionId xid,
Oid typoid);
diff --git a/src/include/replication/pgoutput.h b/src/include/replication/pgoutput.h
index 89f94e1147..224394cb93 100644
--- a/src/include/replication/pgoutput.h
+++ b/src/include/replication/pgoutput.h
@@ -33,6 +33,7 @@ typedef struct PGOutputData
bool messages;
bool two_phase;
bool publish_no_origin;
+ bool include_generated_columns;
} PGOutputData;
#endif /* PGOUTPUT_H */
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index 132e789948..93b46fb01f 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -186,6 +186,8 @@ typedef struct
* prepare time */
char *origin; /* Only publish data originating from the
* specified origin */
+ bool include_generated_columns; /* Publish generated
+ * columns */
} logical;
} proto;
} WalRcvStreamOptions;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 30b6371134..aa1450315d 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -687,9 +687,9 @@ UPDATE testpub_tbl5 SET a = 1;
ERROR: cannot update table "testpub_tbl5"
DETAIL: Column list used by the publication does not cover the replica identity.
ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
--- error: generated column "d" can't be in list
+-- ok: generated columns can be in the list too
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d);
-ERROR: cannot use generated column "d" in publication column list
+ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
-- error: system attributes "ctid" not allowed in column list
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid);
ERROR: cannot use system column "ctid" in publication column list
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 17d48b1685..3e08be39b7 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -99,6 +99,11 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
ERROR: subscription with slot_name = NONE must also set create_slot = false
CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
ERROR: subscription with slot_name = NONE must also set enabled = false
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (include_generated_columns = true, copy_data = true);
+ERROR: copy_data = true and include_generated_columns = true are mutually exclusive options
+-- fail - include_generated_columns must be boolean
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, include_generated_columns = foo);
+ERROR: include_generated_columns requires a Boolean value
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
WARNING: subscription was created, but is not connected
@@ -116,18 +121,18 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+ regress_testsub4
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
-------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub4 SET (origin = any);
\dRs+ regress_testsub4
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
-------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
DROP SUBSCRIPTION regress_testsub3;
@@ -145,10 +150,10 @@ ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
@@ -157,10 +162,10 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
ALTER SUBSCRIPTION regress_testsub SET (password_required = false);
ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+------------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | f | f | off | dbname=regress_doesnotexist2 | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (password_required = true);
@@ -176,10 +181,10 @@ ERROR: unrecognized subscription parameter: "create_slot"
-- ok
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345');
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+------------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist2 | 0/12345
(1 row)
-- ok - with lsn = NONE
@@ -188,10 +193,10 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE);
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0');
ERROR: invalid WAL location (LSN): 0/0
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+------------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist2 | 0/0
(1 row)
BEGIN;
@@ -223,10 +228,10 @@ ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar);
ERROR: invalid value for parameter "synchronous_commit": "foobar"
HINT: Available values: local, remote_write, remote_apply, on, off.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
----------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+----------
- regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+------------------------------+----------
+ regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | f | local | dbname=regress_doesnotexist2 | 0/0
(1 row)
-- rename back to keep the rest simple
@@ -255,19 +260,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (binary = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
DROP SUBSCRIPTION regress_testsub;
@@ -279,27 +284,27 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (streaming = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
-- fail - publication already exists
@@ -314,10 +319,10 @@ ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refr
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false);
ERROR: publication "testpub1" is already in subscription "regress_testsub"
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
-- fail - publication used more than once
@@ -332,10 +337,10 @@ ERROR: publication "testpub3" is not in subscription "regress_testsub"
-- ok - delete publications
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
DROP SUBSCRIPTION regress_testsub;
@@ -371,19 +376,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
-- we can alter streaming when two_phase enabled
ALTER SUBSCRIPTION regress_testsub SET (streaming = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -393,10 +398,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -409,18 +414,18 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+----------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0
+ List of subscriptions
+ Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Include generated columns | Synchronous commit | Conninfo | Skip LSN
+-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+---------------------------+--------------------+-----------------------------+----------
+ regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | f | f | off | dbname=regress_doesnotexist | 0/0
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 479d4f3264..b1899ddb1a 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -413,8 +413,9 @@ ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, x);
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (b, c);
UPDATE testpub_tbl5 SET a = 1;
ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
--- error: generated column "d" can't be in list
+-- ok: generated columns can be in the list too
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d);
+ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5;
-- error: system attributes "ctid" not allowed in column list
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid);
-- ok
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 007c9e7037..7f7057d1b4 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -59,6 +59,10 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE);
CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false);
CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (include_generated_columns = true, copy_data = true);
+
+-- fail - include_generated_columns must be boolean
+CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, include_generated_columns = foo);
-- ok - with slot_name = NONE
CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl
index 8b2e5f4708..13499a155d 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -12,12 +12,30 @@ use Test::More;
my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->append_conf(
+ 'postgresql.conf',
+ "max_wal_senders = 20
+ max_replication_slots = 20");
$node_publisher->start;
+# All subscribers on this node will use parameter include_generated_columns = false
my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
$node_subscriber->init;
+$node_subscriber->append_conf(
+ 'postgresql.conf',
+ "max_logical_replication_workers = 20
+ max_worker_processes = 20");
$node_subscriber->start;
+# All subscribers on this node will use parameter include_generated_columns = true
+my $node_subscriber2 = PostgreSQL::Test::Cluster->new('subscriber2');
+$node_subscriber2->init;
+$node_subscriber2->append_conf(
+ 'postgresql.conf',
+ "max_logical_replication_workers = 20
+ max_worker_processes = 20");
+$node_subscriber2->start;
+
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
$node_publisher->safe_psql('postgres',
@@ -28,32 +46,255 @@ $node_subscriber->safe_psql('postgres',
"CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) STORED, c int)"
);
+# tab_gen_to_gen:
+# publisher-side has generated col 'b'.
+# subscriber-side has generated col 'b', with different computation.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_gen_to_nogen:
+# publisher-side has generated col 'b'.
+# subscriber-side has non-generated col 'b'.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_nogen (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_nogen (a int, b int)");
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_nogen (a int, b int)");
+
+# tab_gen_to_missing:
+# publisher-side has generated col 'b'.
+# subscriber-side col 'b' is missing.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_missing (a int, b int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_missing (a int)");
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_missing (a int)");
+
+# tab_missing_to_gen:
+# publisher-side col 'b' is missing.
+# subscriber-side has generated col 'b'.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_missing_to_gen (a int)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_nogen_to_gen:
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_nogen_to_gen (a int, b int)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
+# tab_order:
+# publisher-side has generated cols 'b' and 'c'.
+# subscriber-side has non-generated col 'b', and generated-col 'c'.
+# columns on publisher/subscriber are in a different order
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_order (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_order (c int GENERATED ALWAYS AS (a * 22) STORED, a int, b int)"
+);
+
+# tab_alter:
+# for testing ALTER SUBSCRIPTION ... REFRESH PUBLICATION
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_alter (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 2) STORED)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_alter (a int, b int, c int GENERATED ALWAYS AS (a * 22) STORED)"
+);
+
# data for initial sync
$node_publisher->safe_psql('postgres',
"INSERT INTO tab1 (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION pub1 FOR ALL TABLES");
+ "INSERT INTO tab_gen_to_gen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_gen_to_nogen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_gen_to_missing (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_missing_to_gen (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_nogen_to_gen (a, b) VALUES (1, 1), (2, 2), (3, 3)");
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_order (a) VALUES (1), (2), (3)");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_alter (a) VALUES (1), (2), (3)");
+
+# create publications
+#
+# pub_combo_gen_to_missing is not included in pub_combo, because some tests give errors.
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_tab1 FOR TABLE tab1");
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_gen, tab_gen_to_nogen, tab_missing_to_gen"
+);
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_combo_gen_to_missing FOR TABLE tab_gen_to_missing"
+);
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_combo_nogen_to_gen FOR TABLE tab_nogen_to_gen"
+);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_misc FOR TABLE tab_order");
+
+# create subscriptions
+#
+# Note that all subscriptions created on node_subscriber2 use copy_data = false,
+# because copy_data = true with include_generated_columns is not yet supported.
+# For this reason, the expected inital data on node_subscriber2 is always empty.
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_tab1 CONNECTION '$publisher_connstr' PUBLICATION regress_pub_tab1"
+);
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_combo CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo"
+);
$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+ "CREATE SUBSCRIPTION regress_sub1_combo_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing"
);
+# Note, regress_sub1_combo_nogen_to_gen is not created here due to expected errors. See later.
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_combo CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo WITH (include_generated_columns = true, copy_data = false)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_combo_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing with (include_generated_columns = true, copy_data = false)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH (include_generated_columns = true, copy_data = false)"
+);
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_misc CONNECTION '$publisher_connstr' PUBLICATION regress_pub_misc WITH (include_generated_columns = true, copy_data = false)"
+);
+
+#####################
# Wait for initial sync of all subscriptions
+#####################
+
$node_subscriber->wait_for_subscription_sync;
+$node_subscriber2->wait_for_subscription_sync;
my $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab1");
is( $result, qq(1|22
2|44
3|66), 'generated columns initial sync');
+#####################
+# TEST tab_gen_to_gen initial sync
+#####################
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen");
+is( $result, qq(1|22
+2|44
+3|66), 'tab_gen_to_gen initial sync, when include_generated_columns=false');
+$result =
+ $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen");
+is($result, qq(),
+ 'tab_gen_to_gen initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_gen_to_nogen initial sync
+#####################
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_nogen");
+is( $result, qq(1|
+2|
+3|), 'tab_gen_to_nogen, when include_generated_columns=false');
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_nogen");
+is($result, qq(),
+ 'tab_gen_to_nogen initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_gen_to_missing initial sync
+#####################
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT a FROM tab_gen_to_missing");
+is( $result, qq(1
+2
+3), 'tab_gen_to_missing initial sync, when include_generated_columns=false');
+# Note, the following is expected to work only because copy_data = false
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a FROM tab_gen_to_missing");
+is($result, qq(),
+ 'tab_gen_to_missing initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_missing_to_gen initial sync
+#####################
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_missing_to_gen");
+is( $result, qq(1|22
+2|44
+3|66), 'tab_missing_to_gen initial sync, when include_generated_columns=false'
+);
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_missing_to_gen");
+is($result, qq(),
+ 'tab_missing_to_gen initial sync, when include_generated_columns=true');
+
+#####################
+# TEST tab_nogen_to_gen initial sync
+#####################
+# The subscription is created here, because it causes the tablesync worker to restart repetitively.
+my $offset = -s $node_subscriber->logfile;
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_combo_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH (include_generated_columns = false)"
+);
+$node_subscriber->wait_for_log(
+ qr/ERROR: ( [A-Z0-9]:)? column "b" is a generated column/, $offset);
+# Note, the following is expected to work only because copy_data = false
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_nogen_to_gen");
+is($result, qq(),
+ 'tab_nogen_to_gen initial sync, when include_generated_columns=true');
+
+# tab_order:
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b, c FROM tab_order ORDER BY a");
+is($result, qq(), 'generated column initial sync');
+
+# tab_alter:
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b, c FROM tab_alter ORDER BY a");
+is($result, qq(), 'unsubscribed table initial data');
+
# data to replicate
$node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (4), (5)");
$node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 6 WHERE a = 5");
-$node_publisher->wait_for_catchup('sub1');
+$node_publisher->wait_for_catchup('regress_sub1_tab1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1");
is( $result, qq(1|22|
@@ -62,6 +303,288 @@ is( $result, qq(1|22|
4|88|
6|132|), 'generated columns replicated');
+#####################
+# TEST tab_gen_to_gen replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side has generated col 'b', using a different computation.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_gen_to_gen VALUES (4), (5)");
+
+# regress_sub1_combo: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+#$node_publisher->wait_for_catchup('regress_pub_combo');
+$node_publisher->wait_for_catchup('regress_sub1_combo');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_gen ORDER BY a");
+is( $result, qq(1|22
+2|44
+3|66
+4|88
+5|110),
+ 'confirm generated columns are not replicated when include_generated_columns=false'
+);
+
+# regress_sub2_combo: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated. We can know this because the result
+# value is the subscriber-side computation (which is different from the
+# publisher-side computation for this column).
+$node_publisher->wait_for_catchup('regress_sub2_combo');
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_gen ORDER BY a");
+is( $result, qq(4|88
+5|110),
+ 'confirm generated columns are not replicated when the subscriber-side column is also generated'
+);
+
+#####################
+# TEST tab_gen_to_nogen replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side has non-generated col 'b'.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_gen_to_nogen VALUES (4), (5)");
+
+# regress_sub1_combo: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+$node_publisher->wait_for_catchup('regress_sub1_combo');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_nogen ORDER BY a");
+is( $result, qq(1|
+2|
+3|
+4|
+5|),
+ 'confirm generated columns are not replicated when the subscriber-side column is not generated'
+);
+
+# regress_sub2_combo: (include_generated_columns = true)
+# Confirm that col 'b' is replicated.
+$node_publisher->wait_for_catchup('regress_sub2_combo');
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_gen_to_nogen ORDER BY a");
+is( $result, qq(4|8
+5|10),
+ 'confirm generated columns are replicated when the subscriber-side column is not generated'
+);
+
+#####################
+# TEST tab_gen_to_missing replication
+#
+# publisher-side has generated col 'b'.
+# subscriber-side col 'b' is missing.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_gen_to_missing VALUES (4), (5)");
+
+# regress_sub1_combo_gen_to_missing: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated.
+$node_publisher->wait_for_catchup('regress_sub1_combo_gen_to_missing');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT a FROM tab_gen_to_missing ORDER BY a");
+is( $result, qq(1
+2
+3
+4
+5),
+ 'missing generated column, include_generated_columns = false');
+
+# regress_sub2_combo_gen_to_missing: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated and it will throw an error.
+my $offset2 = -s $node_subscriber2->logfile;
+$node_subscriber2->wait_for_log(
+ qr/ERROR: ( [A-Z0-9]+:)? logical replication target relation "public.tab_gen_to_missing" is missing replicated column: "b"/,
+ $offset2);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub1_combo_gen_to_missing");
+$node_subscriber2->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub2_combo_gen_to_missing");
+$node_publisher->safe_psql('postgres',
+ "DROP PUBLICATION regress_pub_combo_gen_to_missing");
+
+#####################
+# TEST tab_missing_to_gen replication
+#
+# publisher-side col 'b' is missing.
+# subscriber-side col 'b' is generated.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_missing_to_gen VALUES (4), (5)");
+
+# regress_sub1_combo: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated, but is generated as normal
+$node_publisher->wait_for_catchup('regress_sub1_combo');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_missing_to_gen ORDER BY a");
+is( $result, qq(1|22
+2|44
+3|66
+4|88
+5|110),
+ 'confirm when publisher col is missing, subscriber generated columns are generated as normal'
+);
+
+# regress_sub2_combo: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated, but is generated as normal
+$node_publisher->wait_for_catchup('regress_sub2_combo');
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_missing_to_gen ORDER BY a");
+is( $result, qq(4|88
+5|110),
+ 'confirm when publisher col is missing, subscriber generated columns are generated as normal'
+);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub1_combo");
+$node_subscriber2->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub2_combo");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_combo");
+
+#####################
+# TEST tab_nogen_to_gen replication
+#
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
+#####################
+
+# When copy_data=true a COPY error occurred. Try again but with copy_data=false.
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_combo_nogen_to_gen_nocopy CONNECTION '$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH (include_generated_columns = false, copy_data = false)"
+);
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_nogen_to_gen VALUES (4), (5)");
+
+# regress_sub1_combo_nogen_to_gen: (include_generated_columns = false)
+#
+# XXX
+# The test below shows that current PG17 behavior does not give an error,
+# But this conflicts with the copy_data=true behavior so it might be a PG17 bug.
+# Needs more study.
+$node_publisher->wait_for_catchup('regress_sub1_combo_nogen_to_gen_nocopy');
+$result =
+ $node_subscriber->safe_psql('postgres',
+ "SELECT a, b FROM tab_nogen_to_gen ORDER BY a");
+is( $result, qq(4|88
+5|110),
+ 'confirm when publisher col is not generated, subscriber generated columns are generated as normal'
+);
+
+# regress_sub2_combo_nogen_to_gen: (include_generated_columns = true)
+# When copy_data=false, no COPY error occurs.
+# The col 'b' is not replicated; the subscriber-side generated value is inserted.
+#
+# XXX
+# It is correct for this to give the same result as above, but it needs more
+# study to determine if the above result was actually correct, or a PG17 bug.
+$node_publisher->wait_for_catchup('regress_sub2_combo_nogen_to_gen');
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b FROM tab_nogen_to_gen ORDER BY a");
+is( $result, qq(4|88
+5|110),
+ 'confirm when publisher col is not generated, subscriber generated columns are generated as normal'
+);
+
+# cleanup
+$node_subscriber->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub1_combo_nogen_to_gen_nocopy");
+$node_subscriber2->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub2_combo_nogen_to_gen");
+$node_publisher->safe_psql('postgres',
+ "DROP PUBLICATION regress_pub_combo_nogen_to_gen");
+
+#####################
+# TEST tab_order replication
+#
+# publisher-side cols 'b' and 'c' are generated
+# subscriber-side col 'b' is not generated and col 'c' is generated.
+# But pub/sub table cols are in different order.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_order VALUES (4), (5)");
+
+# regress_sub2_misc: (include_generated_columns = true)
+# Confirm that depsite different orders replication occurs to the correct columns
+$node_publisher->wait_for_catchup('regress_sub2_misc');
+$result =
+ $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b, c FROM tab_order ORDER BY a");
+is( $result, qq(4|8|88
+5|10|110),
+ 'replicate generated columns with different order on the subscriber');
+
+#####################
+# TEST tab_alter replication
+#
+# Add a new table to existing publication, then
+# do ALTER SUBSCRIPTION ... REFRESH PUBLICATION
+#####################
+
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION regress_pub_misc ADD TABLE tab_alter");
+$node_subscriber2->safe_psql('postgres',
+ "ALTER SUBSCRIPTION regress_sub2_misc REFRESH PUBLICATION");
+$node_publisher->wait_for_catchup('regress_sub2_misc');
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b, c FROM tab_alter ORDER BY a");
+is( $result, qq(1||22
+2||44
+3||66), 'add new table to existing publication');
+
+#####################
+# TEST tab_alter
+#
+# Drop the generated column's expression on subscriber side.
+# This changes the generated column into a non-generated column.
+#####################
+
+# change a gencol to a nogen col
+$node_subscriber2->safe_psql('postgres',
+ "ALTER TABLE tab_alter ALTER COLUMN c DROP EXPRESSION");
+
+# insert some data
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_alter (a) VALUES (4), (5)");
+
+# confirm that replication now works for the subscriber nogen col
+$result = $node_subscriber2->safe_psql('postgres',
+ "SELECT a, b, c FROM tab_alter ORDER BY a");
+is( $result, qq(1||22
+2||44
+3||66
+4|8|8
+5|10|10), 'after drop generated column expression');
+
+# cleanup
+$node_subscriber2->safe_psql('postgres',
+ "DROP SUBSCRIPTION regress_sub2_misc");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_misc");
+
+#####################
# try it with a subscriber-side trigger
$node_subscriber->safe_psql(
@@ -84,7 +607,7 @@ $node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (7), (8)");
$node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 9 WHERE a = 7");
-$node_publisher->wait_for_catchup('sub1');
+$node_publisher->wait_for_catchup('regress_sub1_tab1');
$result =
$node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY 1");
diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl
index 9a97fa5020..3bb2301b43 100644
--- a/src/test/subscription/t/031_column_list.pl
+++ b/src/test/subscription/t/031_column_list.pl
@@ -1202,16 +1202,16 @@ $result = $node_publisher->safe_psql(
is( $result, qq(t
t), 'check the number of columns in the old tuple');
-# TEST: Generated and dropped columns are not considered for the column list.
+# TEST: Dropped columns are not considered for the column list.
# So, the publication having a column list except for those columns and a
-# publication without any column (aka all columns as part of the columns
+# publication without any column list (aka all columns as part of the column
# list) are considered to have the same column list.
$node_publisher->safe_psql(
'postgres', qq(
CREATE TABLE test_mix_4 (a int PRIMARY KEY, b int, c int, d int GENERATED ALWAYS AS (a + 1) STORED);
ALTER TABLE test_mix_4 DROP COLUMN c;
- CREATE PUBLICATION pub_mix_7 FOR TABLE test_mix_4 (a, b);
+ CREATE PUBLICATION pub_mix_7 FOR TABLE test_mix_4 (a, b, d);
CREATE PUBLICATION pub_mix_8 FOR TABLE test_mix_4;
-- initial data
--
2.41.0.windows.3