From ac2435bb68ae594c7fc4a5a44f2b88d167cfafb4 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Mon, 4 Nov 2024 15:10:17 +0530 Subject: [PATCH v48 1/2] Enable support for 'publish_generated_columns' parameter. This patch introduces support for the replication of generated column data alongside regular column changes by adding a 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 (not changed by this patch). * 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 | 8 +- doc/src/sgml/protocol.sgml | 2 +- doc/src/sgml/ref/create_publication.sgml | 12 + src/backend/catalog/pg_publication.c | 69 ++- src/backend/commands/publicationcmds.c | 33 +- src/backend/replication/logical/proto.c | 69 +-- src/backend/replication/pgoutput/pgoutput.c | 172 +++++-- src/bin/pg_dump/pg_dump.c | 41 +- 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 | 7 + src/include/replication/logicalproto.h | 21 +- src/test/regress/expected/psql.out | 6 +- src/test/regress/expected/publication.out | 504 ++++++++++++-------- src/test/regress/sql/publication.sql | 42 ++ 17 files changed, 686 insertions(+), 332 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index f02f67d7b8..898b6ddc8d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -514,9 +514,11 @@ CREATE TABLE people ( - Generated columns can be replicated during logical replication by - including them in the column list of the - CREATE PUBLICATION command. + Generated columns are allowed to be replicated during logical replication + according to the CREATE PUBLICATION parameter + + publish_generated_columns or by including them + in the column list of the CREATE PUBLICATION command. diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 71b6b2a535..4c0a1a0068 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -7477,7 +7477,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" - Next, one of the following submessages appears for each column: + Next, one of the following submessages appears for each published column: diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index d2cac06fd7..54acc2d356 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -223,6 +223,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 17a6093d06..77e3665657 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -225,6 +225,39 @@ filter_partitions(List *table_infos) } } +/* + * Returns true if the relation has column list associated with the publication, + * false otherwise. + */ +bool +has_column_list_defined(Publication *pub, Oid relid) +{ + HeapTuple cftuple = NULL; + + if (pub->alltables) + return false; + + cftuple = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)); + if (HeapTupleIsValid(cftuple)) + { + bool isnull = true; + + /* Lookup the column list attribute. */ + (void) SysCacheGetAttr(PUBLICATIONRELMAP, cftuple, + Anum_pg_publication_rel_prattrs, + &isnull); + ReleaseSysCache(cftuple); + + /* Was a column list found? */ + if (!isnull) + return true; + } + + return false; +} + /* * Returns true if any schema is associated with the publication, false if no * schema is associated with the publication. @@ -573,6 +606,39 @@ pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols, MemoryContext mcxt) return result; } +/* + * Returns a bitmap representing the columns of the specified table. + * + * Generated columns are included if include_gencols is true. + * + * If mcxt isn't NULL, build the bitmapset in that context. + */ +Bitmapset * +pub_form_cols_map(Relation relation, bool include_gencols, MemoryContext mcxt) +{ + MemoryContext oldcxt = NULL; + Bitmapset *result = NULL; + TupleDesc desc = RelationGetDescr(relation); + + if (mcxt) + oldcxt = MemoryContextSwitchTo(mcxt); + + for (int i = 0; i < desc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(desc, i); + + if (att->attisdropped || (att->attgenerated && !include_gencols)) + continue; + + result = bms_add_member(result, att->attnum); + } + + if (mcxt) + MemoryContextSwitchTo(oldcxt); + + return result; +} + /* * Insert new publication / schema mapping. */ @@ -998,6 +1064,7 @@ GetPublication(Oid pubid) pub->pubactions.pubdelete = pubform->pubdelete; pub->pubactions.pubtruncate = pubform->pubtruncate; pub->pubviaroot = pubform->pubviaroot; + pub->pubgencols = pubform->pubgencols; ReleaseSysCache(tup); @@ -1205,7 +1272,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 ac4af53feb..2c8fbc593a 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 include_gencols); static void logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, - bool binary, Bitmapset *columns); + bool binary, Bitmapset *columns, + bool include_gencols); static void logicalrep_read_attrs(StringInfo in, LogicalRepRelation *rel); static void logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple); @@ -399,7 +400,8 @@ logicalrep_read_origin(StringInfo in, XLogRecPtr *origin_lsn) */ void logicalrep_write_insert(StringInfo out, TransactionId xid, Relation rel, - TupleTableSlot *newslot, bool binary, Bitmapset *columns) + TupleTableSlot *newslot, bool binary, + Bitmapset *columns, bool include_gencols) { pq_sendbyte(out, LOGICAL_REP_MSG_INSERT); @@ -411,7 +413,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, include_gencols); } /* @@ -444,7 +446,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 include_gencols) { pq_sendbyte(out, LOGICAL_REP_MSG_UPDATE); @@ -465,11 +467,12 @@ logicalrep_write_update(StringInfo out, TransactionId xid, Relation rel, pq_sendbyte(out, 'O'); /* old tuple follows */ else pq_sendbyte(out, 'K'); /* old key follows */ - logicalrep_write_tuple(out, rel, oldslot, binary, columns); + logicalrep_write_tuple(out, rel, oldslot, binary, columns, + include_gencols); } pq_sendbyte(out, 'N'); /* new tuple follows */ - logicalrep_write_tuple(out, rel, newslot, binary, columns); + logicalrep_write_tuple(out, rel, newslot, binary, columns, include_gencols); } /* @@ -519,7 +522,7 @@ logicalrep_read_update(StringInfo in, bool *has_oldtuple, void logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel, TupleTableSlot *oldslot, bool binary, - Bitmapset *columns) + Bitmapset *columns, bool include_gencols) { Assert(rel->rd_rel->relreplident == REPLICA_IDENTITY_DEFAULT || rel->rd_rel->relreplident == REPLICA_IDENTITY_FULL || @@ -539,7 +542,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, include_gencols); } /* @@ -655,7 +658,7 @@ logicalrep_write_message(StringInfo out, TransactionId xid, XLogRecPtr lsn, */ void logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel, - Bitmapset *columns) + Bitmapset *columns, bool include_gencols) { char *relname; @@ -677,7 +680,7 @@ logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel, pq_sendbyte(out, rel->rd_rel->relreplident); /* send the attribute info */ - logicalrep_write_attrs(out, rel, columns); + logicalrep_write_attrs(out, rel, columns, include_gencols); } /* @@ -754,7 +757,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 include_gencols) { TupleDesc desc; Datum *values; @@ -768,7 +771,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (!logicalrep_should_publish_column(att, columns)) + if (!logicalrep_should_publish_column(att, columns, include_gencols)) continue; nliveatts++; @@ -786,7 +789,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, Form_pg_type typclass; Form_pg_attribute att = TupleDescAttr(desc, i); - if (!logicalrep_should_publish_column(att, columns)) + if (!logicalrep_should_publish_column(att, columns, include_gencols)) continue; if (isnull[i]) @@ -904,7 +907,8 @@ logicalrep_read_tuple(StringInfo in, LogicalRepTupleData *tuple) * Write relation attribute metadata to the stream. */ static void -logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) +logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns, + bool include_gencols) { TupleDesc desc; int i; @@ -919,7 +923,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) { Form_pg_attribute att = TupleDescAttr(desc, i); - if (!logicalrep_should_publish_column(att, columns)) + if (!logicalrep_should_publish_column(att, columns, include_gencols)) continue; nliveatts++; @@ -937,7 +941,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) Form_pg_attribute att = TupleDescAttr(desc, i); uint8 flags = 0; - if (!logicalrep_should_publish_column(att, columns)) + if (!logicalrep_should_publish_column(att, columns, include_gencols)) continue; /* REPLICA IDENTITY FULL means all columns are sent as part of key. */ @@ -1248,29 +1252,30 @@ logicalrep_message_type(LogicalRepMsgType action) /* * Check if the column 'att' of a table should be published. * - * 'columns' represents the column list specified for that table in the - * publication. + * 'columns' represents the publication column list (if any) for that table. * - * Note that generated columns can be present only in 'columns' list. + * 'include_gencols' flag indicates whether generated columns should be + * published when there is no column list. Typically, this will have the same + * value as the 'publish_generated_columns' publication parameter. + * + * Note that generated columns can be published only when present in a + * publication column list, or when include_gencols is true. */ bool -logicalrep_should_publish_column(Form_pg_attribute att, Bitmapset *columns) +logicalrep_should_publish_column(Form_pg_attribute att, Bitmapset *columns, + bool include_gencols) { if (att->attisdropped) return false; - /* - * Skip publishing generated columns if they are not included in the - * column list. - */ - if (!columns && att->attgenerated) - return false; + /* If a column list is provided, publish only the cols in that list. */ + if (columns) + return bms_is_member(att->attnum, columns); /* - * Check if a column is covered by a column list. + * If no column list is provided, generated columns will be published only + * if include_gencols is true, while all non-generated columns will always + * be published. */ - if (columns && !bms_is_member(att->attnum, columns)) - return false; - - return true; + return att->attgenerated ? include_gencols : true; } diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 12c1735906..386b087f79 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,13 @@ typedef struct RelationSyncEntry */ Bitmapset *columns; + /* + * Include generated columns for publication is set to true if + * 'publish_generated_columns' parameter is true, and the relation + * contains generated columns. + */ + bool include_gencols; + /* * Private context to store additional data for this entry - state for the * row filter expressions, column list, etc. @@ -213,6 +217,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 +738,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 +756,11 @@ 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; + bool include_gencols = relentry->include_gencols; int i; /* @@ -766,7 +775,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (!logicalrep_should_publish_column(att, columns)) + if (!logicalrep_should_publish_column(att, columns, include_gencols)) continue; if (att->atttypid < FirstGenbkiObjectId) @@ -778,7 +787,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid, } OutputPluginPrepareWrite(ctx, false); - logicalrep_write_rel(ctx->out, xid, relation, columns); + logicalrep_write_rel(ctx->out, xid, relation, columns, include_gencols); OutputPluginWrite(ctx, false); } @@ -1004,6 +1013,66 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications, } } +/* + * If the table contains a generated column, check for any conflicting + * values of 'publish_generated_columns' parameter in the publications. + */ +static void +check_and_init_gencol(PGOutputData *data, List *publications, + RelationSyncEntry *entry) +{ + Relation relation = RelationIdGetRelation(entry->publish_as_relid); + TupleDesc desc = RelationGetDescr(relation); + bool gencolpresent = false; + 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 are no generated columns to be published. */ + if (!gencolpresent) + { + entry->include_gencols = false; + return; + } + + /* + * There may be a conflicting value for 'publish_generated_columns' + * parameter in the publications. + */ + foreach_ptr(Publication, pub, publications) + { + /* + * The column list takes precedence over 'publish_generated_columns' + * parameter. Those will be checked later, see + * pgoutput_column_list_init. + */ + if (has_column_list_defined(pub, entry->publish_as_relid)) + continue; + + if (first) + { + entry->include_gencols = pub->pubgencols; + first = false; + } + else if (entry->include_gencols != 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. */ @@ -1014,6 +1083,8 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, ListCell *lc; bool first = true; Relation relation = RelationIdGetRelation(entry->publish_as_relid); + bool found_pub_with_collist = false; + Bitmapset *relcols = NULL; /* * Find if there are any column lists for this relation. If there are, @@ -1028,7 +1099,6 @@ 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". */ foreach(lc, publications) { @@ -1067,55 +1137,42 @@ 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); - bool att_gen_present = false; - pgoutput_ensure_entry_cxt(data, entry); + found_pub_with_collist = true; cols = pub_collist_to_bitmapset(cols, 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) - continue; - - if (att->attgenerated) - { - /* - * Generated cols are skipped unless they are - * present in a column list. - */ - if (!bms_is_member(att->attnum, cols)) - continue; - - att_gen_present = true; - } - - nliveatts++; - } - - /* - * Generated attributes are published only when they are - * present in the column list. Otherwise, a NULL column - * list means publish all columns. - */ - if (!att_gen_present && bms_num_members(cols) == nliveatts) - { - bms_free(cols); - cols = NULL; - } } ReleaseSysCache(cftuple); } } + /* + * For non-column list publications — e.g. TABLE (without a column + * list), ALL TABLES, or ALL TABLES IN SCHEMA, we consider all columns + * of the table (including generated columns when + * 'publish_generated_columns' parameter is true). + */ + if (!cols) + { + /* + * For the first publication with no specified column list, we + * retrieve and cache the table columns. This allows comparison + * with the column lists of other publications to detect any + * differences. Columns are retrieved only when there is more than + * one publication, as differences can only arise in that case. + */ + if (!relcols && (list_length(publications) > 1)) + { + pgoutput_ensure_entry_cxt(data, entry); + relcols = pub_form_cols_map(relation, entry->include_gencols, + entry->entry_cxt); + } + + cols = relcols; + } + if (first) { entry->columns = cols; @@ -1129,6 +1186,13 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, RelationGetRelationName(relation))); } /* loop all subscribed publications */ + /* + * If no column list publications exist, columns will be selected later + * according to the 'publish_generated_columns' parameter. + */ + if (!found_pub_with_collist) + entry->columns = NULL; + RelationClose(relation); } @@ -1541,15 +1605,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->include_gencols); 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->include_gencols); break; case REORDER_BUFFER_CHANGE_DELETE: logicalrep_write_delete(ctx->out, xid, targetrel, old_slot, - data->binary, relentry->columns); + data->binary, relentry->columns, + relentry->include_gencols); break; default: Assert(false); @@ -2223,6 +2290,9 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) /* Initialize the row filter */ pgoutput_row_filter_init(data, rel_publications, entry); + /* Check whether to publish generated columns. */ + check_and_init_gencol(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 d8c6330732..e8628e1f2a 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,24 +4292,26 @@ getPublications(Archive *fout) query = createPQExpBuffer(); /* Get the publications. */ + appendPQExpBufferStr(query, "SELECT p.tableoid, p.oid, p.pubname, " + "p.pubowner, p.puballtables, p.pubinsert, " + "p.pubupdate, p.pubdelete, "); + + if (fout->remoteVersion >= 110000) + appendPQExpBufferStr(query, "p.pubtruncate, "); + else + appendPQExpBufferStr(query, "false AS pubtruncate, "); + 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 " - "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 " - "FROM pg_publication p"); + appendPQExpBufferStr(query, "p.pubviaroot, "); 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 " - "FROM pg_publication p"); + appendPQExpBufferStr(query, "false AS pubviaroot, "); + + if (fout->remoteVersion >= 180000) + appendPQExpBufferStr(query, "p.pubgencols "); + else + appendPQExpBufferStr(query, "false AS pubgencols "); + + appendPQExpBufferStr(query, "FROM pg_publication p"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -4327,6 +4330,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 +4355,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 +4438,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 ac60829d68..213904440f 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 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 */ 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 [...] 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..b24950c502 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 has_column_list_defined(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); @@ -158,5 +163,7 @@ extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid, extern Bitmapset *pub_collist_to_bitmapset(Bitmapset *columns, Datum pubcols, MemoryContext mcxt); +extern Bitmapset *pub_form_cols_map(Relation relation, bool include_gencols, + MemoryContext mcxt); #endif /* PG_PUBLICATION_H */ diff --git a/src/include/replication/logicalproto.h b/src/include/replication/logicalproto.h index b219f22655..fe8583d1b6 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 include_gencols); 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 include_gencols); extern LogicalRepRelId logicalrep_read_update(StringInfo in, bool *has_oldtuple, LogicalRepTupleData *oldtup, LogicalRepTupleData *newtup); extern void logicalrep_write_delete(StringInfo out, TransactionId xid, Relation rel, TupleTableSlot *oldslot, - bool binary, Bitmapset *columns); + bool binary, Bitmapset *columns, + bool include_gencols); 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 include_gencols); extern LogicalRepRelation *logicalrep_read_rel(StringInfo in); extern void logicalrep_write_typ(StringInfo out, TransactionId xid, Oid typoid); @@ -271,6 +273,7 @@ extern void logicalrep_read_stream_abort(StringInfo in, bool read_abort_info); extern const char *logicalrep_message_type(LogicalRepMsgType action); extern bool logicalrep_should_publish_column(Form_pg_attribute att, - Bitmapset *columns); + Bitmapset *columns, + bool include_gencols); #endif /* LOGICAL_PROTO_H */ 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 d2ed1efc3b..43b482706c 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: @@ -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 12aea71c0f..48e68bcca2 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 @@ -1111,7 +1113,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; -- 2.34.1