From f0aef83bb03ca5cfb5faf45afa1ced5f0747c867 Mon Sep 17 00:00:00 2001 From: Khanna Date: Tue, 8 Oct 2024 11:02:36 +0530 Subject: [PATCH v37] Enable support for 'publish_generated_columns' option. Currently generated column values are not replicated because it is assumed that the corresponding subscriber-side table will generate its own values for those columns. This commit supports the transmission of generated column information and data alongside regular table changes. This behaviour is controlled by a new PUBLICATION parameter ('publish_generated_columns'). Example usage of subscription option: CREATE PUBLICATION FOR TABLE tab_gencol WITH (publish_generated_columns = true); When 'publish_generated_columns' is false, generated columns are not replicated. But when generated columns are specified in PUBLICATION col-list, it is replicated even the 'publish_generated_columns' is false. The replication of generated column during initial sync using tablesync: When 'copy_data' is true, during the initial sync, the data is replicated from the publisher to the subscriber using the COPY command. The normal COPY command does not copy generated columns, so when 'publish_generated_columns' is true, we need to copy using the syntax: 'COPY (SELECT column_name FROM table_name) TO STDOUT'. Here 'publish_generated_columns' is a PUBLICATION parameter and 'copy_data' is a SUBSCRIPTION parameter. Summary: when (publish_generated_columns = true) * 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. when (publish_generated_columns = false) * 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: Publisher generated column is not replicated. The subscriber column will be filled with the subscriber-side default data. * publisher generated column => subscriber generated column: Publisher generated column is not replicated. The subscriber generated column will be filed with the subscriber-side computed or default data. There is a change in 'pg_publicataion' catalog so we need to bump the catversion. --- doc/src/sgml/ddl.sgml | 6 +- doc/src/sgml/protocol.sgml | 2 +- doc/src/sgml/ref/create_publication.sgml | 12 + src/backend/catalog/pg_publication.c | 13 +- src/backend/catalog/pg_subscription.c | 31 ++ src/backend/commands/publicationcmds.c | 33 +- src/backend/commands/subscriptioncmds.c | 31 -- src/backend/replication/logical/proto.c | 8 +- src/backend/replication/logical/relation.c | 2 +- src/backend/replication/logical/tablesync.c | 205 +++++++-- src/backend/replication/pgoutput/pgoutput.c | 108 +++-- src/bin/pg_dump/pg_dump.c | 21 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 10 + src/bin/psql/describe.c | 18 +- src/bin/psql/tab-complete.in.c | 2 +- src/include/catalog/pg_publication.h | 4 + src/include/catalog/pg_subscription.h | 4 + src/include/replication/logicalrelation.h | 3 +- src/test/regress/expected/psql.out | 6 +- src/test/regress/expected/publication.out | 468 +++++++++++--------- src/test/regress/sql/publication.sql | 40 +- src/test/subscription/t/031_column_list.pl | 4 +- 23 files changed, 687 insertions(+), 345 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8ab0ddb112..7b9c349343 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -514,8 +514,10 @@ CREATE TABLE people ( - Generated columns are skipped for logical replication and cannot be - specified in a CREATE PUBLICATION column list. + Generated columns may be skipped during logical replication according to the + CREATE PUBLICATION parameter + + publish_generated_columns. 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;" Next, the following message part appears for each column included in - the publication (except generated columns): + the publication: 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 name + + + publish_generated_columns (boolean) + + + Specifies whether the generated columns present in the tables + associated with the publication should be replicated. + The default is false. + + + + diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 7fe5fe2b86..7ebb851e53 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -511,7 +511,6 @@ pub_collist_validate(Relation targetrel, List *columns) { Bitmapset *set = NULL; ListCell *lc; - TupleDesc tupdesc = RelationGetDescr(targetrel); foreach(lc, columns) { @@ -530,12 +529,6 @@ pub_collist_validate(Relation targetrel, List *columns) errmsg("cannot use system column \"%s\" in publication column list", colname)); - if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated) - ereport(ERROR, - errcode(ERRCODE_INVALID_COLUMN_REFERENCE), - errmsg("cannot use generated column \"%s\" in publication column list", - colname)); - if (bms_is_member(attnum, set)) ereport(ERROR, errcode(ERRCODE_DUPLICATE_OBJECT), @@ -1006,6 +999,7 @@ GetPublication(Oid pubid) pub->pubactions.pubdelete = pubform->pubdelete; pub->pubactions.pubtruncate = pubform->pubtruncate; pub->pubviaroot = pubform->pubviaroot; + pub->pubgencols = pubform->pubgencols; ReleaseSysCache(tup); @@ -1214,7 +1208,10 @@ pg_get_publication_tables(PG_FUNCTION_ARGS) { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) + continue; + + if (att->attgenerated && !pub->pubgencols) continue; attnums[nattnums++] = att->attnum; diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c index 9efc9159f2..fcfbf86c0b 100644 --- a/src/backend/catalog/pg_subscription.c +++ b/src/backend/catalog/pg_subscription.c @@ -551,3 +551,34 @@ GetSubscriptionRelations(Oid subid, bool not_ready) return res; } + +/* + * Add publication names from the list to a string. + */ +void +get_publications_str(List *publications, StringInfo dest, bool quote_literal) +{ + ListCell *lc; + bool first = true; + + Assert(publications != NIL); + + foreach(lc, publications) + { + char *pubname = strVal(lfirst(lc)); + + if (first) + first = false; + else + appendStringInfoString(dest, ", "); + + if (quote_literal) + appendStringInfoString(dest, quote_literal_cstr(pubname)); + else + { + appendStringInfoChar(dest, '"'); + appendStringInfoString(dest, pubname); + appendStringInfoChar(dest, '"'); + } + } +} 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/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 02ccc636b8..addf307cb6 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -439,37 +439,6 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, } } -/* - * Add publication names from the list to a string. - */ -static void -get_publications_str(List *publications, StringInfo dest, bool quote_literal) -{ - ListCell *lc; - bool first = true; - - Assert(publications != NIL); - - foreach(lc, publications) - { - char *pubname = strVal(lfirst(lc)); - - if (first) - first = false; - else - appendStringInfoString(dest, ", "); - - if (quote_literal) - appendStringInfoString(dest, quote_literal_cstr(pubname)); - else - { - appendStringInfoChar(dest, '"'); - appendStringInfoString(dest, pubname); - appendStringInfoChar(dest, '"'); - } - } -} - /* * Check that the specified publications are present on the publisher. */ diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c index 980f6e2741..6b085e555c 100644 --- a/src/backend/replication/logical/proto.c +++ b/src/backend/replication/logical/proto.c @@ -781,7 +781,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -802,7 +802,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, Form_pg_type typclass; Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -938,7 +938,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -959,7 +959,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) Form_pg_attribute att = TupleDescAttr(desc, i); uint8 flags = 0; - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c index f139e7b01e..338b083696 100644 --- a/src/backend/replication/logical/relation.c +++ b/src/backend/replication/logical/relation.c @@ -205,7 +205,7 @@ logicalrep_relmap_update(LogicalRepRelation *remoterel) * * Returns -1 if not found. */ -static int +int logicalrep_rel_att_by_name(LogicalRepRelation *remoterel, const char *attname) { int i; diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c index e03e761392..6f9e1269a0 100644 --- a/src/backend/replication/logical/tablesync.c +++ b/src/backend/replication/logical/tablesync.c @@ -118,6 +118,7 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/rel.h" #include "utils/rls.h" #include "utils/snapmgr.h" #include "utils/syscache.h" @@ -693,20 +694,72 @@ process_syncing_tables(XLogRecPtr current_lsn) /* * Create list of columns for COPY based on logical relation mapping. + * Exclude columns that are subscription table generated columns. */ static List * -make_copy_attnamelist(LogicalRepRelMapEntry *rel) +make_copy_attnamelist(LogicalRepRelMapEntry *rel, bool *remotegenlist) { List *attnamelist = NIL; - int i; + bool *localgenlist; + TupleDesc desc; - for (i = 0; i < rel->remoterel.natts; i++) + desc = RelationGetDescr(rel->localrel); + + /* + * localgenlist stores if a generated column on remoterel has a matching + * name corresponding to a generated column on localrel. + */ + localgenlist = palloc0(rel->remoterel.natts * sizeof(bool)); + + /* + * This loop checks for generated columns of the subscription table. + */ + for (int i = 0; i < desc->natts; i++) { - attnamelist = lappend(attnamelist, - makeString(rel->remoterel.attnames[i])); + int remote_attnum; + Form_pg_attribute attr = TupleDescAttr(desc, i); + + if (!attr->attgenerated) + continue; + + remote_attnum = logicalrep_rel_att_by_name(&rel->remoterel, + NameStr(attr->attname)); + + if (remote_attnum >= 0) + { + /* + * Check if the subscription table generated column has same name + * as a non-generated column in the corresponding publication + * table. + */ + if (!remotegenlist[remote_attnum]) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("logical replication target relation \"%s.%s\" has a generated column \"%s\" " + "but corresponding column on source relation is not a generated column", + rel->remoterel.nspname, rel->remoterel.relname, NameStr(attr->attname)))); + + /* + * 'localgenlist' records that this is a generated column in the + * subscription table. Later, we use this information to skip + * adding this column to the column list for COPY. + */ + localgenlist[remote_attnum] = true; + } } + /* + * Construct column list for COPY, excluding columns that are subscription + * table generated columns. + */ + for (int i = 0; i < rel->remoterel.natts; i++) + { + if (!localgenlist[i]) + attnamelist = lappend(attnamelist, + makeString(rel->remoterel.attnames[i])); + } + pfree(localgenlist); return attnamelist; } @@ -791,19 +844,21 @@ 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, +fetch_remote_table_info(char *nspname, char *relname, bool **remotegenlist_res, LogicalRepRelation *lrel, List **qual) { 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; + bool *remotegenlist; + bool has_pub_with_pubgencols = false; int natt; - ListCell *lc; Bitmapset *included_cols = NULL; + int server_version = walrcv_server_version(LogRepWorkerWalRcvConn); lrel->nspname = nspname; lrel->relname = relname; @@ -846,30 +901,24 @@ fetch_remote_table_info(char *nspname, char *relname, /* - * Get column lists for each relation. + * Get column lists for each relation, and check if any of the + * publications have the 'publish_generated_columns' parameter enabled. * * 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; Oid attrsRow[] = {INT2VECTOROID}; - StringInfoData pub_names; - - initStringInfo(&pub_names); - foreach(lc, MySubscription->publications) - { - if (foreach_current_index(lc) > 0) - appendStringInfoString(&pub_names, ", "); - appendStringInfoString(&pub_names, quote_literal_cstr(strVal(lfirst(lc)))); - } + StringInfo pub_names = makeStringInfo(); /* * Fetch info about column lists for the relation (from all the * publications). */ + get_publications_str(MySubscription->publications, pub_names, true); resetStringInfo(&cmd); appendStringInfo(&cmd, "SELECT DISTINCT" @@ -881,7 +930,7 @@ fetch_remote_table_info(char *nspname, char *relname, " WHERE gpt.relid = %u AND c.oid = gpt.relid" " AND p.pubname IN ( %s )", lrel->remoteid, - pub_names.data); + pub_names->data); pubres = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, lengthof(attrsRow), attrsRow); @@ -937,7 +986,44 @@ fetch_remote_table_info(char *nspname, char *relname, walrcv_clear_result(pubres); - pfree(pub_names.data); + /* + * Check if any of the publications have the + * 'publish_generated_columns' parameter enabled. + */ + if (server_version >= 180000) + { + WalRcvExecResult *gencolres; + Oid gencolsRow[] = {BOOLOID}; + + resetStringInfo(&cmd); + appendStringInfo(&cmd, + "SELECT count(*) > 0 FROM pg_catalog.pg_publication " + "WHERE pubname IN ( %s ) AND pubgencols = 't'", + pub_names->data); + + gencolres = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, + lengthof(gencolsRow), gencolsRow); + if (gencolres->status != WALRCV_OK_TUPLES) + ereport(ERROR, + errcode(ERRCODE_CONNECTION_FAILURE), + errmsg("could not fetch generated column publication information from publication list: %s", + pub_names->data)); + + tslot = MakeSingleTupleTableSlot(gencolres->tupledesc, &TTSOpsMinimalTuple); + if (!tuplestore_gettupleslot(gencolres->tuplestore, true, false, tslot)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("failed to fetch tuple for generated column publication information from publication list: %s", + pub_names->data)); + + has_pub_with_pubgencols = DatumGetBool(slot_getattr(tslot, 1, &isnull)); + Assert(!isnull); + + ExecClearTuple(tslot); + walrcv_clear_result(gencolres); + } + + pfree(pub_names->data); } /* @@ -948,20 +1034,25 @@ 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", lrel->remoteid); + + appendStringInfo(&cmd, " AND a.attrelid = %u" " ORDER BY a.attnum", - lrel->remoteid, - (walrcv_server_version(LogRepWorkerWalRcvConn) >= 120000 ? - "AND a.attgenerated = ''" : ""), 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, @@ -973,6 +1064,7 @@ fetch_remote_table_info(char *nspname, char *relname, lrel->attnames = palloc0(MaxTupleAttributeNumber * sizeof(char *)); lrel->atttyps = palloc0(MaxTupleAttributeNumber * sizeof(Oid)); lrel->attkeys = NULL; + remotegenlist = palloc0(MaxTupleAttributeNumber * sizeof(bool)); /* * Store the columns as a list of names. Ignore those that are not @@ -995,6 +1087,20 @@ fetch_remote_table_info(char *nspname, char *relname, continue; } + if (server_version >= 120000) + remotegenlist[natt] = DatumGetBool(slot_getattr(slot, 5, &isnull)); + + /* + * If the column is generated and neither the generated column option + * is specified nor it appears in the column list, we will skip it. + */ + if (remotegenlist[natt] && !has_pub_with_pubgencols && + !bms_is_member(attnum, included_cols)) + { + ExecClearTuple(slot); + continue; + } + rel_colname = TextDatumGetCString(slot_getattr(slot, 2, &isnull)); Assert(!isnull); @@ -1015,7 +1121,7 @@ fetch_remote_table_info(char *nspname, char *relname, ExecDropSingleTupleTableSlot(slot); lrel->natts = natt; - + *remotegenlist_res = remotegenlist; walrcv_clear_result(res); /* @@ -1037,7 +1143,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 +1229,13 @@ copy_table(Relation rel) List *attnamelist; ParseState *pstate; List *options = NIL; + bool *remotegenlist; + 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), &remotegenlist, + &lrel, &qual); /* Put the relation into relmap. */ logicalrep_relmap_update(&lrel); @@ -1135,11 +1244,29 @@ copy_table(Relation rel) relmapentry = logicalrep_rel_open(lrel.remoteid, NoLock); Assert(rel == relmapentry->localrel); + attnamelist = make_copy_attnamelist(relmapentry, remotegenlist); + /* Start copy on the publisher. */ initStringInfo(&cmd); - /* Regular table with no row filter */ - if (lrel.relkind == RELKIND_RELATION && qual == NIL) + /* + * Check if the remote table has any generated columns that should be + * copied. + */ + for (int i = 0; i < relmapentry->remoterel.natts; i++) + { + if (remotegenlist[i]) + { + gencol_copy_needed = true; + break; + } + } + + /* + * 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 +1300,20 @@ 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 + * 'publish_generated_columns' is specified as true and the remote + * table has generated columns, 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 +1371,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..d953a1afce 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -766,7 +766,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (att->atttypid < FirstGenbkiObjectId) @@ -1008,6 +1008,37 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications, } } +/* + * Return a column list bitmap for the specified table. + * + * Generated columns are excluded. + */ +static Bitmapset * +prepare_nogen_columns_bms(PGOutputData *data, RelationSyncEntry *entry, + TupleDesc desc) +{ + Bitmapset *cols = NULL; + MemoryContext oldcxt = NULL; + + pgoutput_ensure_entry_cxt(data, entry); + oldcxt = MemoryContextSwitchTo(entry->entry_cxt); + + for (int i = 0; i < desc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(desc, i); + + /* Skip if the attribute is dropped or generated */ + if (att->attisdropped || att->attgenerated) + continue; + + cols = bms_add_member(cols, i + 1); + } + + MemoryContextSwitchTo(oldcxt); + + return cols; +} + /* * Initialize the column list. */ @@ -1042,13 +1073,18 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, Bitmapset *cols = NULL; /* - * If the publication is FOR ALL TABLES then it is treated the same as - * if there are no column lists (even if other publications have a - * list). + * Process potential column lists for the following cases: a. Any + * publication that is not FOR ALL TABLES. b. When the publication is + * FOR ALL TABLES and 'publish_generated_columns' is false. FOR ALL + * TABLES publication doesn't have user-defined column lists, so all + * columns will be replicated by default. However, if + * 'publish_generated_columns' is set to false, column lists must + * still be created to exclude any generated columns from being + * published. */ - if (!pub->alltables) + if (!(pub->alltables && pub->pubgencols)) { - bool pub_no_list = true; + bool pub_rel_has_collist = false; /* * Check for the presence of a column list in this publication. @@ -1063,47 +1099,53 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, if (HeapTupleIsValid(cftuple)) { + bool pub_no_list = true; + /* Lookup the column list attribute. */ cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple, Anum_pg_publication_rel_prattrs, &pub_no_list); - /* Build the column list bitmap in the per-entry context. */ - if (!pub_no_list) /* when not null */ - { - int i; - int nliveatts = 0; - TupleDesc desc = RelationGetDescr(relation); + pub_rel_has_collist = !pub_no_list; + } - pgoutput_ensure_entry_cxt(data, entry); + /* Build the column list bitmap in the per-entry context. */ + if (pub_rel_has_collist || !pub->pubgencols) + { + int nliveatts = 0; + TupleDesc desc = RelationGetDescr(relation); - cols = pub_collist_to_bitmapset(cols, cfdatum, - entry->entry_cxt); + pgoutput_ensure_entry_cxt(data, entry); - /* Get the number of live attributes. */ - for (i = 0; i < desc->natts; i++) - { - Form_pg_attribute att = TupleDescAttr(desc, i); + if (pub_rel_has_collist) + cols = pub_collist_to_bitmapset(cols, cfdatum, entry->entry_cxt); + else + cols = prepare_nogen_columns_bms(data, entry, desc); - if (att->attisdropped || att->attgenerated) - continue; + /* Get the number of live attributes. */ + for (int i = 0; i < desc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(desc, i); - nliveatts++; - } + if (att->attisdropped) + continue; - /* - * 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; - } + nliveatts++; } - ReleaseSysCache(cftuple); + /* + * If column list includes all the columns of the table, set + * it to NULL. + */ + if (bms_num_members(cols) == nliveatts) + { + bms_free(cols); + cols = NULL; + } } + + if (HeapTupleIsValid(cftuple)) + ReleaseSysCache(cftuple); } if (first) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 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 6a36c91083..16cbef3693 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -6237,7 +6237,7 @@ listPublications(const char *pattern) PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, false, false, false, false, false, false, false}; + static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false}; if (pset.sversion < 100000) { @@ -6272,7 +6272,10 @@ listPublications(const char *pattern) appendPQExpBuffer(&buf, ",\n pubviaroot AS \"%s\"", gettext_noop("Via root")); - + if (pset.sversion >= 180000) + appendPQExpBuffer(&buf, + ",\n pubgencols AS \"%s\"", + gettext_noop("Generated columns")); appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6361,6 +6364,7 @@ describePublications(const char *pattern) PGresult *res; bool has_pubtruncate; bool has_pubviaroot; + bool has_pubgencols; PQExpBufferData title; printTableContent cont; @@ -6377,6 +6381,7 @@ describePublications(const char *pattern) has_pubtruncate = (pset.sversion >= 110000); has_pubviaroot = (pset.sversion >= 130000); + has_pubgencols = (pset.sversion >= 180000); initPQExpBuffer(&buf); @@ -6390,6 +6395,9 @@ describePublications(const char *pattern) if (has_pubviaroot) appendPQExpBufferStr(&buf, ", pubviaroot"); + if (has_pubgencols) + appendPQExpBufferStr(&buf, + ", pubgencols"); appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6441,6 +6449,8 @@ describePublications(const char *pattern) ncols++; if (has_pubviaroot) ncols++; + if (has_pubgencols) + ncols++; initPQExpBuffer(&title); printfPQExpBuffer(&title, _("Publication %s"), pubname); @@ -6455,6 +6465,8 @@ describePublications(const char *pattern) printTableAddHeader(&cont, gettext_noop("Truncates"), true, align); if (has_pubviaroot) printTableAddHeader(&cont, gettext_noop("Via root"), true, align); + if (has_pubgencols) + printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align); printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); @@ -6465,6 +6477,8 @@ describePublications(const char *pattern) printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); if (has_pubviaroot) printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); + if (has_pubgencols) + printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); if (!puballtables) { diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index b4efb127dc..41de3c93bb 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -3511,7 +3511,7 @@ match_previous_words(int pattern_id, COMPLETE_WITH("WITH ("); /* Complete "CREATE PUBLICATION [...] 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 " with "AS ON" */ diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h index d9518a58b0..849b3a0804 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; diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h index 0aa14ec4a2..158b444275 100644 --- a/src/include/catalog/pg_subscription.h +++ b/src/include/catalog/pg_subscription.h @@ -20,6 +20,7 @@ #include "access/xlogdefs.h" #include "catalog/genbki.h" #include "catalog/pg_subscription_d.h" +#include "lib/stringinfo.h" #include "nodes/pg_list.h" @@ -180,4 +181,7 @@ extern void DisableSubscription(Oid subid); extern int CountDBSubscriptions(Oid dbid); +extern void get_publications_str(List *publications, StringInfo dest, + bool quote_literal); + #endif /* PG_SUBSCRIPTION_H */ diff --git a/src/include/replication/logicalrelation.h b/src/include/replication/logicalrelation.h index e687b40a56..8cdb7affbf 100644 --- a/src/include/replication/logicalrelation.h +++ b/src/include/replication/logicalrelation.h @@ -41,7 +41,8 @@ typedef struct LogicalRepRelMapEntry extern void logicalrep_relmap_update(LogicalRepRelation *remoterel); extern void logicalrep_partmap_reset_relmap(LogicalRepRelation *remoterel); - +extern int logicalrep_rel_att_by_name(LogicalRepRelation *remoterel, + const char *attname); extern LogicalRepRelMapEntry *logicalrep_rel_open(LogicalRepRelId remoteid, LOCKMODE lockmode); extern LogicalRepRelMapEntry *logicalrep_partition_open(LogicalRepRelMapEntry *root, 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..e419ca88d4 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,44 @@ 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); +-- Generated columns in column list, when 'publish_generated_columns'=true +CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true); +-- Generated columns in column list, then set 'publication_generate_columns'=false +ALTER PUBLICATION pub2 SET (publish_generated_columns = false); +-- Remove generate columns from column list, when 'publish_generated_columns'=false +ALTER PUBLICATION pub2 SET TABLE gencols(a); +-- Add generated columns in column list, when 'publish_generated_columns'=false +ALTER PUBLICATION pub2 SET TABLE 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..9724ba3f0d 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,42 @@ 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); +-- Generated columns in column list, when 'publish_generated_columns'=true +CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true); + +-- Generated columns in column list, then set 'publication_generate_columns'=false +ALTER PUBLICATION pub2 SET (publish_generated_columns = false); + +-- Remove generate columns from column list, when 'publish_generated_columns'=false +ALTER PUBLICATION pub2 SET TABLE gencols(a); + +-- Add generated columns in column list, when 'publish_generated_columns'=false +ALTER PUBLICATION pub2 SET TABLE 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/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl index 9a97fa5020..2480aa4f14 100644 --- a/src/test/subscription/t/031_column_list.pl +++ b/src/test/subscription/t/031_column_list.pl @@ -1202,9 +1202,9 @@ $result = $node_publisher->safe_psql( is( $result, qq(t t), 'check the number of columns in the old tuple'); -# TEST: Generated and dropped columns are not considered for the column list. +# TEST: Dropped columns are not considered for the column list. # So, the publication having a column list except for those columns and a -# publication without any column (aka all columns as part of the columns +# publication without any column list (aka all columns as part of the column # list) are considered to have the same column list. $node_publisher->safe_psql( 'postgres', qq( -- 2.41.0.windows.3