From 835aeb6c8136bf7576d200414bea564bf239de5d Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Mon, 30 Sep 2024 09:35:12 +1000 Subject: [PATCH v35] Enable support for 'publish_generated_columns' option. Currently generated column values are not replicated because it is assumed that the corresponding subscriber-side table will generate its own values for those columns. This commit supports the transmission of generated column information and data alongside regular table changes. This behaviour is controlled by a new PUBLICATION parameter ('publish_generated_columns'). Example usage of subscription option: CREATE PUBLICATION FOR TABLE tab_gencol WITH (publish_generated_columns = true); When 'publish_generated_columns' is false, generated columns are not replicated. But when generated columns are specified in PUBLICATION col-list, it is replicated even the 'publish_generated_columns' is false. There is a change in 'pg_publicataion' catalog so we need to bump the catversion. --- doc/src/sgml/ddl.sgml | 6 +- doc/src/sgml/protocol.sgml | 2 +- doc/src/sgml/ref/create_publication.sgml | 12 + src/backend/catalog/pg_publication.c | 17 +- src/backend/commands/publicationcmds.c | 36 ++- src/backend/replication/logical/proto.c | 8 +- src/backend/replication/pgoutput/pgoutput.c | 102 ++++-- src/bin/pg_dump/pg_dump.c | 21 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 10 + src/bin/psql/describe.c | 18 +- src/bin/psql/tab-complete.c | 2 +- src/include/catalog/pg_publication.h | 7 +- src/test/regress/expected/psql.out | 6 +- src/test/regress/expected/publication.out | 478 ++++++++++++++++------------ src/test/regress/sql/publication.sql | 38 ++- src/test/subscription/t/031_column_list.pl | 4 +- 17 files changed, 491 insertions(+), 277 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8ab0ddb..7b9c349 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -514,8 +514,10 @@ CREATE TABLE people ( - Generated columns are skipped for logical replication and cannot be - specified in a CREATE PUBLICATION column list. + Generated columns may be skipped during logical replication according to the + CREATE PUBLICATION parameter + + publish_generated_columns. diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 11b6456..56de72c 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -6543,7 +6543,7 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" Next, the following message part appears for each column included in - the publication (except generated columns): + the publication: diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index fd9c5de..cd20bd4 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -222,6 +222,18 @@ CREATE PUBLICATION name + + + publish_generated_columns (boolean) + + + Specifies whether the generated columns present in the tables + associated with the publication should be replicated. + The default is false. + + + + diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 7fe5fe2..583da09 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -420,7 +420,8 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri, check_publication_add_relation(targetrel); /* Validate and translate column names into a Bitmapset of attnums. */ - attnums = pub_collist_validate(pri->relation, pri->columns); + attnums = pub_collist_validate(pri->relation, pri->columns, + pub->pubgencols); /* Form a tuple. */ memset(values, 0, sizeof(values)); @@ -507,7 +508,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri, * corresponding attnums. */ Bitmapset * -pub_collist_validate(Relation targetrel, List *columns) +pub_collist_validate(Relation targetrel, List *columns, bool pubgencols) { Bitmapset *set = NULL; ListCell *lc; @@ -530,10 +531,10 @@ pub_collist_validate(Relation targetrel, List *columns) errmsg("cannot use system column \"%s\" in publication column list", colname)); - if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated) - ereport(ERROR, + if (TupleDescAttr(tupdesc, attnum - 1)->attgenerated && !pubgencols) + ereport(WARNING, errcode(ERRCODE_INVALID_COLUMN_REFERENCE), - errmsg("cannot use generated column \"%s\" in publication column list", + errmsg("specified generated column \"%s\" in publication column list when publish_generated_columns as false", colname)); if (bms_is_member(attnum, set)) @@ -1006,6 +1007,7 @@ GetPublication(Oid pubid) pub->pubactions.pubdelete = pubform->pubdelete; pub->pubactions.pubtruncate = pubform->pubtruncate; pub->pubviaroot = pubform->pubviaroot; + pub->pubgencols = pubform->pubgencols; ReleaseSysCache(tup); @@ -1214,7 +1216,10 @@ pg_get_publication_tables(PG_FUNCTION_ARGS) { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) + continue; + + if (att->attgenerated && !pub->pubgencols) continue; attnums[nattnums++] = att->attnum; diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index d6ffef3..8c09125 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -78,12 +78,15 @@ parse_publication_options(ParseState *pstate, bool *publish_given, PublicationActions *pubactions, bool *publish_via_partition_root_given, - bool *publish_via_partition_root) + bool *publish_via_partition_root, + bool *publish_generated_columns_given, + bool *publish_generated_columns) { ListCell *lc; *publish_given = false; *publish_via_partition_root_given = false; + *publish_generated_columns_given = false; /* defaults */ pubactions->pubinsert = true; @@ -91,6 +94,7 @@ parse_publication_options(ParseState *pstate, pubactions->pubdelete = true; pubactions->pubtruncate = true; *publish_via_partition_root = false; + *publish_generated_columns = false; /* Parse options */ foreach(lc, options) @@ -151,6 +155,13 @@ parse_publication_options(ParseState *pstate, *publish_via_partition_root_given = true; *publish_via_partition_root = defGetBoolean(defel); } + else if (strcmp(defel->defname, "publish_generated_columns") == 0) + { + if (*publish_generated_columns_given) + errorConflictingDefElem(defel, pstate); + *publish_generated_columns_given = true; + *publish_generated_columns = defGetBoolean(defel); + } else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -737,6 +748,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) PublicationActions pubactions; bool publish_via_partition_root_given; bool publish_via_partition_root; + bool publish_generated_columns_given; + bool publish_generated_columns; AclResult aclresult; List *relations = NIL; List *schemaidlist = NIL; @@ -776,7 +789,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) stmt->options, &publish_given, &pubactions, &publish_via_partition_root_given, - &publish_via_partition_root); + &publish_via_partition_root, + &publish_generated_columns_given, + &publish_generated_columns); puboid = GetNewOidWithIndex(rel, PublicationObjectIndexId, Anum_pg_publication_oid); @@ -793,6 +808,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt) BoolGetDatum(pubactions.pubtruncate); values[Anum_pg_publication_pubviaroot - 1] = BoolGetDatum(publish_via_partition_root); + values[Anum_pg_publication_pubgencols - 1] = + BoolGetDatum(publish_generated_columns); tup = heap_form_tuple(RelationGetDescr(rel), values, nulls); @@ -878,6 +895,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt, PublicationActions pubactions; bool publish_via_partition_root_given; bool publish_via_partition_root; + bool publish_generated_columns_given; + bool publish_generated_columns; ObjectAddress obj; Form_pg_publication pubform; List *root_relids = NIL; @@ -887,7 +906,9 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt, stmt->options, &publish_given, &pubactions, &publish_via_partition_root_given, - &publish_via_partition_root); + &publish_via_partition_root, + &publish_generated_columns_given, + &publish_generated_columns); pubform = (Form_pg_publication) GETSTRUCT(tup); @@ -997,6 +1018,12 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt, replaces[Anum_pg_publication_pubviaroot - 1] = true; } + if (publish_generated_columns_given) + { + values[Anum_pg_publication_pubgencols - 1] = BoolGetDatum(publish_generated_columns); + replaces[Anum_pg_publication_pubgencols - 1] = true; + } + tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls, replaces); @@ -1182,7 +1209,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup, * is cheap enough that that seems harmless. */ newcolumns = pub_collist_validate(newpubrel->relation, - newpubrel->columns); + newpubrel->columns, + pubform->pubgencols); /* * Check if any of the new set of relations matches with the diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c index 980f6e2..6b085e5 100644 --- a/src/backend/replication/logical/proto.c +++ b/src/backend/replication/logical/proto.c @@ -781,7 +781,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -802,7 +802,7 @@ logicalrep_write_tuple(StringInfo out, Relation rel, TupleTableSlot *slot, Form_pg_type typclass; Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -938,7 +938,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) @@ -959,7 +959,7 @@ logicalrep_write_attrs(StringInfo out, Relation rel, Bitmapset *columns) Form_pg_attribute att = TupleDescAttr(desc, i); uint8 flags = 0; - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (!column_in_column_list(att->attnum, columns)) diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c index 00e7024..24c56ed 100644 --- a/src/backend/replication/pgoutput/pgoutput.c +++ b/src/backend/replication/pgoutput/pgoutput.c @@ -766,7 +766,7 @@ send_relation_and_attrs(Relation relation, TransactionId xid, { Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) + if (att->attisdropped) continue; if (att->atttypid < FirstGenbkiObjectId) @@ -1009,6 +1009,36 @@ pgoutput_row_filter_init(PGOutputData *data, List *publications, } /* + * Prepare new column list bitmap. + * This encompasses all table columns, excluding the generated ones. + */ +static Bitmapset * +prepare_all_columns_bms(PGOutputData *data, RelationSyncEntry *entry, + TupleDesc desc) +{ + Bitmapset *cols = NULL; + MemoryContext oldcxt = NULL; + + pgoutput_ensure_entry_cxt(data, entry); + oldcxt = MemoryContextSwitchTo(entry->entry_cxt); + + for (int i = 0; i < desc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(desc, i); + + /* Skip if the attribute is dropped or generated */ + if (att->attisdropped || att->attgenerated) + continue; + + cols = bms_add_member(cols, i + 1); + } + + MemoryContextSwitchTo(oldcxt); + + return cols; +} + +/* * Initialize the column list. */ static void @@ -1042,13 +1072,14 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, Bitmapset *cols = NULL; /* - * If the publication is FOR ALL TABLES then it is treated the same as - * if there are no column lists (even if other publications have a - * list). + * To handle cases where the publish_generated_columns option isn't + * specified for all tables in a publication, the pubgencolumns check + * needs to be performed. In such cases, we must create a column list + * that excludes generated columns. */ - if (!pub->alltables) + if (!pub->alltables || !pub->pubgencols) { - bool pub_no_list = true; + bool pub_rel_has_collist = true; /* * Check for the presence of a column list in this publication. @@ -1066,44 +1097,47 @@ pgoutput_column_list_init(PGOutputData *data, List *publications, /* Lookup the column list attribute. */ cfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, cftuple, Anum_pg_publication_rel_prattrs, - &pub_no_list); + &pub_rel_has_collist); + } - /* Build the column list bitmap in the per-entry context. */ - if (!pub_no_list) /* when not null */ - { - int i; - int nliveatts = 0; - TupleDesc desc = RelationGetDescr(relation); + /* Build the column list bitmap in the per-entry context. */ + if (!pub_rel_has_collist || !pub->pubgencols) + { + int i; + int nliveatts = 0; + TupleDesc desc = RelationGetDescr(relation); - pgoutput_ensure_entry_cxt(data, entry); + pgoutput_ensure_entry_cxt(data, entry); - cols = pub_collist_to_bitmapset(cols, cfdatum, - entry->entry_cxt); + if (!pub_rel_has_collist) + cols = pub_collist_to_bitmapset(cols, cfdatum, entry->entry_cxt); + else + cols = prepare_all_columns_bms(data, entry, desc); - /* Get the number of live attributes. */ - for (i = 0; i < desc->natts; i++) - { - Form_pg_attribute att = TupleDescAttr(desc, i); + /* Get the number of live attributes. */ + for (i = 0; i < desc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(desc, i); - if (att->attisdropped || att->attgenerated) - continue; + if (att->attisdropped) + continue; - nliveatts++; - } + nliveatts++; + } - /* - * If column list includes all the columns of the table, - * set it to NULL. - */ - if (bms_num_members(cols) == nliveatts) - { - bms_free(cols); - cols = NULL; - } + /* + * If column list includes all the columns of the table, set + * it to NULL. + */ + if (bms_num_members(cols) == nliveatts) + { + bms_free(cols); + cols = NULL; } + } + if (HeapTupleIsValid(cftuple)) ReleaseSysCache(cftuple); - } } if (first) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 130b807..a0dad1e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4282,6 +4282,7 @@ getPublications(Archive *fout) int i_pubdelete; int i_pubtruncate; int i_pubviaroot; + int i_pubgencols; int i, ntups; @@ -4293,23 +4294,29 @@ getPublications(Archive *fout) resetPQExpBuffer(query); /* Get the publications. */ - if (fout->remoteVersion >= 130000) + if (fout->remoteVersion >= 180000) appendPQExpBufferStr(query, "SELECT p.tableoid, p.oid, p.pubname, " "p.pubowner, " - "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot " + "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot, p.pubgencols " + "FROM pg_publication p"); + else if (fout->remoteVersion >= 130000) + appendPQExpBufferStr(query, + "SELECT p.tableoid, p.oid, p.pubname, " + "p.pubowner, " + "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot, false AS pubviagencols " "FROM pg_publication p"); else if (fout->remoteVersion >= 110000) appendPQExpBufferStr(query, "SELECT p.tableoid, p.oid, p.pubname, " "p.pubowner, " - "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, false AS pubviaroot " + "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, false AS pubviaroot, false AS pubviagencols " "FROM pg_publication p"); else appendPQExpBufferStr(query, "SELECT p.tableoid, p.oid, p.pubname, " "p.pubowner, " - "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, false AS pubtruncate, false AS pubviaroot " + "p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, false AS pubtruncate, false AS pubviaroot, false AS pubviagencols " "FROM pg_publication p"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -4326,6 +4333,7 @@ getPublications(Archive *fout) i_pubdelete = PQfnumber(res, "pubdelete"); i_pubtruncate = PQfnumber(res, "pubtruncate"); i_pubviaroot = PQfnumber(res, "pubviaroot"); + i_pubgencols = PQfnumber(res, "pubgencols"); pubinfo = pg_malloc(ntups * sizeof(PublicationInfo)); @@ -4350,6 +4358,8 @@ getPublications(Archive *fout) (strcmp(PQgetvalue(res, i, i_pubtruncate), "t") == 0); pubinfo[i].pubviaroot = (strcmp(PQgetvalue(res, i, i_pubviaroot), "t") == 0); + pubinfo[i].pubgencols = + (strcmp(PQgetvalue(res, i, i_pubgencols), "t") == 0); /* Decide whether we want to dump it */ selectDumpableObject(&(pubinfo[i].dobj), fout); @@ -4429,6 +4439,9 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo) if (pubinfo->pubviaroot) appendPQExpBufferStr(query, ", publish_via_partition_root = true"); + if (pubinfo->pubgencols) + appendPQExpBufferStr(query, ", publish_generated_columns = true"); + appendPQExpBufferStr(query, ");\n"); if (pubinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 9f907ed..c1552ea 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 ab6c830..91a4c63 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 6a36c91..16cbef3 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -6237,7 +6237,7 @@ listPublications(const char *pattern) PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, false, false, false, false, false, false, false}; + static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false}; if (pset.sversion < 100000) { @@ -6272,7 +6272,10 @@ listPublications(const char *pattern) appendPQExpBuffer(&buf, ",\n pubviaroot AS \"%s\"", gettext_noop("Via root")); - + if (pset.sversion >= 180000) + appendPQExpBuffer(&buf, + ",\n pubgencols AS \"%s\"", + gettext_noop("Generated columns")); appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6361,6 +6364,7 @@ describePublications(const char *pattern) PGresult *res; bool has_pubtruncate; bool has_pubviaroot; + bool has_pubgencols; PQExpBufferData title; printTableContent cont; @@ -6377,6 +6381,7 @@ describePublications(const char *pattern) has_pubtruncate = (pset.sversion >= 110000); has_pubviaroot = (pset.sversion >= 130000); + has_pubgencols = (pset.sversion >= 180000); initPQExpBuffer(&buf); @@ -6390,6 +6395,9 @@ describePublications(const char *pattern) if (has_pubviaroot) appendPQExpBufferStr(&buf, ", pubviaroot"); + if (has_pubgencols) + appendPQExpBufferStr(&buf, + ", pubgencols"); appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6441,6 +6449,8 @@ describePublications(const char *pattern) ncols++; if (has_pubviaroot) ncols++; + if (has_pubgencols) + ncols++; initPQExpBuffer(&title); printfPQExpBuffer(&title, _("Publication %s"), pubname); @@ -6455,6 +6465,8 @@ describePublications(const char *pattern) printTableAddHeader(&cont, gettext_noop("Truncates"), true, align); if (has_pubviaroot) printTableAddHeader(&cont, gettext_noop("Via root"), true, align); + if (has_pubgencols) + printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align); printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); @@ -6465,6 +6477,8 @@ describePublications(const char *pattern) printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); if (has_pubviaroot) printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); + if (has_pubgencols) + printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); if (!puballtables) { diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index a7ccde6..ea36b18 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3182,7 +3182,7 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("WITH ("); /* Complete "CREATE PUBLICATION [...] WITH" */ else if (HeadMatches("CREATE", "PUBLICATION") && TailMatches("WITH", "(")) - COMPLETE_WITH("publish", "publish_via_partition_root"); + COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root"); /* CREATE RULE */ /* Complete "CREATE [ OR REPLACE ] RULE " with "AS ON" */ diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h index d9518a5..2a3816f 100644 --- a/src/include/catalog/pg_publication.h +++ b/src/include/catalog/pg_publication.h @@ -54,6 +54,9 @@ CATALOG(pg_publication,6104,PublicationRelationId) /* true if partition changes are published using root schema */ bool pubviaroot; + + /* true if generated columns data should be published */ + bool pubgencols; } FormData_pg_publication; /* ---------------- @@ -103,6 +106,7 @@ typedef struct Publication char *name; bool alltables; bool pubviaroot; + bool pubgencols; PublicationActions pubactions; } Publication; @@ -152,7 +156,8 @@ extern bool is_publishable_relation(Relation rel); extern bool is_schema_publication(Oid pubid); extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri, bool if_not_exists); -extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns); +extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns, + bool pubgencols); extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid, bool if_not_exists); diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 3819bf5..62e4820 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 660245e..a26b5a4 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -29,21 +29,27 @@ CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publis ERROR: conflicting or redundant options LINE 1: ...ub_xxx WITH (publish_via_partition_root = 'true', publish_vi... ^ +CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'true', publish_generated_columns = '0'); +ERROR: conflicting or redundant options +LINE 1: ...pub_xxx WITH (publish_generated_columns = 'true', publish_ge... + ^ +CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'foo'); +ERROR: publish_generated_columns requires a Boolean value \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+---------- - testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f - testpub_default | regress_publication_user | f | f | t | f | f | f + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------+--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f + testpub_default | regress_publication_user | f | f | t | f | f | f | f (2 rows) ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+---------- - testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f - testpub_default | regress_publication_user | f | t | t | t | f | f + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------+--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + testpib_ins_trunct | regress_publication_user | f | t | f | f | f | f | f + testpub_default | regress_publication_user | f | t | t | t | f | f | f (2 rows) --- adding tables @@ -87,10 +93,10 @@ RESET client_min_messages; -- should be able to add schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortable + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_tbl1" Tables from schemas: @@ -99,20 +105,20 @@ Tables from schemas: -- should be able to drop schema from 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortable + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_tbl1" -- should be able to set schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortable + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test" @@ -123,10 +129,10 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test; CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk; RESET client_min_messages; \dRp+ testpub_for_tbl_schema - Publication testpub_for_tbl_schema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_for_tbl_schema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -144,10 +150,10 @@ LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo; -- should be able to add a table of the same schema to the schema publication ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -156,10 +162,10 @@ Tables from schemas: -- should be able to drop the table ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test" @@ -170,10 +176,10 @@ ERROR: relation "testpub_nopk" is not part of the publication -- should be able to set table to schema publication ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test.testpub_nopk" @@ -195,10 +201,10 @@ Publications: "testpub_foralltables" \dRp+ testpub_foralltables - Publication testpub_foralltables - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | t | t | t | f | f | f + Publication testpub_foralltables + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | t | t | t | f | f | f | f (1 row) DROP TABLE testpub_tbl2; @@ -210,19 +216,19 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; RESET client_min_messages; \dRp+ testpub3 - Publication testpub3 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub3 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_tbl3" "public.testpub_tbl3a" \dRp+ testpub4 - Publication testpub4 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub4 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_tbl3" @@ -243,10 +249,10 @@ UPDATE testpub_parted1 SET a = 1; -- only parent is listed as being in publication, not the partition ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_forparted + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_parted" @@ -261,10 +267,10 @@ ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1; UPDATE testpub_parted1 SET a = 1; ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true); \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | t + Publication testpub_forparted + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | t | f Tables: "public.testpub_parted" @@ -293,10 +299,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub5 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -309,10 +315,10 @@ Tables: ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub5 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -328,10 +334,10 @@ Publications: ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub5 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) @@ -339,10 +345,10 @@ Tables: -- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression) ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub5 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500)) @@ -375,10 +381,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax1 - Publication testpub_syntax1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub_syntax1 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE (e < 999) @@ -388,10 +394,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax2 - Publication testpub_syntax2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | f | f + Publication testpub_syntax2 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | f | f | f Tables: "public.testpub_rf_tbl1" "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999) @@ -506,10 +512,10 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2; ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99); RESET client_min_messages; \dRp+ testpub6 - Publication testpub6 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub6 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99) Tables from schemas: @@ -687,9 +693,10 @@ UPDATE testpub_tbl5 SET a = 1; ERROR: cannot update table "testpub_tbl5" DETAIL: Column list used by the publication does not cover the replica identity. ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; --- error: generated column "d" can't be in list +-- ok: generated columns can be in the list too ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d); -ERROR: cannot use generated column "d" in publication column list +WARNING: specified generated column "d" in publication column list when publish_generated_columns as false +ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; -- error: system attributes "ctid" not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid); ERROR: cannot use system column "ctid" in publication column list @@ -730,10 +737,10 @@ CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate'); RESET client_min_messages; ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok \dRp+ testpub_table_ins - Publication testpub_table_ins - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | f | f | t | f + Publication testpub_table_ins + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | f | f | t | f | f Tables: "public.testpub_tbl5" (a) @@ -917,10 +924,10 @@ CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c)); ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey; ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1); \dRp+ testpub_both_filters - Publication testpub_both_filters - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_both_filters + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1) @@ -1125,10 +1132,10 @@ ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; ERROR: publication "testpub_fortbl" already exists \dRp+ testpub_fortbl - Publication testpub_fortbl - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortbl + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1166,10 +1173,10 @@ Publications: "testpub_fortbl" \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | f | f + Publication testpub_default + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | f | f | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1247,10 +1254,10 @@ REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; DROP TABLE testpub_tbl1; \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | f | f + Publication testpub_default + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | f | f | f (1 row) -- fail - must be owner of publication @@ -1260,20 +1267,20 @@ ERROR: must be owner of publication testpub_default RESET ROLE; ALTER PUBLICATION testpub_default RENAME TO testpub_foo; \dRp testpub_foo - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root --------------+--------------------------+------------+---------+---------+---------+-----------+---------- - testpub_foo | regress_publication_user | f | t | t | t | f | f + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +-------------+--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + testpub_foo | regress_publication_user | f | t | t | t | f | f | f (1 row) -- rename back to keep the rest simple ALTER PUBLICATION testpub_foo RENAME TO testpub_default; ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ------------------+---------------------------+------------+---------+---------+---------+-----------+---------- - testpub_default | regress_publication_user2 | f | t | t | t | f | f + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +-----------------+---------------------------+------------+---------+---------+---------+-----------+----------+------------------- + testpub_default | regress_publication_user2 | f | t | t | t | f | f | f (1 row) -- adding schemas and tables @@ -1289,19 +1296,19 @@ CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int); SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1315,44 +1322,44 @@ CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CUR CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub3_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "public" \dRp+ testpub4_forschema - Publication testpub4_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub4_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "CURRENT_SCHEMA" \dRp+ testpub5_forschema - Publication testpub5_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub5_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub6_forschema - Publication testpub6_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub6_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortable + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "CURRENT_SCHEMA.CURRENT_SCHEMA" @@ -1386,10 +1393,10 @@ ERROR: schema "testpub_view" does not exist -- dropping the schema should reflect the change in publication DROP SCHEMA pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1397,20 +1404,20 @@ Tables from schemas: -- renaming the schema should reflect the change in publication ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1_renamed" "pub_test2" ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub2_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1418,10 +1425,10 @@ Tables from schemas: -- alter publication add schema ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1430,10 +1437,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1442,10 +1449,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1; ERROR: schema "pub_test1" is already member of publication "testpub1_forschema" \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1453,10 +1460,10 @@ Tables from schemas: -- alter publication drop schema ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" @@ -1464,10 +1471,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; ERROR: tables from schema "pub_test2" are not part of the publication \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" @@ -1475,29 +1482,29 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" -- drop all schemas ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f (1 row) -- alter publication set multiple schema ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1506,10 +1513,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" "pub_test2" @@ -1518,10 +1525,10 @@ Tables from schemas: -- removing the duplicate schemas ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub1_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" @@ -1600,18 +1607,18 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub3_forschema; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub3_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f (1 row) ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub3_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables from schemas: "pub_test1" @@ -1621,20 +1628,20 @@ CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TA CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1; RESET client_min_messages; \dRp+ testpub_forschema_fortable - Publication testpub_forschema_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_forschema_fortable + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" \dRp+ testpub_fortable_forschema - Publication testpub_fortable_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ---------------------------+------------+---------+---------+---------+-----------+---------- - regress_publication_user | f | t | t | t | t | f + Publication testpub_fortable_forschema + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | f | t | t | t | t | f | f Tables: "pub_test2.tbl1" Tables from schemas: @@ -1749,6 +1756,53 @@ DROP PUBLICATION pub; DROP TABLE sch1.tbl1; DROP SCHEMA sch1 cascade; DROP SCHEMA sch2 cascade; +-- Test the publication 'publish_generated_columns' parameter enabled or disabled +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns=1); +\dRp+ pub1 + Publication pub1 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | t | t | t | t | t | f | t +(1 row) + +CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns=0); +\dRp+ pub2 + Publication pub2 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Generated columns +--------------------------+------------+---------+---------+---------+-----------+----------+------------------- + regress_publication_user | t | t | t | t | t | f | f +(1 row) + +RESET client_min_messages; +DROP PUBLICATION pub1; +DROP PUBLICATION pub2; +-- Test the publication 'publish_generated_columns' parameter enabled or disabled for different combinations. +SET client_min_messages = 'WARNING'; +CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); +-- gencols in column list with 'publish_generated_columns'=false. +CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false); +WARNING: specified generated column "gen1" in publication column list when publish_generated_columns as false +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +-- gencols in column list with 'publish_generated_columns'=true. +CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true); +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +-- ALTER PUBLICATION setting 'publication_generate_columns'=false. +ALTER PUBLICATION pub2 SET (publish_generated_columns = false); +-- No gencols in column list with 'publish_generated_columns'=false. +CREATE PUBLICATION pub3 WITH (publish_generated_columns=false); +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +-- ALTER PUBLICATION to add gencols to column list. +ALTER PUBLICATION pub3 ADD TABLE gencols(a, gen1); +WARNING: specified generated column "gen1" in publication column list when publish_generated_columns as false +DROP PUBLICATION pub1; +DROP PUBLICATION pub2; +DROP PUBLICATION pub3; +DROP TABLE gencols; +RESET client_min_messages; RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index f68a5b5..d7b43dc 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -24,6 +24,8 @@ ALTER PUBLICATION testpub_default SET (publish = update); CREATE PUBLICATION testpub_xxx WITH (foo); CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0'); +CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'true', publish_generated_columns = '0'); +CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = 'foo'); \dRp @@ -413,8 +415,9 @@ ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, x); ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (b, c); UPDATE testpub_tbl5 SET a = 1; ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; --- error: generated column "d" can't be in list +-- ok: generated columns can be in the list too ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, d); +ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl5; -- error: system attributes "ctid" not allowed in column list ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl5 (a, ctid); ALTER PUBLICATION testpub_fortable SET TABLE testpub_tbl1 (id, ctid); @@ -1110,6 +1113,39 @@ DROP TABLE sch1.tbl1; DROP SCHEMA sch1 cascade; DROP SCHEMA sch2 cascade; +-- Test the publication 'publish_generated_columns' parameter enabled or disabled +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns=1); +\dRp+ pub1 + +CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns=0); +\dRp+ pub2 + +RESET client_min_messages; +DROP PUBLICATION pub1; +DROP PUBLICATION pub2; + +-- Test the publication 'publish_generated_columns' parameter enabled or disabled for different combinations. +SET client_min_messages = 'WARNING'; +CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); +-- gencols in column list with 'publish_generated_columns'=false. +CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns=false); +-- gencols in column list with 'publish_generated_columns'=true. +CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns=true); +-- ALTER PUBLICATION setting 'publication_generate_columns'=false. +ALTER PUBLICATION pub2 SET (publish_generated_columns = false); + +-- No gencols in column list with 'publish_generated_columns'=false. +CREATE PUBLICATION pub3 WITH (publish_generated_columns=false); +-- ALTER PUBLICATION to add gencols to column list. +ALTER PUBLICATION pub3 ADD TABLE gencols(a, gen1); + +DROP PUBLICATION pub1; +DROP PUBLICATION pub2; +DROP PUBLICATION pub3; +DROP TABLE gencols; + +RESET client_min_messages; RESET SESSION AUTHORIZATION; DROP ROLE regress_publication_user, regress_publication_user2; DROP ROLE regress_publication_user_dummy; diff --git a/src/test/subscription/t/031_column_list.pl b/src/test/subscription/t/031_column_list.pl index 9a97fa5..2480aa4 100644 --- a/src/test/subscription/t/031_column_list.pl +++ b/src/test/subscription/t/031_column_list.pl @@ -1202,9 +1202,9 @@ $result = $node_publisher->safe_psql( is( $result, qq(t t), 'check the number of columns in the old tuple'); -# TEST: Generated and dropped columns are not considered for the column list. +# TEST: Dropped columns are not considered for the column list. # So, the publication having a column list except for those columns and a -# publication without any column (aka all columns as part of the columns +# publication without any column list (aka all columns as part of the column # list) are considered to have the same column list. $node_publisher->safe_psql( 'postgres', qq( -- 1.8.3.1