v30-0001-Enable-support-for-publish_generated_columns-opt.patch
application/octet-stream
Filename: v30-0001-Enable-support-for-publish_generated_columns-opt.patch
Type: application/octet-stream
Part: 1
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 v30-0001
Subject: Enable support for 'publish_generated_columns' option.
| File | + | − |
|---|---|---|
| contrib/test_decoding/expected/generated_columns.out | 60 | 0 |
| contrib/test_decoding/Makefile | 2 | 1 |
| contrib/test_decoding/meson.build | 1 | 0 |
| contrib/test_decoding/sql/generated_columns.sql | 28 | 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_publication.sgml | 21 | 0 |
| src/backend/catalog/pg_publication.c | 2 | 8 |
| src/backend/commands/publicationcmds.c | 31 | 3 |
| src/backend/replication/logical/proto.c | 4 | 4 |
| src/backend/replication/pgoutput/pgoutput.c | 70 | 29 |
| src/bin/pg_dump/pg_dump.c | 14 | 1 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/psql/describe.c | 16 | 2 |
| src/bin/psql/tab-complete.c | 1 | 1 |
| src/include/catalog/pg_publication.h | 4 | 0 |
| src/test/regress/expected/psql.out | 3 | 3 |
| src/test/regress/expected/publication.out | 237 | 212 |
| src/test/regress/sql/publication.sql | 15 | 1 |
| src/test/subscription/t/031_column_list.pl | 3 | 3 |
From 3ef7303f1ef01c9f9c1562f5b3ba0f5696289054 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Fri, 23 Aug 2024 10:03:38 +1000
Subject: [PATCH v30 1/2] Enable support for 'publish_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 'publish_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 'publish_generated_columns' is false, generated columns are not
replicated, even when present in a PUBLICATION col-list.
Example usage for test_decoding plugin:
SELECT data FROM pg_logical_slot_get_changes('slot1', NULL, NULL,
'include-xids', '0','skip-empty-xacts', '1',
'include-generated-columns','1');
Example usage of subscription option:
CREATE PUBLICATION FOR TABLE tab_gencol WITH (publish_generated_columns
= true);
---
contrib/test_decoding/Makefile | 3 +-
.../expected/generated_columns.out | 60 +++
contrib/test_decoding/meson.build | 1 +
.../test_decoding/sql/generated_columns.sql | 28 ++
contrib/test_decoding/test_decoding.c | 26 +-
doc/src/sgml/ddl.sgml | 6 +-
doc/src/sgml/protocol.sgml | 17 +-
doc/src/sgml/ref/create_publication.sgml | 21 +
src/backend/catalog/pg_publication.c | 10 +-
src/backend/commands/publicationcmds.c | 34 +-
src/backend/replication/logical/proto.c | 8 +-
src/backend/replication/pgoutput/pgoutput.c | 99 ++--
src/bin/pg_dump/pg_dump.c | 15 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/describe.c | 18 +-
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_publication.h | 4 +
src/test/regress/expected/psql.out | 6 +-
src/test/regress/expected/publication.out | 449 +++++++++---------
src/test/regress/sql/publication.sql | 16 +-
src/test/subscription/t/031_column_list.pl | 6 +-
21 files changed, 553 insertions(+), 277 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..d6a402c4bf
--- /dev/null
+++ b/contrib/test_decoding/expected/generated_columns.out
@@ -0,0 +1,60 @@
+-- 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);
+-- For 'test_decoding' the parameter 'include-generated-columns' is enabled by default,
+-- so the values for the generated column 'b' will be replicated even when the parameter is not specified.
+INSERT INTO gencoltable (a) VALUES (1);
+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
+ COMMIT
+(3 rows)
+
+-- When 'include-generated-columns' is enabled, the values of the generated column 'b' will be replicated.
+INSERT INTO gencoltable (a) VALUES (2);
+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]:2 b[integer]:4
+ COMMIT
+(3 rows)
+
+-- When 'include-generated-columns' is disabled, the values of the generated column 'b' will not be replicated.
+INSERT INTO gencoltable (a) VALUES (3);
+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]:3
+ COMMIT
+(3 rows)
+
+-- When REPLICA IDENTITY is FULL, the old-key data includes the generated columns data for updates.
+ALTER TABLE gencoltable REPLICA IDENTITY FULL;
+UPDATE gencoltable SET a = 10;
+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: UPDATE: old-key: a[integer]:1 b[integer]:2 new-tuple: a[integer]:10 b[integer]:20
+ table public.gencoltable: UPDATE: old-key: a[integer]:2 b[integer]:4 new-tuple: a[integer]:10 b[integer]:20
+ table public.gencoltable: UPDATE: old-key: a[integer]:3 b[integer]:6 new-tuple: a[integer]:10 b[integer]:20
+ 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..be04db49e4
--- /dev/null
+++ b/contrib/test_decoding/sql/generated_columns.sql
@@ -0,0 +1,28 @@
+-- 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);
+
+-- For 'test_decoding' the parameter 'include-generated-columns' is enabled by default,
+-- so the values for the generated column 'b' will be replicated even when the parameter is not specified.
+INSERT INTO gencoltable (a) VALUES (1);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+
+-- When 'include-generated-columns' is enabled, the values of the generated column 'b' will be replicated.
+INSERT INTO gencoltable (a) VALUES (2);
+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' is disabled, the values of the generated column 'b' will not be replicated.
+INSERT INTO gencoltable (a) VALUES (3);
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '0');
+
+-- When REPLICA IDENTITY is FULL, the old-key data includes the generated columns data for updates.
+ALTER TABLE gencoltable REPLICA IDENTITY FULL;
+UPDATE gencoltable SET a = 10;
+SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1', 'include-generated-columns', '1');
+
+DROP TABLE gencoltable;
+
+SELECT 'stop' FROM pg_drop_replication_slot('regression_slot');
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 b671858627..d5681c96a2 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 PUBLICATION</command> option
+ <link linkend="sql-createpublication-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 11b6456779..2765fa3629 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3324,6 +3324,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
@@ -6542,8 +6553,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_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fd9c5deac9..ac375922df 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -222,6 +222,27 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry id="sql-createpublication-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 publication should be replicated.
+ The default is <literal>false</literal>.
+ </para>
+ <para>
+ If the publisher-side column is also a generated column then this option
+ has no effect; the publisher column will be filled as normal with the
+ publisher-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>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 7fe5fe2b86..4b2048b40e 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -511,7 +511,6 @@ pub_collist_validate(Relation targetrel, List *columns)
{
Bitmapset *set = NULL;
ListCell *lc;
- TupleDesc tupdesc = RelationGetDescr(targetrel);
foreach(lc, columns)
{
@@ -530,12 +529,6 @@ pub_collist_validate(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),
@@ -1006,6 +999,7 @@ GetPublication(Oid pubid)
pub->pubactions.pubdelete = pubform->pubdelete;
pub->pubactions.pubtruncate = pubform->pubtruncate;
pub->pubviaroot = pubform->pubviaroot;
+ pub->pubgencolumns = pubform->pubgencolumns;
ReleaseSysCache(tup);
@@ -1214,7 +1208,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/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index d6ffef374e..6242a094de 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -78,12 +78,15 @@ parse_publication_options(ParseState *pstate,
bool *publish_given,
PublicationActions *pubactions,
bool *publish_via_partition_root_given,
- bool *publish_via_partition_root)
+ bool *publish_via_partition_root,
+ bool *publish_generated_columns_given,
+ bool *publish_generated_columns)
{
ListCell *lc;
*publish_given = false;
*publish_via_partition_root_given = false;
+ *publish_generated_columns_given = false;
/* defaults */
pubactions->pubinsert = true;
@@ -91,6 +94,7 @@ parse_publication_options(ParseState *pstate,
pubactions->pubdelete = true;
pubactions->pubtruncate = true;
*publish_via_partition_root = false;
+ *publish_generated_columns = false;
/* Parse options */
foreach(lc, options)
@@ -151,6 +155,13 @@ parse_publication_options(ParseState *pstate,
*publish_via_partition_root_given = true;
*publish_via_partition_root = defGetBoolean(defel);
}
+ else if (strcmp(defel->defname, "publish_generated_columns") == 0)
+ {
+ if (*publish_generated_columns_given)
+ errorConflictingDefElem(defel, pstate);
+ *publish_generated_columns_given = true;
+ *publish_generated_columns = defGetBoolean(defel);
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -737,6 +748,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
PublicationActions pubactions;
bool publish_via_partition_root_given;
bool publish_via_partition_root;
+ bool publish_generated_columns_given;
+ bool publish_generated_columns;
AclResult aclresult;
List *relations = NIL;
List *schemaidlist = NIL;
@@ -776,7 +789,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
stmt->options,
&publish_given, &pubactions,
&publish_via_partition_root_given,
- &publish_via_partition_root);
+ &publish_via_partition_root,
+ &publish_generated_columns_given,
+ &publish_generated_columns);
puboid = GetNewOidWithIndex(rel, PublicationObjectIndexId,
Anum_pg_publication_oid);
@@ -793,6 +808,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
BoolGetDatum(pubactions.pubtruncate);
values[Anum_pg_publication_pubviaroot - 1] =
BoolGetDatum(publish_via_partition_root);
+ values[Anum_pg_publication_pubgencolumns - 1] =
+ BoolGetDatum(publish_generated_columns);
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
@@ -878,6 +895,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
PublicationActions pubactions;
bool publish_via_partition_root_given;
bool publish_via_partition_root;
+ bool publish_generated_columns_given;
+ bool publish_generated_columns;
ObjectAddress obj;
Form_pg_publication pubform;
List *root_relids = NIL;
@@ -887,7 +906,9 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
stmt->options,
&publish_given, &pubactions,
&publish_via_partition_root_given,
- &publish_via_partition_root);
+ &publish_via_partition_root,
+ &publish_generated_columns_given,
+ &publish_generated_columns);
pubform = (Form_pg_publication) GETSTRUCT(tup);
@@ -997,6 +1018,13 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
replaces[Anum_pg_publication_pubviaroot - 1] = true;
}
+
+ if (publish_generated_columns_given)
+ {
+ values[Anum_pg_publication_pubgencolumns - 1] = BoolGetDatum(publish_generated_columns);
+ replaces[Anum_pg_publication_pubgencolumns - 1] = true;
+ }
+
tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
replaces);
diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c
index 980f6e2741..6b085e555c 100644
--- a/src/backend/replication/logical/proto.c
+++ b/src/backend/replication/logical/proto.c
@@ -781,7 +781,7 @@ 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 (!column_in_column_list(att->attnum, columns))
@@ -802,7 +802,7 @@ 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 (!column_in_column_list(att->attnum, columns))
@@ -938,7 +938,7 @@ 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 (!column_in_column_list(att->attnum, columns))
@@ -959,7 +959,7 @@ 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 (!column_in_column_list(att->attnum, columns))
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 00e7024563..394043837d 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -766,7 +766,7 @@ 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->atttypid < FirstGenbkiObjectId)
@@ -1008,6 +1008,36 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications,
}
}
+/*
+ * Prepare new column list bitmap. This includes all the columns of the table.
+ */
+static Bitmapset *
+prepare_all_columns_bms(PGOutputData *data, RelationSyncEntry *entry,
+ TupleDesc desc)
+{
+ Bitmapset *cols = NULL;
+ MemoryContext oldcxt = NULL;
+
+ pgoutput_ensure_entry_cxt(data, entry);
+ oldcxt = MemoryContextSwitchTo(entry->entry_cxt);
+
+ for (int i = 0; i < desc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(desc, i);
+
+ /* Skip if the attribute is dropped */
+ if (att->attisdropped)
+ continue;
+
+ /* Iterate the cols until generated columns are found. */
+ cols = bms_add_member(cols, i + 1);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+
+ return cols;
+}
+
/*
* Initialize the column list.
*/
@@ -1042,11 +1072,11 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
Bitmapset *cols = NULL;
/*
- * If the publication is FOR ALL TABLES then it is treated the same as
- * if there are no column lists (even if other publications have a
- * list).
+ * If the publication is FOR ALL TABLES and include generated columns
+ * then it is treated the same as if there are no column lists (even
+ * if other publications have a list).
*/
- if (!pub->alltables)
+ if (!pub->alltables || !pub->pubgencolumns)
{
bool pub_no_list = true;
@@ -1067,43 +1097,54 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
Anum_pg_publication_rel_prattrs,
&pub_no_list);
+ }
- /* Build the column list bitmap in the per-entry context. */
- if (!pub_no_list) /* when not null */
- {
- int i;
- int nliveatts = 0;
- TupleDesc desc = RelationGetDescr(relation);
+ /* Build the column list bitmap in the per-entry context. */
+ if (!pub_no_list || !pub->pubgencolumns) /* when not null */
+ {
+ int i;
+ int nliveatts = 0;
+ TupleDesc desc = RelationGetDescr(relation);
- pgoutput_ensure_entry_cxt(data, entry);
+ pgoutput_ensure_entry_cxt(data, entry);
+ if (!pub_no_list)
cols = pub_collist_to_bitmapset(cols, cfdatum,
entry->entry_cxt);
+ else
+ cols = prepare_all_columns_bms(data, entry, desc);
- /* Get the number of live attributes. */
- for (i = 0; i < desc->natts; i++)
- {
- Form_pg_attribute att = TupleDescAttr(desc, i);
+ /* Get the number of live attributes. */
+ for (i = 0; i < desc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
- continue;
-
- nliveatts++;
- }
+ if (att->attisdropped)
+ continue;
/*
- * If column list includes all the columns of the table,
- * set it to NULL.
+ * If column list contain generated column it will not
+ * replicate the table to the subscriber port.
*/
- if (bms_num_members(cols) == nliveatts)
- {
- bms_free(cols);
- cols = NULL;
- }
+ if (att->attgenerated && !pub->pubgencolumns)
+ cols = bms_del_member(cols, i + 1);
+
+ nliveatts++;
}
- ReleaseSysCache(cftuple);
+ /*
+ * If column list includes all the columns of the table, set
+ * it to NULL.
+ */
+ if (bms_num_members(cols) == nliveatts)
+ {
+ bms_free(cols);
+ cols = NULL;
+ }
}
+
+ if (HeapTupleIsValid(cftuple))
+ ReleaseSysCache(cftuple);
}
if (first)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 546e7e4ce1..06fda226ac 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4282,6 +4282,7 @@ getPublications(Archive *fout)
int i_pubdelete;
int i_pubtruncate;
int i_pubviaroot;
+ int i_pubgencolumns;
int i,
ntups;
@@ -4293,7 +4294,13 @@ getPublications(Archive *fout)
resetPQExpBuffer(query);
/* Get the publications. */
- if (fout->remoteVersion >= 130000)
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBufferStr(query,
+ "SELECT p.tableoid, p.oid, p.pubname, "
+ "p.pubowner, "
+ "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot, p.pubgencolumns "
+ "FROM pg_publication p");
+ else if (fout->remoteVersion >= 130000)
appendPQExpBufferStr(query,
"SELECT p.tableoid, p.oid, p.pubname, "
"p.pubowner, "
@@ -4326,6 +4333,7 @@ getPublications(Archive *fout)
i_pubdelete = PQfnumber(res, "pubdelete");
i_pubtruncate = PQfnumber(res, "pubtruncate");
i_pubviaroot = PQfnumber(res, "pubviaroot");
+ i_pubgencolumns = PQfnumber(res, "pubgencolumns");
pubinfo = pg_malloc(ntups * sizeof(PublicationInfo));
@@ -4350,6 +4358,8 @@ getPublications(Archive *fout)
(strcmp(PQgetvalue(res, i, i_pubtruncate), "t") == 0);
pubinfo[i].pubviaroot =
(strcmp(PQgetvalue(res, i, i_pubviaroot), "t") == 0);
+ pubinfo[i].pubgencolumns =
+ (strcmp(PQgetvalue(res, i, i_pubgencolumns), "t") == 0);
/* Decide whether we want to dump it */
selectDumpableObject(&(pubinfo[i].dobj), fout);
@@ -4429,6 +4439,9 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
if (pubinfo->pubviaroot)
appendPQExpBufferStr(query, ", publish_via_partition_root = true");
+ if (pubinfo->pubgencolumns)
+ appendPQExpBufferStr(query, ", publish_generated_columns = true");
+
appendPQExpBufferStr(query, ");\n");
if (pubinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 0b7d21b2e9..de9783cd77 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -625,6 +625,7 @@ typedef struct _PublicationInfo
bool pubdelete;
bool pubtruncate;
bool pubviaroot;
+ bool pubgencolumns;
} PublicationInfo;
/*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 7c9a1f234c..983962b6b9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6229,7 +6229,7 @@ listPublications(const char *pattern)
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
- static const bool translate_columns[] = {false, false, false, false, false, false, false, false};
+ static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false};
if (pset.sversion < 100000)
{
@@ -6264,7 +6264,10 @@ listPublications(const char *pattern)
appendPQExpBuffer(&buf,
",\n pubviaroot AS \"%s\"",
gettext_noop("Via root"));
-
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf,
+ ",\n pubgencolumns AS \"%s\"",
+ gettext_noop("Generated columns"));
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_publication\n");
@@ -6353,6 +6356,7 @@ describePublications(const char *pattern)
PGresult *res;
bool has_pubtruncate;
bool has_pubviaroot;
+ bool has_pubgencol;
PQExpBufferData title;
printTableContent cont;
@@ -6369,6 +6373,7 @@ describePublications(const char *pattern)
has_pubtruncate = (pset.sversion >= 110000);
has_pubviaroot = (pset.sversion >= 130000);
+ has_pubgencol = (pset.sversion >= 180000);
initPQExpBuffer(&buf);
@@ -6382,6 +6387,9 @@ describePublications(const char *pattern)
if (has_pubviaroot)
appendPQExpBufferStr(&buf,
", pubviaroot");
+ if (has_pubgencol)
+ appendPQExpBufferStr(&buf,
+ ", pubgencolumns");
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_publication\n");
@@ -6433,6 +6441,8 @@ describePublications(const char *pattern)
ncols++;
if (has_pubviaroot)
ncols++;
+ if (has_pubgencol)
+ ncols++;
initPQExpBuffer(&title);
printfPQExpBuffer(&title, _("Publication %s"), pubname);
@@ -6447,6 +6457,8 @@ describePublications(const char *pattern)
printTableAddHeader(&cont, gettext_noop("Truncates"), true, align);
if (has_pubviaroot)
printTableAddHeader(&cont, gettext_noop("Via root"), true, align);
+ if (has_pubgencol)
+ printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align);
printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
@@ -6457,6 +6469,8 @@ describePublications(const char *pattern)
printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
if (has_pubviaroot)
printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ if (has_pubgencol)
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
if (!puballtables)
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a7ccde6d7d..ea36b18ea2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3182,7 +3182,7 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("WITH (");
/* Complete "CREATE PUBLICATION <name> [...] WITH" */
else if (HeadMatches("CREATE", "PUBLICATION") && TailMatches("WITH", "("))
- COMPLETE_WITH("publish", "publish_via_partition_root");
+ COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
/* CREATE RULE */
/* Complete "CREATE [ OR REPLACE ] RULE <sth>" with "AS ON" */
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index d9518a58b0..fc85a6474b 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -54,6 +54,9 @@ CATALOG(pg_publication,6104,PublicationRelationId)
/* true if partition changes are published using root schema */
bool pubviaroot;
+
+ /* true if generated columns data should be published */
+ bool pubgencolumns;
} FormData_pg_publication;
/* ----------------
@@ -103,6 +106,7 @@ typedef struct Publication
char *name;
bool alltables;
bool pubviaroot;
+ bool pubgencolumns;
PublicationActions pubactions;
} Publication;
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6aeb7cb963..dd453da47e 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6303,9 +6303,9 @@ List of schemas
(0 rows)
\dRp "no.such.publication"
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+-------+------------+---------+---------+---------+-----------+----------
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+------+-------+------------+---------+---------+---------+-----------+----------+-------------------
(0 rows)
\dRs "no.such.subscription"
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 660245ed0c..ab703e2eab 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -29,21 +29,25 @@ CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publis
ERROR: conflicting or redundant options
LINE 1: ...ub_xxx WITH (publish_via_partition_root = 'true', publish_vi...
^
+CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'true', publish_generated_columns = '0');
+ERROR: conflicting or redundant options
+LINE 1: ...pub_xxx WITH (publish_generated_columns = 'true', publish_ge...
+ ^
\dRp
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------+--------------------------+------------+---------+---------+---------+-----------+----------
- testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f
- testpub_default | regress_publication_user | f | f | t | f | f | f
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f
+ testpub_default | regress_publication_user | f | f | t | f | f | f | f
(2 rows)
ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
\dRp
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------+--------------------------+------------+---------+---------+---------+-----------+----------
- testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f
- testpub_default | regress_publication_user | f | t | t | t | f | f
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f
+ testpub_default | regress_publication_user | f | t | t | t | f | f | f
(2 rows)
--- adding tables
@@ -87,10 +91,10 @@ RESET client_min_messages;
-- should be able to add schema to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_tbl1"
Tables from schemas:
@@ -99,20 +103,20 @@ Tables from schemas:
-- should be able to drop schema from 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_tbl1"
-- should be able to set schema to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test"
@@ -123,10 +127,10 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
RESET client_min_messages;
\dRp+ testpub_for_tbl_schema
- Publication testpub_for_tbl_schema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_for_tbl_schema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test.testpub_nopk"
Tables from schemas:
@@ -144,10 +148,10 @@ LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo;
-- should be able to add a table of the same schema to the schema publication
ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test.testpub_nopk"
Tables from schemas:
@@ -156,10 +160,10 @@ Tables from schemas:
-- should be able to drop the table
ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test"
@@ -170,10 +174,10 @@ ERROR: relation "testpub_nopk" is not part of the publication
-- should be able to set table to schema publication
ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test.testpub_nopk"
@@ -195,10 +199,10 @@ Publications:
"testpub_foralltables"
\dRp+ testpub_foralltables
- Publication testpub_foralltables
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | t | t | t | f | f | f
+ Publication testpub_foralltables
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | t | t | t | f | f | f | f
(1 row)
DROP TABLE testpub_tbl2;
@@ -210,19 +214,19 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
RESET client_min_messages;
\dRp+ testpub3
- Publication testpub3
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub3
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_tbl3"
"public.testpub_tbl3a"
\dRp+ testpub4
- Publication testpub4
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub4
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_tbl3"
@@ -243,10 +247,10 @@ UPDATE testpub_parted1 SET a = 1;
-- only parent is listed as being in publication, not the partition
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
\dRp+ testpub_forparted
- Publication testpub_forparted
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_forparted
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_parted"
@@ -261,10 +265,10 @@ ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1;
UPDATE testpub_parted1 SET a = 1;
ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true);
\dRp+ testpub_forparted
- Publication testpub_forparted
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | t
+ Publication testpub_forparted
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | t | f
Tables:
"public.testpub_parted"
@@ -293,10 +297,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub5
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
@@ -309,10 +313,10 @@ Tables:
ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000);
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub5
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
@@ -328,10 +332,10 @@ Publications:
ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2;
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub5
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))
@@ -339,10 +343,10 @@ Tables:
-- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression)
ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500);
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub5
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500))
@@ -375,10 +379,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub_syntax1
- Publication testpub_syntax1
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub_syntax1
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl3" WHERE (e < 999)
@@ -388,10 +392,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub_syntax2
- Publication testpub_syntax2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | f | f
+ Publication testpub_syntax2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | f | f | f
Tables:
"public.testpub_rf_tbl1"
"testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999)
@@ -506,10 +510,10 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
\dRp+ testpub6
- Publication testpub6
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub6
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99)
Tables from schemas:
@@ -687,9 +691,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
@@ -730,10 +734,10 @@ CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate');
RESET client_min_messages;
ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok
\dRp+ testpub_table_ins
- Publication testpub_table_ins
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | f | f | t | f
+ Publication testpub_table_ins
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | f | f | t | f | f
Tables:
"public.testpub_tbl5" (a)
@@ -917,10 +921,10 @@ CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c));
ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey;
ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1);
\dRp+ testpub_both_filters
- Publication testpub_both_filters
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_both_filters
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1)
@@ -1125,10 +1129,10 @@ ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" already exists
\dRp+ testpub_fortbl
- Publication testpub_fortbl
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortbl
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
@@ -1166,10 +1170,10 @@ Publications:
"testpub_fortbl"
\dRp+ testpub_default
- Publication testpub_default
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | f | f
+ Publication testpub_default
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | f | f | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
@@ -1247,10 +1251,10 @@ REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
DROP TABLE testpub_parted;
DROP TABLE testpub_tbl1;
\dRp+ testpub_default
- Publication testpub_default
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | f | f
+ Publication testpub_default
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | f | f | f
(1 row)
-- fail - must be owner of publication
@@ -1260,20 +1264,20 @@ ERROR: must be owner of publication testpub_default
RESET ROLE;
ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
\dRp testpub_foo
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
--------------+--------------------------+------------+---------+---------+---------+-----------+----------
- testpub_foo | regress_publication_user | f | t | t | t | f | f
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+-------------+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ testpub_foo | regress_publication_user | f | t | t | t | f | f | f
(1 row)
-- rename back to keep the rest simple
ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
------------------+---------------------------+------------+---------+---------+---------+-----------+----------
- testpub_default | regress_publication_user2 | f | t | t | t | f | f
+ List of publications
+ Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+-----------------+---------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ testpub_default | regress_publication_user2 | f | t | t | t | f | f | f
(1 row)
-- adding schemas and tables
@@ -1289,19 +1293,19 @@ CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1315,44 +1319,44 @@ CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CUR
CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA";
RESET client_min_messages;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub3_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"public"
\dRp+ testpub4_forschema
- Publication testpub4_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub4_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"CURRENT_SCHEMA"
\dRp+ testpub5_forschema
- Publication testpub5_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub5_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"CURRENT_SCHEMA"
"public"
\dRp+ testpub6_forschema
- Publication testpub6_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub6_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"CURRENT_SCHEMA"
"public"
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"CURRENT_SCHEMA.CURRENT_SCHEMA"
@@ -1386,10 +1390,10 @@ ERROR: schema "testpub_view" does not exist
-- dropping the schema should reflect the change in publication
DROP SCHEMA pub_test3;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1397,20 +1401,20 @@ Tables from schemas:
-- renaming the schema should reflect the change in publication
ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1_renamed"
"pub_test2"
ALTER SCHEMA pub_test1_renamed RENAME to pub_test1;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1418,10 +1422,10 @@ Tables from schemas:
-- alter publication add schema
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1430,10 +1434,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1442,10 +1446,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1;
ERROR: schema "pub_test1" is already member of publication "testpub1_forschema"
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1453,10 +1457,10 @@ Tables from schemas:
-- alter publication drop schema
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
@@ -1464,10 +1468,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
ERROR: tables from schema "pub_test2" are not part of the publication
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
@@ -1475,29 +1479,29 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
-- drop all schemas
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
(1 row)
-- alter publication set multiple schema
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1506,10 +1510,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1518,10 +1522,10 @@ Tables from schemas:
-- removing the duplicate schemas
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
@@ -1600,18 +1604,18 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub3_forschema;
RESET client_min_messages;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub3_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
(1 row)
ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub3_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables from schemas:
"pub_test1"
@@ -1621,20 +1625,20 @@ CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TA
CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1;
RESET client_min_messages;
\dRp+ testpub_forschema_fortable
- Publication testpub_forschema_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_forschema_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test2.tbl1"
Tables from schemas:
"pub_test1"
\dRp+ testpub_fortable_forschema
- Publication testpub_fortable_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------------------------+------------+---------+---------+---------+-----------+----------
- regress_publication_user | f | t | t | t | t | f
+ Publication testpub_fortable_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
Tables:
"pub_test2.tbl1"
Tables from schemas:
@@ -1749,6 +1753,27 @@ DROP PUBLICATION pub;
DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test the publication with or without 'PUBLISH_GENERATED_COLUMNS' parameter
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION pub1 FOR ALL TABLES WITH (PUBLISH_GENERATED_COLUMNS=1);
+\dRp+ pub1
+ Publication pub1
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | t | t | t | t | t | f | t
+(1 row)
+
+CREATE PUBLICATION pub2 FOR ALL TABLES WITH (PUBLISH_GENERATED_COLUMNS=0);
+\dRp+ pub2
+ Publication pub2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | t | t | t | t | t | f | f
+(1 row)
+
+RESET client_min_messages;
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index f68a5b5986..2673397c17 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -24,6 +24,7 @@ ALTER PUBLICATION testpub_default SET (publish = update);
CREATE PUBLICATION testpub_xxx WITH (foo);
CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0');
+CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'true', publish_generated_columns = '0');
\dRp
@@ -413,8 +414,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);
ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl1 (id, ctid);
@@ -1110,6 +1112,18 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test the publication with or without 'PUBLISH_GENERATED_COLUMNS' parameter
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION pub1 FOR ALL TABLES WITH (PUBLISH_GENERATED_COLUMNS=1);
+\dRp+ pub1
+
+CREATE PUBLICATION pub2 FOR ALL TABLES WITH (PUBLISH_GENERATED_COLUMNS=0);
+\dRp+ pub2
+
+RESET client_min_messages;
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
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.34.1