postgres.git / summary / log / commit / refs
commit d84ffffe582b8e036a14c6bc2378df29167f3a00
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Wed Apr 21 09:54:47 2021 +0000
First released in: 14.0
Add DISTINCT to information schema usage views
Since pg_depend can contain duplicate entries, we need to eliminate
those in information schema views that build on pg_depend, using
DISTINCT. Some of the older views already did that correctly, but
some of the more recently added ones didn't. (In some of these views,
it might not be possible to reproduce the issue because of how the
implementation happens to deduplicate dependencies while recording
them, but it seems better to keep this consistent in all cases.)
src/backend/catalog/information_schema.sql | 18 ++++++++++++------
src/include/catalog/catversion.h | 2 +-
2 files changed, 13 insertions(+), 7 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 0f2c1833e9f..11d9dd60c20 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -406,7 +406,8 @@ GRANT SELECT ON character_sets TO PUBLIC;
*/
CREATE VIEW check_constraint_routine_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(nc.nspname AS sql_identifier) AS constraint_schema,
CAST(c.conname AS sql_identifier) AS constraint_name,
CAST(current_database() AS sql_identifier) AS specific_catalog,
@@ -505,7 +506,8 @@ GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
*/
CREATE VIEW column_column_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(n.nspname AS sql_identifier) AS table_schema,
CAST(c.relname AS sql_identifier) AS table_name,
CAST(ac.attname AS sql_identifier) AS column_name,
@@ -1325,7 +1327,8 @@ GRANT SELECT ON role_column_grants TO PUBLIC;
*/
CREATE VIEW routine_column_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(np.nspname AS sql_identifier) AS specific_schema,
CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
CAST(current_database() AS sql_identifier) AS routine_catalog,
@@ -1434,7 +1437,8 @@ GRANT SELECT ON role_routine_grants TO PUBLIC;
*/
CREATE VIEW routine_routine_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(np.nspname AS sql_identifier) AS specific_schema,
CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
CAST(current_database() AS sql_identifier) AS routine_catalog,
@@ -1462,7 +1466,8 @@ GRANT SELECT ON routine_routine_usage TO PUBLIC;
*/
CREATE VIEW routine_sequence_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(np.nspname AS sql_identifier) AS specific_schema,
CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
CAST(current_database() AS sql_identifier) AS routine_catalog,
@@ -1493,7 +1498,8 @@ GRANT SELECT ON routine_sequence_usage TO PUBLIC;
*/
CREATE VIEW routine_table_usage AS
- SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(np.nspname AS sql_identifier) AS specific_schema,
CAST(nameconcatoid(p.proname, p.oid) AS sql_identifier) AS specific_name,
CAST(current_database() AS sql_identifier) AS routine_catalog,
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index a185f0c313c..c32c5b2731a 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202104201
+#define CATALOG_VERSION_NO 202104211
#endif
[parent: 39d0928a0e88]