From 4085431c9a4ece1e2b55f95c4c7f98fa1dcf3b59 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Mon, 4 Nov 2024 15:10:17 +0530 Subject: [PATCH v50 1/2] Replicate generated columns when 'publish_generated_columns' is set. This patch builds on the work done in commit 745217a051 by enabling the replication of generated columns alongside regular column changes through a new publication parameter: publish_generated_columns. Example usage: CREATE PUBLICATION pub1 FOR TABLE tab_gencol WITH (publish_generated_columns = true); The column list takes precedence. If the generated columns are specified in the column list, they will be replicated even if 'publish_generated_columns' is set to false. Conversely, if generated columns are not included in the column list (assuming the user specifies a column list), they will not be replicated even if 'publish_generated_columns' is true. There is a change in 'pg_publication' catalog so we need to bump the catversion. Author: Vignesh C, Shubham Khanna Reviewed-by: Peter Smith, Amit Kapila, Hayato Kuroda, Shlok Kyal, Ajin Cherian, Hou Zhijie, Masahiko Sawada Discussion: https://postgr.es/m/B80D17B2-2C8E-4C7D-87F2-E5B4BE3C069E@gmail.com --- doc/src/sgml/ddl.sgml | 8 +- doc/src/sgml/protocol.sgml | 2 +- doc/src/sgml/ref/create_publication.sgml | 20 + src/backend/catalog/pg_publication.c | 73 ++- src/backend/commands/publicationcmds.c | 33 +- src/backend/replication/logical/proto.c | 69 +-- src/backend/replication/pgoutput/pgoutput.c | 201 ++++---- 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 | 20 +- 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, 694 insertions(+), 368 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..f8e217d661 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -189,6 +189,26 @@ 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. + + + + + If the subscriber is from a release prior to 18, then initial table + synchronization won't copy generated columns even if parameter + publish_generated_columns is true in the + publisher. + + + + + publish_via_partition_root (boolean) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 17a6093d06..09e2dbdd10 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -256,6 +256,52 @@ is_schema_publication(Oid pubid) return result; } +/* + * Returns true if the relation has column list associated with the + * publication, false otherwise. + * + * If a column list is found, the corresponding bitmap is returned through the + * cols parameter, if provided. The bitmap is constructed within the given + * memory context (mcxt). + */ +bool +check_and_fetch_column_list(Publication *pub, Oid relid, MemoryContext mcxt, + Bitmapset **cols) +{ + HeapTuple cftuple; + bool found = false; + + if (pub->alltables) + return false; + + cftuple = SearchSysCache2(PUBLICATIONRELMAP, + ObjectIdGetDatum(relid), + ObjectIdGetDatum(pub->oid)); + if (HeapTupleIsValid(cftuple)) + { + Datum cfdatum; + bool isnull; + + /* Lookup the column list attribute. */ + cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple, + Anum_pg_publication_rel_prattrs, &isnull); + + /* Was a column list found? */ + if (!isnull) + { + /* Build the column list bitmap in the given memory context. */ + if (cols) + *cols = pub_collist_to_bitmapset(*cols, cfdatum, mcxt); + + found = true; + } + + ReleaseSysCache(cftuple); + } + + return found; +} + /* * Gets the relations based on the publication partition option for a specified * relation. @@ -573,6 +619,30 @@ 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. + */ +Bitmapset * +pub_form_cols_map(Relation relation, bool include_gencols) +{ + Bitmapset *result = NULL; + TupleDesc desc = RelationGetDescr(relation); + + 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); + } + + return result; +} + /* * Insert new publication / schema mapping. */ @@ -998,6 +1068,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 +1276,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..2c2085b2f9 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,26 @@ 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; - - /* - * Check if a column is covered by a column list. - */ - if (columns && !bms_is_member(att->attnum, columns)) - return false; + /* If a column list is provided, publish only the cols in that list. */ + if (columns) + return bms_is_member(att->attnum, columns); - return true; + /* All non-generated columns are always published. */ + return att->attgenerated ? include_gencols : true; } diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 12c1735906..a6002b223d 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); @@ -129,6 +126,12 @@ typedef struct RelationSyncEntry bool replicate_valid; /* overall validity flag for entry */ bool schema_sent; + + /* + * This is set if the 'publish_generated_columns' parameter is true, and + * the relation contains generated columns. + */ + bool include_gencols; List *streamed_txns; /* streamed toplevel transactions with this * schema */ @@ -213,6 +216,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 +737,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 +755,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 +774,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 +786,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 +1012,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 the + * 'publish_generated_columns' parameter. Those will be checked later, + * see pgoutput_column_list_init. + */ + if (check_and_fetch_column_list(pub, entry->publish_as_relid, NULL, NULL)) + 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 +1082,10 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, ListCell *lc; bool first = true; Relation relation = RelationIdGetRelation(entry->publish_as_relid); + bool found_pub_collist = false; + Bitmapset *relcols = NULL; + + pgoutput_ensure_entry_cxt(data, entry); /* * Find if there are any column lists for this relation. If there are, @@ -1027,93 +1099,39 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, * fetch_table_list. But one can later change the publication so we still * 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) { Publication *pub = lfirst(lc); - HeapTuple cftuple = NULL; - Datum cfdatum = 0; Bitmapset *cols = NULL; + /* Retrieve the bitmap of columns for a column list publication. */ + found_pub_collist |= check_and_fetch_column_list(pub, + entry->publish_as_relid, + entry->entry_cxt, &cols); + /* - * 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). + * 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 (!pub->alltables) + if (!cols) { - bool pub_no_list = true; - /* - * Check for the presence of a column list in this publication. - * - * Note: If we find no pg_publication_rel row, it's a publication - * defined for a whole schema, so it can't have a column list, - * just like a FOR ALL TABLES publication. + * Cache the table columns for the first publication with no + * specified column list to detect publication with a different + * column list. */ - cftuple = SearchSysCache2(PUBLICATIONRELMAP, - ObjectIdGetDatum(entry->publish_as_relid), - ObjectIdGetDatum(pub->oid)); - - if (HeapTupleIsValid(cftuple)) + if (!relcols && (list_length(publications) > 1)) { - /* 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); - bool att_gen_present = false; - - pgoutput_ensure_entry_cxt(data, entry); - - cols = pub_collist_to_bitmapset(cols, cfdatum, - entry->entry_cxt); + MemoryContext oldcxt = MemoryContextSwitchTo(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); + relcols = pub_form_cols_map(relation, entry->include_gencols); + MemoryContextSwitchTo(oldcxt); } + + cols = relcols; } if (first) @@ -1129,6 +1147,13 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, RelationGetRelationName(relation))); } /* loop all subscribed publications */ + /* + * If no column list publications exist, columns to be published will be + * computed later according to the 'publish_generated_columns' parameter. + */ + if (!found_pub_collist) + entry->columns = NULL; + RelationClose(relation); } @@ -1541,15 +1566,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); @@ -2000,6 +2028,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) { entry->replicate_valid = false; entry->schema_sent = false; + entry->include_gencols = false; entry->streamed_txns = NIL; entry->pubactions.pubinsert = entry->pubactions.pubupdate = entry->pubactions.pubdelete = entry->pubactions.pubtruncate = false; @@ -2052,6 +2081,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation) * earlier definition. */ entry->schema_sent = false; + entry->include_gencols = false; list_free(entry->streamed_txns); entry->streamed_txns = NIL; bms_free(entry->columns); @@ -2223,6 +2253,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..215ad9ef0b 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) { @@ -6264,6 +6264,10 @@ listPublications(const char *pattern) appendPQExpBuffer(&buf, ",\n pubtruncate AS \"%s\"", gettext_noop("Truncates")); + if (pset.sversion >= 180000) + appendPQExpBuffer(&buf, + ",\n pubgencols AS \"%s\"", + gettext_noop("Generated columns")); if (pset.sversion >= 130000) appendPQExpBuffer(&buf, ",\n pubviaroot AS \"%s\"", @@ -6356,6 +6360,7 @@ describePublications(const char *pattern) int i; PGresult *res; bool has_pubtruncate; + bool has_pubgencols; bool has_pubviaroot; PQExpBufferData title; @@ -6372,6 +6377,7 @@ describePublications(const char *pattern) } has_pubtruncate = (pset.sversion >= 110000); + has_pubgencols = (pset.sversion >= 180000); has_pubviaroot = (pset.sversion >= 130000); initPQExpBuffer(&buf); @@ -6383,9 +6389,13 @@ describePublications(const char *pattern) if (has_pubtruncate) appendPQExpBufferStr(&buf, ", pubtruncate"); + if (has_pubgencols) + appendPQExpBufferStr(&buf, + ", pubgencols"); if (has_pubviaroot) appendPQExpBufferStr(&buf, ", pubviaroot"); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6435,6 +6445,8 @@ describePublications(const char *pattern) if (has_pubtruncate) ncols++; + if (has_pubgencols) + ncols++; if (has_pubviaroot) ncols++; @@ -6449,6 +6461,8 @@ describePublications(const char *pattern) printTableAddHeader(&cont, gettext_noop("Deletes"), true, align); if (has_pubtruncate) printTableAddHeader(&cont, gettext_noop("Truncates"), true, align); + if (has_pubgencols) + printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align); if (has_pubviaroot) printTableAddHeader(&cont, gettext_noop("Via root"), true, align); @@ -6459,8 +6473,10 @@ describePublications(const char *pattern) printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); if (has_pubtruncate) printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); - if (has_pubviaroot) + if (has_pubgencols) printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); + if (has_pubviaroot) + 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..9a83a72d6b 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,8 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors, extern bool is_publishable_relation(Relation rel); extern bool is_schema_publication(Oid pubid); +extern bool check_and_fetch_column_list(Publication *pub, Oid relid, + MemoryContext mcxt, Bitmapset **cols); extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri, bool if_not_exists); extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns); @@ -158,5 +164,6 @@ 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); #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..36dc31c16c 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 | Generated columns | Via root +------+-------+------------+---------+---------+---------+-----------+-------------------+---------- (0 rows) \dRs "no.such.subscription" diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index d2ed1efc3b..a8949ffc2c 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 | Generated columns | Via root +--------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | t | t | t | t | f | t 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +-------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +-----------------+---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | t | t | t | t | t | f +(1 row) + +CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns=0); +\dRp+ pub2 + Publication pub2 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | t | t | t | t | t | f +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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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 | Generated columns | Via root +--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- + 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