v34-0001-Enable-support-for-publish_generated_columns-opt.patch
application/octet-stream
Filename: v34-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 v34-0001
Subject: Enable support for 'publish_generated_columns' option.
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 4 | 2 |
| doc/src/sgml/protocol.sgml | 1 | 1 |
| doc/src/sgml/ref/create_publication.sgml | 12 | 0 |
| src/backend/catalog/pg_publication.c | 11 | 6 |
| src/backend/commands/publicationcmds.c | 32 | 4 |
| src/backend/replication/logical/proto.c | 4 | 4 |
| src/backend/replication/pgoutput/pgoutput.c | 68 | 34 |
| src/bin/pg_dump/pg_dump.c | 17 | 4 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/pg_dump/t/002_pg_dump.pl | 10 | 0 |
| src/bin/psql/describe.c | 16 | 2 |
| src/bin/psql/tab-complete.c | 1 | 1 |
| src/include/catalog/pg_publication.h | 6 | 1 |
| src/test/regress/expected/psql.out | 3 | 3 |
| src/test/regress/expected/publication.out | 266 | 212 |
| src/test/regress/sql/publication.sql | 37 | 1 |
| src/test/subscription/t/031_column_list.pl | 2 | 2 |
From 41975a87537549477978bc3df7dcc16f913a0cee 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 v34 1/3] 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 supports the transmission of generated column information and data
alongside regular table changes. This behaviour is controlled by a new
PUBLICATION parameter ('publish_generated_columns').
Example usage of subscription option:
CREATE PUBLICATION FOR TABLE tab_gencol WITH (publish_generated_columns
= true);
When 'publish_generated_columns' is false, generated columns are not replicated.
But when generated columns are specified in PUBLICATION col-list, it is
replicated even the 'publish_generated_columns' is false.
There is a change in 'pg_publicataion' catalog so we need to
bump the catversion.
---
doc/src/sgml/ddl.sgml | 6 +-
doc/src/sgml/protocol.sgml | 2 +-
doc/src/sgml/ref/create_publication.sgml | 12 +
src/backend/catalog/pg_publication.c | 17 +-
src/backend/commands/publicationcmds.c | 36 +-
src/backend/replication/logical/proto.c | 8 +-
src/backend/replication/pgoutput/pgoutput.c | 102 +++--
src/bin/pg_dump/pg_dump.c | 21 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 10 +
src/bin/psql/describe.c | 18 +-
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_publication.h | 7 +-
src/test/regress/expected/psql.out | 6 +-
src/test/regress/expected/publication.out | 478 +++++++++++---------
src/test/regress/sql/publication.sql | 38 +-
src/test/subscription/t/031_column_list.pl | 4 +-
17 files changed, 491 insertions(+), 277 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8ab0ddb112..7b9c349343 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> parameter
+ <link linkend="sql-createpublication-params-with-publish-generated-columns">
+ <literal>publish_generated_columns</literal></link>.
</para>
</listitem>
</itemizedlist>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 11b6456779..56de72c0c6 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6543,7 +6543,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
<para>
Next, the following message part appears for each column included in
- the publication (except generated columns):
+ the publication:
</para>
<variablelist>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fd9c5deac9..cd20bd469c 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -222,6 +222,18 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry id="sql-createpublication-params-with-publish-generated-columns">
+ <term><literal>publish_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>
+ </listitem>
+ </varlistentry>
+
</variablelist></para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 7fe5fe2b86..583da09748 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -420,7 +420,8 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
check_publication_add_relation(targetrel);
/* Validate and translate column names into a Bitmapset of attnums. */
- attnums = pub_collist_validate(pri->relation, pri->columns);
+ attnums = pub_collist_validate(pri->relation, pri->columns,
+ pub->pubgencols);
/* Form a tuple. */
memset(values, 0, sizeof(values));
@@ -507,7 +508,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
* corresponding attnums.
*/
Bitmapset *
-pub_collist_validate(Relation targetrel, List *columns)
+pub_collist_validate(Relation targetrel, List *columns, bool pubgencols)
{
Bitmapset *set = NULL;
ListCell *lc;
@@ -530,10 +531,10 @@ 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,
+ if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated && !pubgencols)
+ ereport(WARNING,
errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
- errmsg("cannot use generated column \"%s\" in publication column list",
+ errmsg("specified generated column \"%s\" in publication column list when publish_generated_columns as false",
colname));
if (bms_is_member(attnum, set))
@@ -1006,6 +1007,7 @@ GetPublication(Oid pubid)
pub->pubactions.pubdelete = pubform->pubdelete;
pub->pubactions.pubtruncate = pubform->pubtruncate;
pub->pubviaroot = pubform->pubviaroot;
+ pub->pubgencols = pubform->pubgencols;
ReleaseSysCache(tup);
@@ -1214,7 +1216,10 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped)
+ continue;
+
+ if (att->attgenerated && !pub->pubgencols)
continue;
attnums[nattnums++] = att->attnum;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index d6ffef374e..8c09125170 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_pubgencols - 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,12 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
replaces[Anum_pg_publication_pubviaroot - 1] = true;
}
+ if (publish_generated_columns_given)
+ {
+ values[Anum_pg_publication_pubgencols - 1] = BoolGetDatum(publish_generated_columns);
+ replaces[Anum_pg_publication_pubgencols - 1] = true;
+ }
+
tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
replaces);
@@ -1182,7 +1209,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
* is cheap enough that that seems harmless.
*/
newcolumns = pub_collist_validate(newpubrel->relation,
- newpubrel->columns);
+ newpubrel->columns,
+ pubform->pubgencols);
/*
* Check if any of the new set of relations matches with the
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..24c56ed894 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 encompasses all table columns, excluding the generated ones.
+ */
+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 or generated */
+ if (att->attisdropped || att->attgenerated)
+ continue;
+
+ cols = bms_add_member(cols, i + 1);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+
+ return cols;
+}
+
/*
* Initialize the column list.
*/
@@ -1042,13 +1072,14 @@ 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).
+ * To handle cases where the publish_generated_columns option isn't
+ * specified for all tables in a publication, the pubgencolumns check
+ * needs to be performed. In such cases, we must create a column list
+ * that excludes generated columns.
*/
- if (!pub->alltables)
+ if (!pub->alltables || !pub->pubgencols)
{
- bool pub_no_list = true;
+ bool pub_rel_has_collist = true;
/*
* Check for the presence of a column list in this publication.
@@ -1066,44 +1097,47 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
/* Lookup the column list attribute. */
cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
Anum_pg_publication_rel_prattrs,
- &pub_no_list);
+ &pub_rel_has_collist);
+ }
- /* 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_rel_has_collist || !pub->pubgencols)
+ {
+ int i;
+ int nliveatts = 0;
+ TupleDesc desc = RelationGetDescr(relation);
- pgoutput_ensure_entry_cxt(data, entry);
+ pgoutput_ensure_entry_cxt(data, entry);
- cols = pub_collist_to_bitmapset(cols, cfdatum,
- entry->entry_cxt);
+ if (!pub_rel_has_collist)
+ 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;
+ if (att->attisdropped)
+ continue;
- nliveatts++;
- }
+ nliveatts++;
+ }
- /*
- * 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 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 130b80775d..a0dad1ebf7 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_pubgencols;
int i,
ntups;
@@ -4293,23 +4294,29 @@ 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.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot, p.pubgencols "
+ "FROM pg_publication p");
+ else if (fout->remoteVersion >= 130000)
+ appendPQExpBufferStr(query,
+ "SELECT p.tableoid, p.oid, p.pubname, "
+ "p.pubowner, "
+ "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot, false AS pubviagencols "
"FROM pg_publication p");
else if (fout->remoteVersion >= 110000)
appendPQExpBufferStr(query,
"SELECT p.tableoid, p.oid, p.pubname, "
"p.pubowner, "
- "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, false AS pubviaroot "
+ "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, false AS pubviaroot, false AS pubviagencols "
"FROM pg_publication p");
else
appendPQExpBufferStr(query,
"SELECT p.tableoid, p.oid, p.pubname, "
"p.pubowner, "
- "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, false AS pubtruncate, false AS pubviaroot "
+ "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, false AS pubtruncate, false AS pubviaroot, false AS pubviagencols "
"FROM pg_publication p");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -4326,6 +4333,7 @@ getPublications(Archive *fout)
i_pubdelete = PQfnumber(res, "pubdelete");
i_pubtruncate = PQfnumber(res, "pubtruncate");
i_pubviaroot = PQfnumber(res, "pubviaroot");
+ i_pubgencols = PQfnumber(res, "pubgencols");
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].pubgencols =
+ (strcmp(PQgetvalue(res, i, i_pubgencols), "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->pubgencols)
+ 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 9f907ed5ad..c1552ead45 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -626,6 +626,7 @@ typedef struct _PublicationInfo
bool pubdelete;
bool pubtruncate;
bool pubviaroot;
+ bool pubgencols;
} PublicationInfo;
/*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index ab6c830491..91a4c63744 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2986,6 +2986,16 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub5' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub5 WITH (publish_generated_columns = true);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub5 WITH (publish = 'insert, update, delete, truncate', publish_generated_columns = true);\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6a36c91083..16cbef3693 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6237,7 +6237,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)
{
@@ -6272,7 +6272,10 @@ listPublications(const char *pattern)
appendPQExpBuffer(&buf,
",\n pubviaroot AS \"%s\"",
gettext_noop("Via root"));
-
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf,
+ ",\n pubgencols AS \"%s\"",
+ gettext_noop("Generated columns"));
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_publication\n");
@@ -6361,6 +6364,7 @@ describePublications(const char *pattern)
PGresult *res;
bool has_pubtruncate;
bool has_pubviaroot;
+ bool has_pubgencols;
PQExpBufferData title;
printTableContent cont;
@@ -6377,6 +6381,7 @@ describePublications(const char *pattern)
has_pubtruncate = (pset.sversion >= 110000);
has_pubviaroot = (pset.sversion >= 130000);
+ has_pubgencols = (pset.sversion >= 180000);
initPQExpBuffer(&buf);
@@ -6390,6 +6395,9 @@ describePublications(const char *pattern)
if (has_pubviaroot)
appendPQExpBufferStr(&buf,
", pubviaroot");
+ if (has_pubgencols)
+ appendPQExpBufferStr(&buf,
+ ", pubgencols");
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_publication\n");
@@ -6441,6 +6449,8 @@ describePublications(const char *pattern)
ncols++;
if (has_pubviaroot)
ncols++;
+ if (has_pubgencols)
+ ncols++;
initPQExpBuffer(&title);
printfPQExpBuffer(&title, _("Publication %s"), pubname);
@@ -6455,6 +6465,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_pubgencols)
+ printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align);
printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
@@ -6465,6 +6477,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_pubgencols)
+ 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..2a3816f661 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 pubgencols;
} FormData_pg_publication;
/* ----------------
@@ -103,6 +106,7 @@ typedef struct Publication
char *name;
bool alltables;
bool pubviaroot;
+ bool pubgencols;
PublicationActions pubactions;
} Publication;
@@ -152,7 +156,8 @@ extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
bool if_not_exists);
-extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
+extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns,
+ bool pubgencols);
extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
bool if_not_exists);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3819bf5e25..62e4820ce9 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6350,9 +6350,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..a26b5a418e 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -29,21 +29,27 @@ 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...
+ ^
+CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'foo');
+ERROR: publish_generated_columns requires a Boolean value
\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 +93,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 +105,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 +129,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 +150,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 +162,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 +176,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 +201,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 +216,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 +249,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 +267,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 +299,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 +315,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 +334,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 +345,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 +381,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 +394,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 +512,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 +693,10 @@ 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
+WARNING: specified generated column "d" in publication column list when publish_generated_columns as false
+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 +737,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 +924,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 +1132,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 +1173,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 +1254,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 +1267,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 +1296,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 +1322,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 +1393,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 +1404,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 +1425,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 +1437,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 +1449,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 +1460,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 +1471,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 +1482,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 +1513,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 +1525,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 +1607,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 +1628,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 +1756,53 @@ DROP PUBLICATION pub;
DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test the publication 'publish_generated_columns' parameter enabled or disabled
+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;
+-- Test the publication 'publish_generated_columns' parameter enabled or disabled for different combinations.
+SET client_min_messages = 'WARNING';
+CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+-- gencols in column list with 'publish_generated_columns'=false.
+CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false);
+WARNING: specified generated column "gen1" in publication column list when publish_generated_columns as false
+WARNING: "wal_level" is insufficient to publish logical changes
+HINT: Set "wal_level" to "logical" before creating subscriptions.
+-- gencols in column list with 'publish_generated_columns'=true.
+CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true);
+WARNING: "wal_level" is insufficient to publish logical changes
+HINT: Set "wal_level" to "logical" before creating subscriptions.
+-- ALTER PUBLICATION setting 'publication_generate_columns'=false.
+ALTER PUBLICATION pub2 SET (publish_generated_columns = false);
+-- No gencols in column list with 'publish_generated_columns'=false.
+CREATE PUBLICATION pub3 WITH (publish_generated_columns=false);
+WARNING: "wal_level" is insufficient to publish logical changes
+HINT: Set "wal_level" to "logical" before creating subscriptions.
+-- ALTER PUBLICATION to add gencols to column list.
+ALTER PUBLICATION pub3 ADD TABLE gencols(a, gen1);
+WARNING: specified generated column "gen1" in publication column list when publish_generated_columns as false
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+DROP PUBLICATION pub3;
+DROP TABLE gencols;
+RESET client_min_messages;
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..d7b43dcf68 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -24,6 +24,8 @@ 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');
+CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'foo');
\dRp
@@ -413,8 +415,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 +1113,39 @@ DROP TABLE sch1.tbl1;
DROP SCHEMA sch1 cascade;
DROP SCHEMA sch2 cascade;
+-- Test the publication 'publish_generated_columns' parameter enabled or disabled
+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;
+
+-- Test the publication 'publish_generated_columns' parameter enabled or disabled for different combinations.
+SET client_min_messages = 'WARNING';
+CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+-- gencols in column list with 'publish_generated_columns'=false.
+CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false);
+-- gencols in column list with 'publish_generated_columns'=true.
+CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true);
+-- ALTER PUBLICATION setting 'publication_generate_columns'=false.
+ALTER PUBLICATION pub2 SET (publish_generated_columns = false);
+
+-- No gencols in column list with 'publish_generated_columns'=false.
+CREATE PUBLICATION pub3 WITH (publish_generated_columns=false);
+-- ALTER PUBLICATION to add gencols to column list.
+ALTER PUBLICATION pub3 ADD TABLE gencols(a, gen1);
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+DROP PUBLICATION pub3;
+DROP TABLE gencols;
+
+RESET client_min_messages;
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..2480aa4f14 100644
--- a/src/test/subscription/t/031_column_list.pl
+++ b/src/test/subscription/t/031_column_list.pl
@@ -1202,9 +1202,9 @@ $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(
--
2.41.0.windows.3