From 915379c3c2613f2b24d4e9c6fa79a43e7c6a86ec Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 15 Jun 2017 10:46:41 -0400 Subject: [PATCH] psql: Improve display of "for all tables" publications Show "All tables" property in \dRp and \dRp+. Don't list tables for such publications in \dRp+, since it's redundant and the list could be very long. Author: Masahiko Sawada Author: Jeff Janes --- src/bin/psql/describe.c | 62 ++++++++--------- src/test/regress/expected/publication.out | 83 ++++++++++++----------- src/test/regress/sql/publication.sql | 1 + 3 files changed, 75 insertions(+), 71 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 10c8a789b7b..1c268f0b08f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -5047,7 +5047,7 @@ listPublications(const char *pattern) PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, false, false, false, false}; + static const bool translate_columns[] = {false, false, false, false, false, false}; if (pset.sversion < 100000) { @@ -5064,11 +5064,13 @@ listPublications(const char *pattern) printfPQExpBuffer(&buf, "SELECT pubname AS \"%s\",\n" " pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n" + " puballtables AS \"%s\",\n" " pubinsert AS \"%s\",\n" " pubupdate AS \"%s\",\n" " pubdelete AS \"%s\"\n", gettext_noop("Name"), gettext_noop("Owner"), + gettext_noop("All tables"), gettext_noop("Inserts"), gettext_noop("Updates"), gettext_noop("Deletes")); @@ -5145,7 +5147,7 @@ describePublications(const char *pattern) for (i = 0; i < PQntuples(res); i++) { const char align = 'l'; - int ncols = 3; + int ncols = 4; int nrows = 1; int tables = 0; PGresult *tabres; @@ -5161,25 +5163,18 @@ describePublications(const char *pattern) printfPQExpBuffer(&title, _("Publication %s"), pubname); printTableInit(&cont, &myopt, title.data, ncols, nrows); + printTableAddHeader(&cont, gettext_noop("All tables"), true, align); printTableAddHeader(&cont, gettext_noop("Inserts"), true, align); printTableAddHeader(&cont, gettext_noop("Updates"), true, align); printTableAddHeader(&cont, gettext_noop("Deletes"), true, align); + printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false); printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false); - if (puballtables) - printfPQExpBuffer(&buf, - "SELECT n.nspname, c.relname\n" - "FROM pg_catalog.pg_class c,\n" - " pg_catalog.pg_namespace n\n" - "WHERE c.relnamespace = n.oid\n" - " AND c.relkind = " CppAsString2(RELKIND_RELATION) "\n" - " AND n.nspname <> 'pg_catalog'\n" - " AND n.nspname <> 'information_schema'\n" - "ORDER BY 1,2"); - else + if (!puballtables) + { printfPQExpBuffer(&buf, "SELECT n.nspname, c.relname\n" "FROM pg_catalog.pg_class c,\n" @@ -5190,30 +5185,31 @@ describePublications(const char *pattern) " AND pr.prpubid = '%s'\n" "ORDER BY 1,2", pubid); - tabres = PSQLexec(buf.data); - if (!tabres) - { - printTableCleanup(&cont); - PQclear(res); - termPQExpBuffer(&buf); - termPQExpBuffer(&title); - return false; - } - else - tables = PQntuples(tabres); + tabres = PSQLexec(buf.data); + if (!tabres) + { + printTableCleanup(&cont); + PQclear(res); + termPQExpBuffer(&buf); + termPQExpBuffer(&title); + return false; + } + else + tables = PQntuples(tabres); - if (tables > 0) - printTableAddFooter(&cont, _("Tables:")); + if (tables > 0) + printTableAddFooter(&cont, _("Tables:")); - for (j = 0; j < tables; j++) - { - printfPQExpBuffer(&buf, " \"%s.%s\"", - PQgetvalue(tabres, j, 0), - PQgetvalue(tabres, j, 1)); + for (j = 0; j < tables; j++) + { + printfPQExpBuffer(&buf, " \"%s.%s\"", + PQgetvalue(tabres, j, 0), + PQgetvalue(tabres, j, 1)); - printTableAddFooter(&cont, buf.data); + printTableAddFooter(&cont, buf.data); + } + PQclear(tabres); } - PQclear(tabres); printTable(&cont, pset.queryFout, false, pset.logfile); printTableCleanup(&cont); diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index e81919fd8cb..50592c63a9b 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -21,20 +21,20 @@ ERROR: unrecognized publication parameter: foo CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); ERROR: unrecognized "publish" value: "cluster" \dRp - List of publications - Name | Owner | Inserts | Updates | Deletes ---------------------+--------------------------+---------+---------+--------- - testpib_ins_trunct | regress_publication_user | t | f | f - testpub_default | regress_publication_user | f | t | f + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes +--------------------+--------------------------+------------+---------+---------+--------- + testpib_ins_trunct | regress_publication_user | f | t | f | f + testpub_default | regress_publication_user | f | f | t | f (2 rows) ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); \dRp - List of publications - Name | Owner | Inserts | Updates | Deletes ---------------------+--------------------------+---------+---------+--------- - testpib_ins_trunct | regress_publication_user | t | f | f - testpub_default | regress_publication_user | t | t | t + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes +--------------------+--------------------------+------------+---------+---------+--------- + testpib_ins_trunct | regress_publication_user | f | t | f | f + testpub_default | regress_publication_user | f | t | t | t (2 rows) --- adding tables @@ -75,6 +75,13 @@ Indexes: Publications: "testpub_foralltables" +\dRp+ testpub_foralltables + Publication testpub_foralltables + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + t | t | t | f +(1 row) + DROP TABLE testpub_tbl2; DROP PUBLICATION testpub_foralltables; CREATE TABLE testpub_tbl3 (a int); @@ -82,19 +89,19 @@ CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; \dRp+ testpub3 - Publication testpub3 - Inserts | Updates | Deletes ----------+---------+--------- - t | t | t + Publication testpub3 + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + f | t | t | t Tables: "public.testpub_tbl3" "public.testpub_tbl3a" \dRp+ testpub4 - Publication testpub4 - Inserts | Updates | Deletes ----------+---------+--------- - t | t | t + Publication testpub4 + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + f | t | t | t Tables: "public.testpub_tbl3" @@ -112,10 +119,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 - Inserts | Updates | Deletes ----------+---------+--------- - t | t | t + Publication testpub_fortbl + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + f | t | t | t Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -158,10 +165,10 @@ Publications: "testpub_fortbl" \dRp+ testpub_default - Publication testpub_default - Inserts | Updates | Deletes ----------+---------+--------- - t | t | t + Publication testpub_default + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + f | t | t | t Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -203,10 +210,10 @@ DROP TABLE testpub_parted; DROP VIEW testpub_view; DROP TABLE testpub_tbl1; \dRp+ testpub_default - Publication testpub_default - Inserts | Updates | Deletes ----------+---------+--------- - t | t | t + Publication testpub_default + All tables | Inserts | Updates | Deletes +------------+---------+---------+--------- + f | t | t | t (1 row) -- fail - must be owner of publication @@ -216,20 +223,20 @@ ERROR: must be owner of publication testpub_default RESET ROLE; ALTER PUBLICATION testpub_default RENAME TO testpub_foo; \dRp testpub_foo - List of publications - Name | Owner | Inserts | Updates | Deletes --------------+--------------------------+---------+---------+--------- - testpub_foo | regress_publication_user | t | t | t + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes +-------------+--------------------------+------------+---------+---------+--------- + testpub_foo | regress_publication_user | f | t | t | t (1 row) -- rename back to keep the rest simple ALTER PUBLICATION testpub_foo RENAME TO testpub_default; ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default - List of publications - Name | Owner | Inserts | Updates | Deletes ------------------+---------------------------+---------+---------+--------- - testpub_default | regress_publication_user2 | t | t | t + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes +-----------------+---------------------------+------------+---------+---------+--------- + testpub_default | regress_publication_user2 | f | t | t | t (1 row) DROP PUBLICATION testpub_default; diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index cc1f33e72cd..815410b3c5a 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -45,6 +45,7 @@ ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; \d+ testpub_tbl2 +\dRp+ testpub_foralltables DROP TABLE testpub_tbl2; DROP PUBLICATION testpub_foralltables; -- 2.39.5