On Sun, Aug 14, 2022 at 10:24 PM vignesh C <vignes...@gmail.com> wrote:
>
> On Sun, Aug 14, 2022 at 11:07 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> >
> > vignesh C <vignes...@gmail.com> writes:
> > > Currently we do not include the dependent extension information for
> > > index and materialized view in the describe command. I felt it would
> > > be useful to include this information as part of the describe command
> > > like:
> > > \d+ idx_depends
> > > Index "public.idx_depends"
> > > Column | Type | Key? | Definition | Storage | Stats target
> > > --------+---------+------+------------+---------+--------------
> > > a | integer | yes | a | plain |
> > > btree, for table "public.tbl_idx_depends"
> > > Depends:
> > > "plpgsql"
> >
> > > Attached a patch for the same. Thoughts?
> >
> > This seems pretty much useless noise to me. Can you point to
> > any previous requests for such a feature? If we did do it,
> > why would we do it in such a narrow fashion (ie, only dependencies
> > of two specific kinds of objects on one other specific kind of
> > object)? Why did you do it in this direction rather than
> > the other one, ie show dependencies when examining the extension?
>
> While implementing logical replication of "index which depends on
> extension", I found that this information was not available in any of
> the \d describe commands. I felt having this information in the \d
> describe command will be useful in validating the "depends on
> extension" easily. Now that you pointed out, I agree that it will be
> better to show the dependencies from the extension instead of handling
> it in multiple places. I will change it to handle it from extension
> and post an updated version soon for this.
I have updated the patch to display "Objects depending on extension"
as describe extension footer. The changes for the same are available
in the v2 version patch attached. Thoughts?
Regards,
Vignesh
From 3bdb382f38b889b303f7a7036d9a0fc5dbeb2be7 Mon Sep 17 00:00:00 2001
From: Vigneshwaran C <vignes...@gmail.com>
Date: Thu, 28 Jul 2022 22:19:00 +0530
Subject: [PATCH v2] Include the objects depending on extension in describe
extension
Include the objects depending on extension in describe extension
---
.../expected/create_transform.out | 3 -
src/bin/psql/describe.c | 94 ++++++++++++++++---
.../expected/test_extensions.out | 6 --
src/test/regress/expected/indexing.out | 17 ++++
src/test/regress/expected/matview.out | 16 ++++
src/test/regress/sql/indexing.sql | 7 ++
src/test/regress/sql/matview.sql | 6 ++
7 files changed, 128 insertions(+), 21 deletions(-)
diff --git a/contrib/hstore_plperl/expected/create_transform.out b/contrib/hstore_plperl/expected/create_transform.out
index dc72395376..d060d6ff65 100644
--- a/contrib/hstore_plperl/expected/create_transform.out
+++ b/contrib/hstore_plperl/expected/create_transform.out
@@ -49,7 +49,6 @@ CREATE EXTENSION hstore_plperl;
function hstore_to_plperl(internal)
function plperl_to_hstore(internal)
transform for hstore language plperl
-(3 rows)
ALTER EXTENSION hstore_plperl DROP TRANSFORM FOR hstore LANGUAGE plperl;
\dx+ hstore_plperl
@@ -58,7 +57,6 @@ Objects in extension "hstore_plperl"
-------------------------------------
function hstore_to_plperl(internal)
function plperl_to_hstore(internal)
-(2 rows)
ALTER EXTENSION hstore_plperl ADD TRANSFORM FOR hstore LANGUAGE plperl;
\dx+ hstore_plperl
@@ -68,7 +66,6 @@ ALTER EXTENSION hstore_plperl ADD TRANSFORM FOR hstore LANGUAGE plperl;
function hstore_to_plperl(internal)
function plperl_to_hstore(internal)
transform for hstore language plperl
-(3 rows)
DROP EXTENSION hstore CASCADE;
NOTICE: drop cascades to extension hstore_plperl
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 327a69487b..003a3361ec 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -20,6 +20,7 @@
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_default_acl_d.h"
+#include "catalog/pg_extension_d.h"
#include "common.h"
#include "common/logging.h"
#include "describe.h"
@@ -45,7 +46,12 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
const char *cfgname,
const char *pnspname, const char *prsname);
static void printACLColumn(PQExpBuffer buf, const char *colname);
-static bool listOneExtensionContents(const char *extname, const char *oid);
+static bool listOneExtensionContents(const char *extname, const char *oid,
+ printTableContent *const content,
+ PQExpBufferData *title,
+ const printTableOpt *opt);
+static bool addFooterToExtensionDesc(const char *extname, const char *oid,
+ printTableContent *const content);
static bool validateSQLNamePattern(PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
@@ -5994,6 +6000,8 @@ listExtensionContents(const char *pattern)
PQExpBufferData buf;
PGresult *res;
int i;
+ PQExpBufferData title;
+ printTableContent cont;
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
@@ -6035,15 +6043,31 @@ listExtensionContents(const char *pattern)
{
const char *extname;
const char *oid;
+ printTableOpt myopt = pset.popt.topt;
+ myopt.default_footer = false;
extname = PQgetvalue(res, i, 0);
oid = PQgetvalue(res, i, 1);
- if (!listOneExtensionContents(extname, oid))
+ initPQExpBuffer(&title);
+
+ if (!listOneExtensionContents(extname, oid, &cont, &title, &myopt))
+ {
+ PQclear(res);
+ return false;
+ }
+
+ if (!addFooterToExtensionDesc(extname, oid, &cont))
{
PQclear(res);
return false;
}
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+
+ termPQExpBuffer(&title);
+
if (cancel_pressed)
{
PQclear(res);
@@ -6056,12 +6080,16 @@ listExtensionContents(const char *pattern)
}
static bool
-listOneExtensionContents(const char *extname, const char *oid)
+listOneExtensionContents(const char *extname, const char *oid,
+ printTableContent *const content,
+ PQExpBufferData *title,
+ const printTableOpt *opt)
{
PQExpBufferData buf;
PGresult *res;
- PQExpBufferData title;
- printQueryOpt myopt = pset.popt;
+ int ncols = 1;
+ int nrows;
+ int i;
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
@@ -6077,15 +6105,57 @@ listOneExtensionContents(const char *extname, const char *oid)
if (!res)
return false;
- myopt.nullPrint = NULL;
- initPQExpBuffer(&title);
- printfPQExpBuffer(&title, _("Objects in extension \"%s\""), extname);
- myopt.title = title.data;
- myopt.translate_header = true;
+ nrows = PQntuples(res);
- printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ printfPQExpBuffer(title, _("Objects in extension \"%s\""), extname);
+ printTableInit(content, opt, title->data, ncols, nrows);
- termPQExpBuffer(&title);
+ printTableAddHeader(content, gettext_noop("Object description"), true, 'l');
+
+ for (i = 0; i < nrows; i++)
+ printTableAddCell(content, pstrdup(PQgetvalue(res, i, 0)), false, false);
+
+ PQclear(res);
+ return true;
+}
+
+static bool
+addFooterToExtensionDesc(const char *extname, const char *oid,
+ printTableContent *const content)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ int i;
+ int count;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
+ "FROM pg_catalog.pg_depend\n"
+ "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'x'\n"
+ "ORDER BY 1;",
+ gettext_noop("Object description"),
+ oid);
+
+ res = PSQLexec(buf.data);
+ if (!res)
+ {
+ termPQExpBuffer(&buf);
+ return false;
+ }
+ else
+ count = PQntuples(res);
+
+ if (count > 0)
+ printTableAddFooter(content, "\nObjects depending on extension:");
+
+ for (i = 0; i < count; i++)
+ {
+ printfPQExpBuffer(&buf, " %s", PQgetvalue(res, i, 0));
+ printTableAddFooter(content, buf.data);
+ }
+
+ termPQExpBuffer(&buf);
PQclear(res);
return true;
}
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..e58d0e9226 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -52,7 +52,6 @@ Objects in extension "test_ext7"
table ext7_table1
table ext7_table2
table old_table1
-(6 rows)
alter extension test_ext7 update to '2.0';
\dx+ test_ext7
@@ -61,7 +60,6 @@ Objects in extension "test_ext7"
-------------------------------
sequence ext7_table2_col2_seq
table ext7_table2
-(2 rows)
-- test handling of temp objects created by extensions
create extension test_ext8;
@@ -117,7 +115,6 @@ Objects in extension "test_ext8"
function ext8_even(posint)
table ext8_table1
type posint
-(3 rows)
-- dropping it should still work
drop extension test_ext8;
@@ -238,7 +235,6 @@ Objects in extension "test_ext_cor"
operator <<@@(point,polygon)
type test_ext_type
view ext_cor_view
-(4 rows)
--
-- CREATE IF NOT EXISTS is an entirely unsound thing for an extension
@@ -294,7 +290,6 @@ Objects in extension "test_ext_cine"
server ext_cine_srv
table ext_cine_tab1
table ext_cine_tab2
-(8 rows)
ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
\dx+ test_ext_cine
@@ -310,5 +305,4 @@ Objects in extension "test_ext_cine"
table ext_cine_tab1
table ext_cine_tab2
table ext_cine_tab3
-(9 rows)
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 193f780191..e15e8751d2 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1296,6 +1296,23 @@ ERROR: cannot drop inherited constraint "parted_uniq_detach_test1_a_key" of rel
alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
drop table parted_uniq_detach_test, parted_uniq_detach_test1;
+-- check that the index is marked as dependent on extension
+create table tbl_idx_depends(a int);
+create index idx_depends on tbl_idx_depends(a);
+alter index idx_depends depends on extension plpgsql;
+\dx+ plpgsql
+ Objects in extension "plpgsql"
+ Object description
+-------------------------------------------
+ function plpgsql_call_handler()
+ function plpgsql_inline_handler(internal)
+ function plpgsql_validator(oid)
+ language plpgsql
+
+Objects depending on extension:
+ index idx_depends
+
+drop table tbl_idx_depends;
-- check that dropping a column takes with it any partitioned indexes
-- depending on it.
create table parted_index_col_drop(a int, b int, c int)
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index c109d97635..c3ea927618 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -676,3 +676,19 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+-- check that the materialized view is marked as dependent on extension
+CREATE MATERIALIZED VIEW matview_depends AS SELECT 1;
+ALTER MATERIALIZED VIEW matview_depends DEPENDS ON EXTENSION plpgsql;
+\dx+ plpgsql
+ Objects in extension "plpgsql"
+ Object description
+-------------------------------------------
+ function plpgsql_call_handler()
+ function plpgsql_inline_handler(internal)
+ function plpgsql_validator(oid)
+ language plpgsql
+
+Objects depending on extension:
+ materialized view matview_depends
+
+DROP MATERIALIZED VIEW matview_depends;
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 42f398b67c..15728f03f6 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -727,6 +727,13 @@ alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
drop table parted_uniq_detach_test, parted_uniq_detach_test1;
+-- check that the index is marked as dependent on extension
+create table tbl_idx_depends(a int);
+create index idx_depends on tbl_idx_depends(a);
+alter index idx_depends depends on extension plpgsql;
+\dx+ plpgsql
+drop table tbl_idx_depends;
+
-- check that dropping a column takes with it any partitioned indexes
-- depending on it.
create table parted_index_col_drop(a int, b int, c int)
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..169a4e32af 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -295,3 +295,9 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+-- check that the materialized view is marked as dependent on extension
+CREATE MATERIALIZED VIEW matview_depends AS SELECT 1;
+ALTER MATERIALIZED VIEW matview_depends DEPENDS ON EXTENSION plpgsql;
+\dx+ plpgsql
+DROP MATERIALIZED VIEW matview_depends;
--
2.32.0
