v47-0003-Add-new-pg_publication_objects-view-to-display-T.patch

From ba643e06baeb50cb6dfb62d38858d4c46ca358f8 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Tue, 31 Aug 2021 18:25:11 +0530
Subject: [PATCH v47 3/3] Add new "pg_publication_objects" view to display
 "TABLE"/"SCHEMA" publication objects

A new "pg_publication_objects" view is added, to display table/schema object
information associated with publications.

Author: Vignesh C
Reviewed-by: Amit Kapila, Hou Zhijie, Greg Nancarrow, Masahiko Sawada, Peter Eisentraut, Tom Lane, Peter Smith, Ajin Cherian, Rahila Syed, Bharath Rupireddy, Mark Dilger
Tested-by: Tang Haiying
Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ%40mail.gmail.com
---
 doc/src/sgml/catalogs.sgml           | 70 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 19 ++++++++
 src/test/regress/expected/rules.out  | 15 ++++++
 3 files changed, 104 insertions(+)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 00b648a433..1dfb9d1a33 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9503,6 +9503,11 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       <entry>publications and their associated tables</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-publication-objects"><structname>pg_publication_objects</structname></link></entry>
+      <entry>publications and their associated objects</entry>
+     </row>
+
      <row>
       <entry><link linkend="view-pg-replication-origin-status"><structname>pg_replication_origin_status</structname></link></entry>
       <entry>information about replication origins, including replication progress</entry>
@@ -11332,6 +11337,71 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
 
  </sect1>
 
+<sect1 id="view-pg-publication-objects">
+  <title><structname>pg_publication_objects</structname></title>
+
+  <indexterm zone="view-pg-publication-objects">
+   <primary>pg_publication_objects</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_publication_objects</structname> provides
+   information about the mapping between publications and the objects they
+   contain.  Unlike the underlying catalog
+   <link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link>,
+   this view expands publications defined as <literal>FOR TABLE</literal>
+   and <literal>FOR ALL TABLES IN SCHEMA</literal>, so for such publications
+   there will be a row for each eligible object.
+  </para>
+
+  <table>
+   <title><structname>pg_publication_objects</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pubname</structfield> <type>name</type>
+       (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>pubname</structfield>)
+      </para>
+      <para>
+       Name of publication
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objname</structfield> <type>name</type>
+       (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>nspname</structfield> or <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relname</structfield>)
+      </para>
+      <para>
+       Name of schema or Name of table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objtype</structfield> <type>name</type>
+      </para>
+      <para>
+       The object type: <literal>schema</literal> or <literal>table</literal>
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+ 
  <sect1 id="view-pg-publication-tables">
   <title><structname>pg_publication_tables</structname></title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f6e3711d..f70348e34f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -362,6 +362,25 @@ CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
 -- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
 REVOKE ALL ON pg_statistic_ext_data FROM public;
 
+CREATE VIEW pg_publication_objects AS
+SELECT
+    P.pubname,
+    N.nspname AS objname,
+    'schema'::text AS objtype
+FROM pg_catalog.pg_publication P
+    JOIN pg_catalog.pg_publication_namespace S ON P.oid = S.pnpubid
+    JOIN pg_catalog.pg_namespace N on N.oid = S.pnnspid
+UNION
+SELECT
+    P.pubname,
+    quote_ident(N.nspname) || '.' || quote_ident(C.relname) AS objname,
+    'table'::text AS objtype
+FROM pg_catalog.pg_publication P
+    JOIN pg_catalog.pg_publication_rel R ON P.oid = R.prpubid
+    JOIN pg_catalog.pg_class C ON C.oid = R.prrelid
+    JOIN pg_catalog.pg_namespace N ON N.oid = C.relnamespace
+ORDER BY pubname;
+
 CREATE VIEW pg_publication_tables AS
     SELECT
         P.pubname AS pubname,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2fa00a3c29..8796f71de2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1451,6 +1451,21 @@ pg_prepared_xacts| SELECT p.transaction,
    FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
      LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
      LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
+pg_publication_objects| SELECT p.pubname,
+    n.nspname AS objname,
+    'schema'::text AS objtype
+   FROM ((pg_publication p
+     JOIN pg_publication_namespace s ON ((p.oid = s.pnpubid)))
+     JOIN pg_namespace n ON ((n.oid = s.pnnspid)))
+UNION
+ SELECT p.pubname,
+    ((quote_ident((n.nspname)::text) || '.'::text) || quote_ident((c.relname)::text)) AS objname,
+    'table'::text AS objtype
+   FROM (((pg_publication p
+     JOIN pg_publication_rel r ON ((p.oid = r.prpubid)))
+     JOIN pg_class c ON ((c.oid = r.prrelid)))
+     JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
+  ORDER BY 1;
 pg_publication_tables| SELECT p.pubname,
     n.nspname AS schemaname,
     c.relname AS tablename
-- 
2.30.2