From 75b9338123b68eb96a06a96130cc6279ce7a0ab1 Mon Sep 17 00:00:00 2001 From: Jim Jones Date: Fri, 20 Feb 2026 09:52:27 +0100 Subject: [PATCH v2] Show comments in \dRp+, \dRs+, and \dX+ psql meta-commands. Previously, the psql meta-commands that list publications, subscriptions, and extended statistics did not display their associated comments, whereas other \d meta-commands did. This made it inconvenient for users to view these objects together with their descriptions. This commit improves \dRp+ and \dRs+ to include comments for publications and subscriptions. It also extends the \dX meta-command to accept the + option, allowing comments for extended statistics to be shown when requested. --- doc/src/sgml/ref/psql-ref.sgml | 4 +- src/bin/psql/command.c | 2 +- src/bin/psql/describe.c | 40 +- src/bin/psql/describe.h | 2 +- src/test/regress/expected/publication.out | 456 ++++++++++----------- src/test/regress/expected/stats_ext.out | 60 +-- src/test/regress/expected/subscription.out | 176 ++++---- 7 files changed, 378 insertions(+), 362 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8b1d948ba0..3550bda4d8 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -2182,7 +2182,7 @@ SELECT $1 \parse stmt1 - \dX[x] [ pattern ] + \dX[x+] [ pattern ] Lists extended statistics. @@ -2191,6 +2191,8 @@ SELECT $1 \parse stmt1 pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. + If + is appended to the command name, each object + is listed with its associated description. diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 213d48500d..3412b0e3d2 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -1277,7 +1277,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listExtensions(pattern); break; case 'X': /* Extended Statistics */ - success = listExtendedStats(pattern); + success = listExtendedStats(pattern, show_verbose); break; case 'y': /* Event Triggers */ success = listEventTriggers(pattern, show_verbose); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 571a6a003d..d44aa60db5 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -4890,7 +4890,7 @@ listEventTriggers(const char *pattern, bool verbose) * Describes extended statistics. */ bool -listExtendedStats(const char *pattern) +listExtendedStats(const char *pattern, bool verbose) { PQExpBufferData buf; PGresult *res; @@ -4951,6 +4951,11 @@ listExtendedStats(const char *pattern) gettext_noop("MCV")); } + if (verbose) + appendPQExpBuffer(&buf, + ", \npg_catalog.obj_description(oid, 'pg_statistic_ext') AS \"%s\"\n", + gettext_noop("Description")); + appendPQExpBufferStr(&buf, " \nFROM pg_catalog.pg_statistic_ext es \n"); @@ -6574,6 +6579,8 @@ describePublications(const char *pattern) bool has_pubgencols; bool has_pubviaroot; bool has_pubsequence; + int ncols = 6; + int nrows = 1; PQExpBufferData title; printTableContent cont; @@ -6637,6 +6644,9 @@ describePublications(const char *pattern) appendPQExpBufferStr(&buf, ", false AS pubviaroot"); + appendPQExpBufferStr(&buf, + ", pg_catalog.obj_description(oid, 'pg_publication')"); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_publication\n"); @@ -6674,25 +6684,23 @@ describePublications(const char *pattern) return false; } + if (has_pubsequence) + ncols++; + if (has_pubtruncate) + ncols++; + if (has_pubgencols) + ncols++; + if (has_pubviaroot) + ncols++; + for (i = 0; i < PQntuples(res); i++) { const char align = 'l'; - int ncols = 5; - int nrows = 1; char *pubid = PQgetvalue(res, i, 0); char *pubname = PQgetvalue(res, i, 1); bool puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0; printTableOpt myopt = pset.popt.topt; - if (has_pubsequence) - ncols++; - if (has_pubtruncate) - ncols++; - if (has_pubgencols) - ncols++; - if (has_pubviaroot) - ncols++; - initPQExpBuffer(&title); printfPQExpBuffer(&title, _("Publication %s"), pubname); printTableInit(&cont, &myopt, title.data, ncols, nrows); @@ -6710,6 +6718,7 @@ describePublications(const char *pattern) printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align); if (has_pubviaroot) printTableAddHeader(&cont, gettext_noop("Via root"), true, align); + printTableAddHeader(&cont, gettext_noop("Description"), true, align); printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); @@ -6724,6 +6733,7 @@ describePublications(const char *pattern) printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); if (has_pubviaroot) printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false); + printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false); if (!puballtables) { @@ -6806,7 +6816,7 @@ describeSubscriptions(const char *pattern, bool verbose) printQueryOpt myopt = pset.popt; static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false, false, false, false, false, - false, false, false, false, false}; + false, false, false, false, false, false}; if (pset.sversion < 100000) { @@ -6905,6 +6915,10 @@ describeSubscriptions(const char *pattern, bool verbose) appendPQExpBuffer(&buf, ", subskiplsn AS \"%s\"\n", gettext_noop("Skip LSN")); + + appendPQExpBuffer(&buf, + ", pg_catalog.obj_description(oid, 'pg_subscription') AS \"%s\"\n", + gettext_noop("Description")); } /* Only display subscriptions in current database. */ diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index b60a2ad0e1..47fae5ceaf 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -114,7 +114,7 @@ extern bool listExtensions(const char *pattern); extern bool listExtensionContents(const char *pattern); /* \dX */ -extern bool listExtendedStats(const char *pattern); +extern bool listExtendedStats(const char *pattern, bool verbose); /* \dy */ extern bool listEventTriggers(const char *pattern, bool verbose); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 7fb49aaf29..105d15ae45 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -97,10 +97,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl1" Tables from schemas: @@ -109,20 +109,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | 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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" @@ -133,10 +133,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_for_tbl_schema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -154,10 +154,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -166,10 +166,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test" @@ -180,10 +180,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" @@ -207,10 +207,10 @@ Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables - Publication testpub_foralltables - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | f | t | t | f | f | none | f + Publication testpub_foralltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | t | f | t | t | f | f | none | f | (1 row) DROP TABLE testpub_tbl2; @@ -222,19 +222,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub3 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl3" "public.testpub_tbl3a" \dRp+ testpub4 - Publication testpub4 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub4 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl3" @@ -262,10 +262,10 @@ Publications: "regress_pub_forallsequences1" \dRp+ regress_pub_forallsequences1 - Publication regress_pub_forallsequences1 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | t | none | f + Publication regress_pub_forallsequences1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | t | t | t | t | t | none | f | (1 row) SET client_min_messages = 'ERROR'; @@ -298,10 +298,10 @@ SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname (1 row) \dRp+ regress_pub_for_allsequences_alltables - Publication regress_pub_for_allsequences_alltables - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | f | f | f | stored | f + Publication regress_pub_for_allsequences_alltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | t | t | t | f | f | f | stored | f | (1 row) DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1; @@ -335,10 +335,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_parted" @@ -353,10 +353,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | t + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | t | Tables: "public.testpub_parted" @@ -385,10 +385,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -401,10 +401,10 @@ Tables: ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -420,10 +420,10 @@ Publications: ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) @@ -431,10 +431,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500)) @@ -467,10 +467,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub_syntax1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE (e < 999) @@ -480,10 +480,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | f | none | f + Publication testpub_syntax2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | f | none | f | Tables: "public.testpub_rf_tbl1" "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999) @@ -598,10 +598,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub6 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99) Tables from schemas: @@ -893,10 +893,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | f | f | t | none | f + Publication testpub_table_ins + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | f | f | t | none | f | Tables: "public.testpub_tbl5" (a) @@ -1086,10 +1086,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_both_filters + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1) @@ -1297,10 +1297,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortbl + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1340,10 +1340,10 @@ Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default - Publication testpub_default - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------------ + regress_publication_user | f | f | t | t | t | f | none | f | test publication Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1423,10 +1423,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------------ + regress_publication_user | f | f | t | t | t | f | none | f | test publication (1 row) -- fail - must be owner of publication @@ -1465,19 +1465,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | 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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1491,44 +1491,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "public" \dRp+ testpub4_forschema - Publication testpub4_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub4_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" \dRp+ testpub5_forschema - Publication testpub5_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub5_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub6_forschema - Publication testpub6_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub6_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "CURRENT_SCHEMA.CURRENT_SCHEMA" @@ -1562,10 +1562,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1573,20 +1573,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | 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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1594,10 +1594,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1606,10 +1606,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1618,10 +1618,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1629,10 +1629,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" @@ -1640,10 +1640,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" @@ -1651,29 +1651,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | 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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | 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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1682,10 +1682,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" "pub_test2" @@ -1694,10 +1694,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" @@ -1776,18 +1776,18 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub3_forschema; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | (1 row) ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables from schemas: "pub_test1" @@ -1797,20 +1797,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 | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_forschema_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" \dRp+ testpub_fortable_forschema - Publication testpub_fortable_forschema - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication testpub_fortable_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "pub_test2.tbl1" Tables from schemas: @@ -1931,18 +1931,18 @@ DROP SCHEMA sch2 cascade; SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = stored); \dRp+ pub1 - Publication pub1 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | f | t | t | t | t | stored | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | t | f | t | t | t | t | stored | f | (1 row) CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | t | f | t | t | t | t | none | f | (1 row) DROP PUBLICATION pub1; @@ -1953,50 +1953,50 @@ CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); -- Generated columns in column list, when 'publish_generated_columns'='none' CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns = none); \dRp+ pub1 - Publication pub1 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) -- Generated columns in column list, when 'publish_generated_columns'='stored' CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns = stored); \dRp+ pub2 - Publication pub2 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | stored | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | stored | f | Tables: "public.gencols" (a, gen1) -- Generated columns in column list, then set 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) -- Remove generated columns from column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a); \dRp+ pub2 - Publication pub2 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a) -- Add generated columns in column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1); \dRp+ pub2 - Publication pub2 - Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- + regress_publication_user | f | f | t | t | t | t | none | f | Tables: "public.gencols" (a, gen1) diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index cb8856ac50..b6431d1ee9 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3220,45 +3220,45 @@ set search_path to public, stts_s1, stts_s2, tststats; (1 row) \dX+ - List of extended statistics - Schema | Name | Definition | Ndistinct | Dependencies | MCV -----------+------------------------+------------------------------------------------------------------+-----------+--------------+--------- - public | func_deps_stat | (a * 2), upper(b), (c + 1::numeric) FROM functional_dependencies | | defined | - public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined - public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined - public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined - public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined - public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | | - public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined | - public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined - public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined - stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined - stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined - tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined + List of extended statistics + Schema | Name | Definition | Ndistinct | Dependencies | MCV | Description +----------+------------------------+------------------------------------------------------------------+-----------+--------------+---------+------------- + public | func_deps_stat | (a * 2), upper(b), (c + 1::numeric) FROM functional_dependencies | | defined | | + public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined | + public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined | + public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined | + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined | + public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | | | + public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined | | + public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined | + public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined | + stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined | + stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined | + tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined | (12 rows) \dX+ stts_t* - List of extended statistics - Schema | Name | Definition | Ndistinct | Dependencies | MCV ---------+-------------------+-------------------+-----------+--------------+--------- - public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | | - public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined | - public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined - public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined + List of extended statistics + Schema | Name | Definition | Ndistinct | Dependencies | MCV | Description +--------+-------------------+-------------------+-----------+--------------+---------+------------- + public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | | | + public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined | | + public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined | + public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined | (4 rows) \dX+ *stts_hoge - List of extended statistics - Schema | Name | Definition | Ndistinct | Dependencies | MCV ---------+-----------+-------------------------------+-----------+--------------+--------- - public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined + List of extended statistics + Schema | Name | Definition | Ndistinct | Dependencies | MCV | Description +--------+-----------+-------------------------------+-----------+--------------+---------+------------- + public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined | (1 row) \dX+ stts_s2.stts_yama - List of extended statistics - Schema | Name | Definition | Ndistinct | Dependencies | MCV ----------+-----------+-------------------------+-----------+--------------+--------- - stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined + List of extended statistics + Schema | Name | Definition | Ndistinct | Dependencies | MCV | Description +---------+-----------+-------------------------+-----------+--------------+---------+------------- + stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined | (1 row) create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1; diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 3a0637772c..a5fdfe68a0 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -116,18 +116,18 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ regress_testsub4 - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN -------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); \dRs+ regress_testsub4 - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN -------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) DROP SUBSCRIPTION regress_testsub3; @@ -145,10 +145,10 @@ ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar'; ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | test subscription (1 row) ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); @@ -157,10 +157,10 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname'); ALTER SUBSCRIPTION regress_testsub SET (password_required = false); ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription (1 row) ALTER SUBSCRIPTION regress_testsub SET (password_required = true); @@ -176,10 +176,10 @@ ERROR: unrecognized subscription parameter: "create_slot" -- ok ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00012345 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00012345 | test subscription (1 row) -- ok - with lsn = NONE @@ -188,10 +188,10 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE); ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0'); ERROR: invalid WAL location (LSN): 0/0 \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+------------------- + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription (1 row) BEGIN; @@ -227,10 +227,10 @@ ALTER SUBSCRIPTION regress_testsub_foo SET (wal_receiver_timeout = '80s'); ALTER SUBSCRIPTION regress_testsub_foo SET (wal_receiver_timeout = 'foobar'); ERROR: invalid value for parameter "wal_receiver_timeout": "foobar" \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ----------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------ - regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | local | dbname=regress_doesnotexist2 | 80s | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+------------------- + regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | f | 0 | f | local | dbname=regress_doesnotexist2 | 80s | 0/00000000 | test subscription (1 row) -- rename back to keep the rest simple @@ -259,19 +259,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (binary = false); ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) DROP SUBSCRIPTION regress_testsub; @@ -283,27 +283,27 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (streaming = false); ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) -- fail - publication already exists @@ -318,10 +318,10 @@ ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refr ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false); ERROR: publication "testpub1" is already in subscription "regress_testsub" \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) -- fail - publication used more than once @@ -336,10 +336,10 @@ ERROR: publication "testpub3" is not in subscription "regress_testsub" -- ok - delete publications ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) DROP SUBSCRIPTION regress_testsub; @@ -375,19 +375,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) -- we can alter streaming when two_phase enabled ALTER SUBSCRIPTION regress_testsub SET (streaming = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -397,10 +397,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -413,18 +413,18 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -437,10 +437,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); @@ -454,19 +454,19 @@ NOTICE: max_retention_duration is ineffective when retain_dead_tuples is disabl WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications. \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 1000 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 1000 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) -- ok ALTER SUBSCRIPTION regress_testsub SET (max_retention_duration = 0); \dRs+ - List of subscriptions - Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------ - regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 + List of subscriptions + Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description +-----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+------------- + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); -- 2.43.0