v42-0001-Enable-support-for-publish_generated_columns-opt.patch
text/x-patch
Filename: v42-0001-Enable-support-for-publish_generated_columns-opt.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v42-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 | 38 | 10 |
| src/backend/commands/publicationcmds.c | 30 | 3 |
| src/backend/replication/logical/proto.c | 42 | 29 |
| src/backend/replication/logical/tablesync.c | 38 | 22 |
| src/backend/replication/pgoutput/pgoutput.c | 127 | 43 |
| 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 | 1 |
| src/bin/psql/tab-complete.in.c | 2 | 2 |
| src/include/catalog/pg_publication.h | 5 | 0 |
| src/include/replication/logicalproto.h | 10 | 8 |
| src/test/regress/expected/psql.out | 3 | 3 |
| src/test/regress/expected/publication.out | 296 | 212 |
| src/test/regress/sql/publication.sql | 44 | 1 |
| src/test/subscription/t/031_column_list.pl | 2 | 2 |
From 2c49f076e35ed82035784a696ce8e472d041c857 Mon Sep 17 00:00:00 2001
From: Khanna <Shubham.Khanna@fujitsu.com>
Date: Tue, 8 Oct 2024 11:02:36 +0530
Subject: [PATCH v42 1/3] Enable support for 'publish_generated_columns'
option.
Generated column values are not currently replicated because it is assumed
that the corresponding subscriber-side table will generate its own values
for those columns.
This patch supports the transmission of generated column information and data
alongside regular table changes. This behaviour is partly controlled by a new
publication parameter 'publish_generated_columns'.
Example usage:
CREATE PUBLICATION pub1 FOR TABLE tab_gencol WITH (publish_generated_columns = true);
Generated columns can also be published if they are specified in a
publication column list. This overrides the parameter, so it works even if
'publish_generated_columns' is false.
When the subscription parameter 'copy_data' is true, then data is copied
during the initial table synchronization using the COPY command. The
normal COPY command does not copy generated columns, so if generated columns are
published we need to use a different form of the copy syntax:
'COPY (SELECT column_name FROM table_name) TO STDOUT'.
~~
Behavior Summary:
A. when generated columns are published
* Publisher not-generated column => subscriber not-generated column:
This is just normal logical replication (not changed by this patch).
* Publisher not-generated column => subscriber generated column:
This will give ERROR.
* Publisher generated column => subscriber not-generated column:
The publisher generated column value is copied.
* Publisher generated column => subscriber generated column:
This will give ERROR.
B. when generated columns are not published
* Publisher not-generated column => subscriber not-generated column:
This is just normal logical replication (not changed by this patch).
* Publisher not-generated column => subscriber generated column:
This will give ERROR.
* Publisher generated column => subscriber not-generated column:
The publisher generated column is not replicated. The subscriber column
will be filled with the subscriber-side default data.
* Publisher generated column => subscriber generated column:
The publisher generated column is not replicated. The subscriber
generated column will be filled with the subscriber-side computed or
default data.
~
There is a change in 'pg_publication' 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 | 48 +-
src/backend/commands/publicationcmds.c | 33 +-
src/backend/replication/logical/proto.c | 71 +--
src/backend/replication/logical/tablesync.c | 60 ++-
src/backend/replication/pgoutput/pgoutput.c | 170 +++++--
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 | 17 +-
src/bin/psql/tab-complete.in.c | 4 +-
src/include/catalog/pg_publication.h | 5 +
src/include/replication/logicalproto.h | 18 +-
src/test/regress/expected/psql.out | 6 +-
src/test/regress/expected/publication.out | 508 ++++++++++++--------
src/test/regress/sql/publication.sql | 45 +-
src/test/subscription/t/031_column_list.pl | 4 +-
19 files changed, 698 insertions(+), 343 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index f6344b3b79..577bcb4b71 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 are allowed to be replicated during logical replication
+ according to the <command>CREATE PUBLICATION</command> option
+ <link linkend="sql-createpublication-params-with-publish-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 057c46f3f5..e2895209a1 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -6544,7 +6544,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 7e5e357fd9..e937f8aed7 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -225,6 +225,41 @@ filter_partitions(List *table_infos)
}
}
+/*
+ * Returns true if the relation has column list associated with the
+ * publication, false if the relation has no column list associated with the
+ * publication.
+ */
+bool
+is_column_list_publication(Publication *pub, Oid relid)
+{
+ HeapTuple cftuple = NULL;
+ bool isnull = true;
+
+ if (pub->alltables)
+ return false;
+
+ cftuple = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+ if (HeapTupleIsValid(cftuple))
+ {
+ /* Lookup the column list attribute. */
+ (void) SysCacheGetAttr(PUBLICATIONRELMAP, cftuple,
+ Anum_pg_publication_rel_prattrs,
+ &isnull);
+ if (!isnull)
+ {
+ ReleaseSysCache(cftuple);
+ return true;
+ }
+
+ ReleaseSysCache(cftuple);
+ }
+
+ return false;
+}
+
/*
* Returns true if any schema is associated with the publication, false if no
* schema is associated with the publication.
@@ -500,8 +535,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
* pub_collist_validate
* Process and validate the 'columns' list and ensure the columns are all
* valid to use for a publication. Checks for and raises an ERROR for
- * any; unknown columns, system columns, duplicate columns or generated
- * columns.
+ * any unknown columns, system columns, or duplicate columns.
*
* Looks up each column's attnum and returns a 0-based Bitmapset of the
* corresponding attnums.
@@ -511,7 +545,6 @@ pub_collist_validate(Relation targetrel, List *columns)
{
Bitmapset *set = NULL;
ListCell *lc;
- TupleDesc tupdesc = RelationGetDescr(targetrel);
foreach(lc, columns)
{
@@ -530,12 +563,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 +1033,7 @@ GetPublication(Oid pubid)
pub->pubactions.pubdelete = pubform->pubdelete;
pub->pubactions.pubtruncate = pubform->pubtruncate;
pub->pubviaroot = pubform->pubviaroot;
+ pub->pubgencols = pubform->pubgencols;
ReleaseSysCache(tup);
@@ -1213,7 +1241,7 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
+ if (att->attisdropped || (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..0129db18c6 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);
diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c
index 980f6e2741..a13225fd79 100644
--- a/src/backend/replication/logical/proto.c
+++ b/src/backend/replication/logical/proto.c
@@ -30,10 +30,11 @@
#define TRUNCATE_RESTART_SEQS (1<<1)
static void logicalrep_write_attrs(StringInfo out, Relation rel,
- Bitmapset *columns);
+ Bitmapset *columns, bool pubgencols);
static void logicalrep_write_tuple(StringInfo out, Relation rel,
TupleTableSlot *slot,
- bool binary, Bitmapset *columns);
+ bool binary, Bitmapset *columns,
+ bool pubgencols);
static void logicalrep_read_attrs(StringInfo in, LogicalRepRelation *rel);
static void logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple);
@@ -52,6 +53,28 @@ column_in_column_list(int attnum, Bitmapset *columns)
return (columns == NULL || bms_is_member(attnum, columns));
}
+/*
+ * Check if the column should be published.
+ */
+static bool
+should_publish_column(Form_pg_attribute att, Bitmapset *columns,
+ bool pubgencols)
+{
+ if (att->attisdropped)
+ return false;
+
+ /*
+ * Skip publishing generated columns if the option is not specified and if
+ * they are not included in the column list.
+ */
+ if (att->attgenerated && !pubgencols && !columns)
+ return false;
+
+ if (!column_in_column_list(att->attnum, columns))
+ return false;
+
+ return true;
+}
/*
* Write BEGIN to the output stream.
@@ -412,7 +435,8 @@ logicalrep_read_origin(StringInfo in, XLogRecPtr *origin_lsn)
*/
void
logicalrep_write_insert(StringInfo out, TransactionId xid, Relation rel,
- TupleTableSlot *newslot, bool binary, Bitmapset *columns)
+ TupleTableSlot *newslot, bool binary,
+ Bitmapset *columns, bool pubgencols)
{
pq_sendbyte(out, LOGICAL_REP_MSG_INSERT);
@@ -424,7 +448,7 @@ logicalrep_write_insert(StringInfo out, TransactionId xid, Relation rel,
pq_sendint32(out, RelationGetRelid(rel));
pq_sendbyte(out, 'N'); /* new tuple follows */
- logicalrep_write_tuple(out, rel, newslot, binary, columns);
+ logicalrep_write_tuple(out, rel, newslot, binary, columns, pubgencols);
}
/*
@@ -457,7 +481,7 @@ logicalrep_read_insert(StringInfo in, LogicalRepTupleData *newtup)
void
logicalrep_write_update(StringInfo out, TransactionId xid, Relation rel,
TupleTableSlot *oldslot, TupleTableSlot *newslot,
- bool binary, Bitmapset *columns)
+ bool binary, Bitmapset *columns, bool pubgencols)
{
pq_sendbyte(out, LOGICAL_REP_MSG_UPDATE);
@@ -478,11 +502,11 @@ logicalrep_write_update(StringInfo out, TransactionId xid, Relation rel,
pq_sendbyte(out, 'O'); /* old tuple follows */
else
pq_sendbyte(out, 'K'); /* old key follows */
- logicalrep_write_tuple(out, rel, oldslot, binary, columns);
+ logicalrep_write_tuple(out, rel, oldslot, binary, columns, pubgencols);
}
pq_sendbyte(out, 'N'); /* new tuple follows */
- logicalrep_write_tuple(out, rel, newslot, binary, columns);
+ logicalrep_write_tuple(out, rel, newslot, binary, columns, pubgencols);
}
/*
@@ -532,7 +556,7 @@ logicalrep_read_update(StringInfo in, bool *has_oldtuple,
void
logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel,
TupleTableSlot *oldslot, bool binary,
- Bitmapset *columns)
+ Bitmapset *columns, bool pubgencols)
{
Assert(rel->rd_rel->relreplident == REPLICA_IDENTITY_DEFAULT ||
rel->rd_rel->relreplident == REPLICA_IDENTITY_FULL ||
@@ -552,7 +576,7 @@ logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel,
else
pq_sendbyte(out, 'K'); /* old key follows */
- logicalrep_write_tuple(out, rel, oldslot, binary, columns);
+ logicalrep_write_tuple(out, rel, oldslot, binary, columns, pubgencols);
}
/*
@@ -668,7 +692,7 @@ logicalrep_write_message(StringInfo out, TransactionId xid, XLogRecPtr lsn,
*/
void
logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
- Bitmapset *columns)
+ Bitmapset *columns, bool pubgencols)
{
char *relname;
@@ -690,7 +714,7 @@ logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
pq_sendbyte(out, rel->rd_rel->relreplident);
/* send the attribute info */
- logicalrep_write_attrs(out, rel, columns);
+ logicalrep_write_attrs(out, rel, columns, pubgencols);
}
/*
@@ -767,7 +791,7 @@ logicalrep_read_typ(StringInfo in, LogicalRepTyp *ltyp)
*/
static void
logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot,
- bool binary, Bitmapset *columns)
+ bool binary, Bitmapset *columns, bool pubgencols)
{
TupleDesc desc;
Datum *values;
@@ -781,10 +805,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot,
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
- continue;
-
- if (!column_in_column_list(att->attnum, columns))
+ if (!should_publish_column(att, columns, pubgencols))
continue;
nliveatts++;
@@ -802,10 +823,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)
- continue;
-
- if (!column_in_column_list(att->attnum, columns))
+ if (!should_publish_column(att, columns, pubgencols))
continue;
if (isnull[i])
@@ -923,7 +941,8 @@ logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple)
* Write relation attribute metadata to the stream.
*/
static void
-logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns)
+logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns,
+ bool pubgencols)
{
TupleDesc desc;
int i;
@@ -938,10 +957,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- if (att->attisdropped || att->attgenerated)
- continue;
-
- if (!column_in_column_list(att->attnum, columns))
+ if (!should_publish_column(att, columns, pubgencols))
continue;
nliveatts++;
@@ -959,10 +975,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)
- continue;
-
- if (!column_in_column_list(att->attnum, columns))
+ if (!should_publish_column(att, columns, pubgencols))
continue;
/* REPLICA IDENTITY FULL means all columns are sent as part of key. */
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index e03e761392..fa123fda6d 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -791,19 +791,20 @@ copy_read_data(void *outbuf, int minread, int maxread)
* qualifications to be used in the COPY command.
*/
static void
-fetch_remote_table_info(char *nspname, char *relname,
- LogicalRepRelation *lrel, List **qual)
+fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
+ List **qual, bool *remotegencolpresent)
{
WalRcvExecResult *res;
StringInfoData cmd;
TupleTableSlot *slot;
Oid tableRow[] = {OIDOID, CHAROID, CHAROID};
- Oid attrRow[] = {INT2OID, TEXTOID, OIDOID, BOOLOID};
+ Oid attrRow[] = {INT2OID, TEXTOID, OIDOID, BOOLOID, BOOLOID};
Oid qualRow[] = {TEXTOID};
bool isnull;
int natt;
ListCell *lc;
Bitmapset *included_cols = NULL;
+ int server_version = walrcv_server_version(LogRepWorkerWalRcvConn);
lrel->nspname = nspname;
lrel->relname = relname;
@@ -851,7 +852,7 @@ fetch_remote_table_info(char *nspname, char *relname,
* We need to do this before fetching info about column names and types,
* so that we can skip columns that should not be replicated.
*/
- if (walrcv_server_version(LogRepWorkerWalRcvConn) >= 150000)
+ if (server_version >= 150000)
{
WalRcvExecResult *pubres;
TupleTableSlot *tslot;
@@ -873,8 +874,7 @@ fetch_remote_table_info(char *nspname, char *relname,
resetStringInfo(&cmd);
appendStringInfo(&cmd,
"SELECT DISTINCT"
- " (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)"
- " THEN NULL ELSE gpt.attrs END)"
+ " (gpt.attrs)"
" FROM pg_publication p,"
" LATERAL pg_get_publication_tables(p.pubname) gpt,"
" pg_class c"
@@ -948,20 +948,21 @@ fetch_remote_table_info(char *nspname, char *relname,
"SELECT a.attnum,"
" a.attname,"
" a.atttypid,"
- " a.attnum = ANY(i.indkey)"
+ " a.attnum = ANY(i.indkey)");
+
+ if (server_version >= 180000)
+ appendStringInfo(&cmd, ", a.attgenerated != ''");
+
+ appendStringInfo(&cmd,
" FROM pg_catalog.pg_attribute a"
" LEFT JOIN pg_catalog.pg_index i"
" ON (i.indexrelid = pg_get_replica_identity_index(%u))"
" WHERE a.attnum > 0::pg_catalog.int2"
- " AND NOT a.attisdropped %s"
+ " AND NOT a.attisdropped"
" AND a.attrelid = %u"
- " ORDER BY a.attnum",
- lrel->remoteid,
- (walrcv_server_version(LogRepWorkerWalRcvConn) >= 120000 ?
- "AND a.attgenerated = ''" : ""),
- lrel->remoteid);
+ " ORDER BY a.attnum", lrel->remoteid, lrel->remoteid);
res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data,
- lengthof(attrRow), attrRow);
+ server_version >= 180000 ? lengthof(attrRow) : lengthof(attrRow) - 1, attrRow);
if (res->status != WALRCV_OK_TUPLES)
ereport(ERROR,
@@ -1005,6 +1006,9 @@ fetch_remote_table_info(char *nspname, char *relname,
if (DatumGetBool(slot_getattr(slot, 4, &isnull)))
lrel->attkeys = bms_add_member(lrel->attkeys, natt);
+ if (server_version >= 180000)
+ *remotegencolpresent |= DatumGetBool(slot_getattr(slot, 5, &isnull));
+
/* Should never happen. */
if (++natt >= MaxTupleAttributeNumber)
elog(ERROR, "too many columns in remote table \"%s.%s\"",
@@ -1037,7 +1041,7 @@ fetch_remote_table_info(char *nspname, char *relname,
* 3) one of the subscribed publications is declared as TABLES IN SCHEMA
* that includes this relation
*/
- if (walrcv_server_version(LogRepWorkerWalRcvConn) >= 150000)
+ if (server_version >= 150000)
{
StringInfoData pub_names;
@@ -1123,10 +1127,12 @@ copy_table(Relation rel)
List *attnamelist;
ParseState *pstate;
List *options = NIL;
+ bool gencol_copy_needed = false;
/* Get the publisher relation info. */
fetch_remote_table_info(get_namespace_name(RelationGetNamespace(rel)),
- RelationGetRelationName(rel), &lrel, &qual);
+ RelationGetRelationName(rel), &lrel, &qual,
+ &gencol_copy_needed);
/* Put the relation into relmap. */
logicalrep_relmap_update(&lrel);
@@ -1135,11 +1141,16 @@ copy_table(Relation rel)
relmapentry = logicalrep_rel_open(lrel.remoteid, NoLock);
Assert(rel == relmapentry->localrel);
+ attnamelist = make_copy_attnamelist(relmapentry);
+
/* Start copy on the publisher. */
initStringInfo(&cmd);
- /* Regular table with no row filter */
- if (lrel.relkind == RELKIND_RELATION && qual == NIL)
+ /*
+ * Regular table with no row filter and copy of generated columns is not
+ * necessary.
+ */
+ if (lrel.relkind == RELKIND_RELATION && qual == NIL && !gencol_copy_needed)
{
appendStringInfo(&cmd, "COPY %s",
quote_qualified_identifier(lrel.nspname, lrel.relname));
@@ -1173,13 +1184,19 @@ copy_table(Relation rel)
* (SELECT ...), but we can't just do SELECT * because we need to not
* copy generated columns. For tables with any row filters, build a
* SELECT query with OR'ed row filters for COPY.
+ *
+ * We also need to use this same COPY (SELECT ...) syntax when
+ * generated columns are published, because copy of generated columns
+ * is not supported by the normal COPY.
*/
+ int i = 0;
+
appendStringInfoString(&cmd, "COPY (SELECT ");
- for (int i = 0; i < lrel.natts; i++)
+ foreach_node(String, att_name, attnamelist)
{
- appendStringInfoString(&cmd, quote_identifier(lrel.attnames[i]));
- if (i < lrel.natts - 1)
+ if (i++)
appendStringInfoString(&cmd, ", ");
+ appendStringInfoString(&cmd, quote_identifier(strVal(att_name)));
}
appendStringInfoString(&cmd, " FROM ");
@@ -1237,7 +1254,6 @@ copy_table(Relation rel)
(void) addRangeTableEntryForRelation(pstate, rel, AccessShareLock,
NULL, false, false);
- attnamelist = make_copy_attnamelist(relmapentry);
cstate = BeginCopyFrom(pstate, rel, NULL, NULL, false, copy_read_data, attnamelist, options);
/* Do the copy */
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 00e7024563..ed578ec014 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -84,9 +84,6 @@ static bool publications_valid;
static List *LoadPublications(List *pubnames);
static void publication_invalidation_cb(Datum arg, int cacheid,
uint32 hashvalue);
-static void send_relation_and_attrs(Relation relation, TransactionId xid,
- LogicalDecodingContext *ctx,
- Bitmapset *columns);
static void send_repl_origin(LogicalDecodingContext *ctx,
RepOriginId origin_id, XLogRecPtr origin_lsn,
bool send_origin);
@@ -170,6 +167,9 @@ typedef struct RelationSyncEntry
*/
Bitmapset *columns;
+ /* Include publishing generated columns */
+ bool pubgencols;
+
/*
* Private context to store additional data for this entry - state for the
* row filter expressions, column list, etc.
@@ -213,6 +213,9 @@ static void init_rel_sync_cache(MemoryContext cachectx);
static void cleanup_rel_sync_cache(TransactionId xid, bool is_commit);
static RelationSyncEntry *get_rel_sync_entry(PGOutputData *data,
Relation relation);
+static void send_relation_and_attrs(Relation relation, TransactionId xid,
+ LogicalDecodingContext *ctx,
+ RelationSyncEntry *relentry);
static void rel_sync_cache_relation_cb(Datum arg, Oid relid);
static void rel_sync_cache_publication_cb(Datum arg, int cacheid,
uint32 hashvalue);
@@ -731,11 +734,11 @@ maybe_send_schema(LogicalDecodingContext *ctx,
{
Relation ancestor = RelationIdGetRelation(relentry->publish_as_relid);
- send_relation_and_attrs(ancestor, xid, ctx, relentry->columns);
+ send_relation_and_attrs(ancestor, xid, ctx, relentry);
RelationClose(ancestor);
}
- send_relation_and_attrs(relation, xid, ctx, relentry->columns);
+ send_relation_and_attrs(relation, xid, ctx, relentry);
if (data->in_streaming)
set_schema_sent_in_streamed_txn(relentry, topxid);
@@ -749,9 +752,10 @@ maybe_send_schema(LogicalDecodingContext *ctx,
static void
send_relation_and_attrs(Relation relation, TransactionId xid,
LogicalDecodingContext *ctx,
- Bitmapset *columns)
+ RelationSyncEntry *relentry)
{
TupleDesc desc = RelationGetDescr(relation);
+ Bitmapset *columns = relentry->columns;
int i;
/*
@@ -766,12 +770,19 @@ 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)
continue;
+ /*
+ * Skip publishing generated columns if the option is not specified and
+ * if they are not included in the column list.
+ */
+ if (att->attgenerated && !relentry->pubgencols && !columns)
+ continue;
+
/* Skip this attribute if it's not present in the column list */
if (columns != NULL && !bms_is_member(att->attnum, columns))
continue;
@@ -782,7 +793,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid,
}
OutputPluginPrepareWrite(ctx, false);
- logicalrep_write_rel(ctx->out, xid, relation, columns);
+ logicalrep_write_rel(ctx->out, xid, relation, columns, relentry->pubgencols);
OutputPluginWrite(ctx, false);
}
@@ -1008,6 +1019,67 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications,
}
}
+/*
+ * If the table contains a generated column, check for any conflicting
+ * values of publish_generated_columns in the publications.
+ */
+static void
+pgoutput_pubgencol_init(PGOutputData *data, List *publications,
+ RelationSyncEntry *entry)
+{
+ Relation relation = RelationIdGetRelation(entry->publish_as_relid);
+ TupleDesc desc = RelationGetDescr(relation);
+ bool gencolpresent = false;
+ ListCell *lc;
+ bool first = true;
+
+ /* Check if there is any generated column present */
+ for (int i = 0; i < desc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(desc, i);
+ if (att->attgenerated)
+ {
+ gencolpresent = true;
+ break;
+ }
+ }
+
+ /* There is no generated columns to be published */
+ if (!gencolpresent)
+ {
+ entry->pubgencols = false;
+ return;
+ }
+
+ /*
+ * There may be a conflicting value for publish_generated_columns in the
+ * publications.
+ */
+ foreach(lc, publications)
+ {
+ Publication *pub = lfirst(lc);
+
+ /*
+ * The column list takes precedence over pubgencols, so skip checking
+ * column list publications.
+ */
+ if (is_column_list_publication(pub, entry->publish_as_relid))
+ continue;
+
+ if (first)
+ {
+ entry->pubgencols = pub->pubgencols;
+ first = false;
+ }
+ else if (entry->pubgencols != pub->pubgencols)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use different values of publish_generated_columns for table \"%s.%s\" in different publications",
+ get_namespace_name(RelationGetNamespace(relation)),
+ RelationGetRelationName(relation)));
+ }
+}
+
/*
* Initialize the column list.
*/
@@ -1017,7 +1089,31 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
{
ListCell *lc;
bool first = true;
+ Bitmapset *relcols = NULL;
Relation relation = RelationIdGetRelation(entry->publish_as_relid);
+ TupleDesc desc = RelationGetDescr(relation);
+ MemoryContext oldcxt = NULL;
+ bool collistpubexist = false;
+
+ pgoutput_ensure_entry_cxt(data, entry);
+
+ oldcxt = MemoryContextSwitchTo(entry->entry_cxt);
+
+ /*
+ * Prepare the columns that will be published for FOR ALL TABLES and
+ * FOR TABLES IN SCHEMA publication.
+ */
+ for (int i = 0; i < desc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(desc, i);
+
+ if (att->attisdropped || (att->attgenerated && !entry->pubgencols))
+ continue;
+
+ relcols = bms_add_member(relcols, att->attnum);
+ }
+
+ MemoryContextSwitchTo(oldcxt);
/*
* Find if there are any column lists for this relation. If there are,
@@ -1032,14 +1128,15 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
* need to check all the given publication-table mappings and report an
* error if any publications have a different column list.
*
- * FOR ALL TABLES and FOR TABLES IN SCHEMA imply "don't use column list".
+ * FOR ALL TABLES and FOR TABLES IN SCHEMA will use the relcols which is
+ * prepared according to the pubgencols option.
*/
foreach(lc, publications)
{
Publication *pub = lfirst(lc);
HeapTuple cftuple = NULL;
Datum cfdatum = 0;
- Bitmapset *cols = NULL;
+ Bitmapset *cols = relcols;
/*
* If the publication is FOR ALL TABLES then it is treated the same as
@@ -1071,35 +1168,9 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
/* 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);
-
- pgoutput_ensure_entry_cxt(data, entry);
-
- cols = pub_collist_to_bitmapset(cols, cfdatum,
+ collistpubexist = true;
+ cols = pub_collist_to_bitmapset(NULL, cfdatum,
entry->entry_cxt);
-
- /* 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 column list includes all the columns of the table,
- * set it to NULL.
- */
- if (bms_num_members(cols) == nliveatts)
- {
- bms_free(cols);
- cols = NULL;
- }
}
ReleaseSysCache(cftuple);
@@ -1115,10 +1186,17 @@ pgoutput_column_list_init(PGOutputData *data, List *publications,
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot use different column lists for table \"%s.%s\" in different publications",
- get_namespace_name(RelationGetNamespace(relation)),
- RelationGetRelationName(relation)));
+ get_namespace_name(RelationGetNamespace(relation)),
+ RelationGetRelationName(relation)));
} /* loop all subscribed publications */
+ /*
+ * If no column list publications exist, columns will be selected later
+ * based on the generated columns option.
+ */
+ if (!collistpubexist)
+ entry->columns = NULL;
+
RelationClose(relation);
}
@@ -1531,15 +1609,18 @@ pgoutput_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
{
case REORDER_BUFFER_CHANGE_INSERT:
logicalrep_write_insert(ctx->out, xid, targetrel, new_slot,
- data->binary, relentry->columns);
+ data->binary, relentry->columns,
+ relentry->pubgencols);
break;
case REORDER_BUFFER_CHANGE_UPDATE:
logicalrep_write_update(ctx->out, xid, targetrel, old_slot,
- new_slot, data->binary, relentry->columns);
+ new_slot, data->binary, relentry->columns,
+ relentry->pubgencols);
break;
case REORDER_BUFFER_CHANGE_DELETE:
logicalrep_write_delete(ctx->out, xid, targetrel, old_slot,
- data->binary, relentry->columns);
+ data->binary, relentry->columns,
+ relentry->pubgencols);
break;
default:
Assert(false);
@@ -2213,6 +2294,9 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
/* Initialize the row filter */
pgoutput_row_filter_init(data, rel_publications, entry);
+ /* Initialize publish generated columns value */
+ pgoutput_pubgencol_init(data, rel_publications, entry);
+
/* Initialize the column list */
pgoutput_column_list_init(data, rel_publications, entry);
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1b47c388ce..1d79865058 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;
@@ -4291,23 +4292,29 @@ getPublications(Archive *fout)
query = createPQExpBuffer();
/* 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 pubgencols "
"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 pubgencols "
"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 pubgencols "
"FROM pg_publication p");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -4327,6 +4334,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));
@@ -4351,6 +4359,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);
@@ -4432,6 +4442,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 363a66e718..7d78fceed6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6233,7 +6233,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)
{
@@ -6268,6 +6268,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");
@@ -6357,6 +6361,7 @@ describePublications(const char *pattern)
PGresult *res;
bool has_pubtruncate;
bool has_pubviaroot;
+ bool has_pubgencols;
PQExpBufferData title;
printTableContent cont;
@@ -6373,6 +6378,7 @@ describePublications(const char *pattern)
has_pubtruncate = (pset.sversion >= 110000);
has_pubviaroot = (pset.sversion >= 130000);
+ has_pubgencols = (pset.sversion >= 180000);
initPQExpBuffer(&buf);
@@ -6386,6 +6392,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");
@@ -6437,6 +6446,8 @@ describePublications(const char *pattern)
ncols++;
if (has_pubviaroot)
ncols++;
+ if (has_pubgencols)
+ ncols++;
initPQExpBuffer(&title);
printfPQExpBuffer(&title, _("Publication %s"), pubname);
@@ -6451,6 +6462,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);
@@ -6461,6 +6474,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.in.c b/src/bin/psql/tab-complete.in.c
index 1be0056af7..fad2277991 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2261,7 +2261,7 @@ match_previous_words(int pattern_id,
"CURRENT_SCHEMA");
/* ALTER PUBLICATION <name> SET ( */
else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "SET", "("))
- COMPLETE_WITH("publish", "publish_via_partition_root");
+ COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
/* ALTER SUBSCRIPTION <name> */
else if (Matches("ALTER", "SUBSCRIPTION", MatchAny))
COMPLETE_WITH("CONNECTION", "ENABLE", "DISABLE", "OWNER TO",
@@ -3513,7 +3513,7 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("WITH (");
/* Complete "CREATE PUBLICATION <name> [...] WITH" */
else if (Matches("CREATE", "PUBLICATION", MatchAnyN, "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..2839847ba0 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;
@@ -150,6 +154,7 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
+extern bool is_column_list_publication(Publication *pub, Oid relid);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
bool if_not_exists);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
diff --git a/src/include/replication/logicalproto.h b/src/include/replication/logicalproto.h
index c409638a2e..fa6d66bff8 100644
--- a/src/include/replication/logicalproto.h
+++ b/src/include/replication/logicalproto.h
@@ -223,20 +223,21 @@ extern void logicalrep_write_origin(StringInfo out, const char *origin,
XLogRecPtr origin_lsn);
extern char *logicalrep_read_origin(StringInfo in, XLogRecPtr *origin_lsn);
extern void logicalrep_write_insert(StringInfo out, TransactionId xid,
- Relation rel,
- TupleTableSlot *newslot,
- bool binary, Bitmapset *columns);
+ Relation rel, TupleTableSlot *newslot,
+ bool binary, Bitmapset *columns,
+ bool pubgencols);
extern LogicalRepRelId logicalrep_read_insert(StringInfo in, LogicalRepTupleData *newtup);
extern void logicalrep_write_update(StringInfo out, TransactionId xid,
- Relation rel,
- TupleTableSlot *oldslot,
- TupleTableSlot *newslot, bool binary, Bitmapset *columns);
+ Relation rel, TupleTableSlot *oldslot,
+ TupleTableSlot *newslot, bool binary,
+ Bitmapset *columns, bool pubgencols);
extern LogicalRepRelId logicalrep_read_update(StringInfo in,
bool *has_oldtuple, LogicalRepTupleData *oldtup,
LogicalRepTupleData *newtup);
extern void logicalrep_write_delete(StringInfo out, TransactionId xid,
Relation rel, TupleTableSlot *oldslot,
- bool binary, Bitmapset *columns);
+ bool binary, Bitmapset *columns,
+ bool pubgencols);
extern LogicalRepRelId logicalrep_read_delete(StringInfo in,
LogicalRepTupleData *oldtup);
extern void logicalrep_write_truncate(StringInfo out, TransactionId xid,
@@ -247,7 +248,8 @@ extern List *logicalrep_read_truncate(StringInfo in,
extern void logicalrep_write_message(StringInfo out, TransactionId xid, XLogRecPtr lsn,
bool transactional, const char *prefix, Size sz, const char *message);
extern void logicalrep_write_rel(StringInfo out, TransactionId xid,
- Relation rel, Bitmapset *columns);
+ Relation rel, Bitmapset *columns,
+ bool pubgencols);
extern LogicalRepRelation *logicalrep_read_rel(StringInfo in);
extern void logicalrep_write_typ(StringInfo out, TransactionId xid,
Oid typoid);
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..72943ef59a 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,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 column "d" 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 +736,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 +923,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 +1131,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 +1172,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 +1253,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 +1266,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 +1295,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 +1321,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 +1392,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 +1403,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 +1424,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 +1436,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 +1448,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 +1459,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 +1470,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 +1481,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 +1512,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 +1524,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 +1606,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 +1627,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 +1755,84 @@ 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)
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+-- Test the 'publish_generated_columns' parameter enabled or disabled for
+-- different scenarios with/without generated columns in column lists.
+CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+-- Generated columns in column list, when 'publish_generated_columns'=false
+CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false);
+\dRp+ pub1
+ Publication pub1
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
+Tables:
+ "public.gencols" (a, gen1)
+
+-- Generated columns in column list, when 'publish_generated_columns'=true
+CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true);
+\dRp+ pub2
+ Publication pub2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | t
+Tables:
+ "public.gencols" (a, gen1)
+
+-- Generated columns in column list, then set 'publication_generate_columns'=false
+ALTER PUBLICATION pub2 SET (publish_generated_columns = false);
+\dRp+ pub2
+ Publication pub2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
+Tables:
+ "public.gencols" (a, gen1)
+
+-- Remove generated columns from column list, when 'publish_generated_columns'=false
+ALTER PUBLICATION pub2 SET TABLE gencols(a);
+\dRp+ pub2
+ Publication pub2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
+Tables:
+ "public.gencols" (a)
+
+-- Add generated columns in column list, when 'publish_generated_columns'=false
+ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1);
+\dRp+ pub2
+ Publication pub2
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns
+--------------------------+------------+---------+---------+---------+-----------+----------+-------------------
+ regress_publication_user | f | t | t | t | t | f | f
+Tables:
+ "public.gencols" (a, gen1)
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+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..1ee322fc4f 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 column "d" 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);
@@ -1109,7 +1112,47 @@ 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
+CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns=0);
+\dRp+ pub2
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+
+-- Test the 'publish_generated_columns' parameter enabled or disabled for
+-- different scenarios with/without generated columns in column lists.
+CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
+
+-- Generated columns in column list, when 'publish_generated_columns'=false
+CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false);
+\dRp+ pub1
+-- Generated columns in column list, when 'publish_generated_columns'=true
+CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true);
+\dRp+ pub2
+
+-- Generated columns in column list, then set 'publication_generate_columns'=false
+ALTER PUBLICATION pub2 SET (publish_generated_columns = false);
+\dRp+ pub2
+
+-- Remove generated columns from column list, when 'publish_generated_columns'=false
+ALTER PUBLICATION pub2 SET TABLE gencols(a);
+\dRp+ pub2
+
+-- Add generated columns in column list, when 'publish_generated_columns'=false
+ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1);
+\dRp+ pub2
+
+DROP PUBLICATION pub1;
+DROP PUBLICATION pub2;
+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.34.1