Motivation:
We haven't fully solved the changing collation-provider problem. An
upgrade of the OS may change the version of libc or icu, and that might
affect the collation, which could leave you with various corrupt
database objects including:
* indexes
* constraints
* range types or multiranges (or other types dependent
on collation for internal consistency)
* materialized views
* partitioned tables (range or hash)
There's discussion about trying to reliably detect these changes and
remedy them. But there are major challenges; for instance, glibc
doesn't give a reliable signal that a collation may have changed, which
would leave us with a lot of false positives and create a new set of
problems (e.g. reindexing when it's unnecessary). And even with ICU, we
don't have a way to support multiple versions of a provider or of a
single collation, so trying to upgrade would still be a hassle.
Proposal:
Add in some tools to make it easier for administrators to find out if
they are at risk and solve the problem for themselves in a systematic
way.
Patches:
0001: Treat "default" collation as unpinned, so that entries in
pg_depend are created. The rationale is that, since the "default"
collation can change, it's not really an immutable system object, and
it's worth tracking which objects are affected by it. It seems to bloat
pg_depend by about 5-10% though -- that doesn't seem great, but I'm not
sure if it's a real problem or not.
0002: Enable pg_collation_actual_version() to work on the default
collation (oid=100) so that it doesn't need to be treated as a special
case.
0003: Fix ALTER COLLATION "default" REFRESH VERSION, which currently
throws an unhelpful internal error. Instead, issue a more helpful error
that suggests "ALTER DATABASE ... REFRESH COLLATION VERSION" instead.
0004: Add system views:
pg_collation_versions: quickly see the current (from the catalog)
and actual (from the provider) versions of each collation
pg_collation_dependencies: map of objects to the collations they
depend on
Along with these patches, you can use some tricks to verify data, such
as /contrib/amcheck; or fix the data with things like:
* REINDEX
* VACUUM FULL/TRUNCATE/CLUSTER
* REFRESH MATERIALIZED VIEW
And then refresh the collation version when you're confident that your
data is valid.
TODO:
* The dependencies view is not rigorously complete, because the
directed dependency graph doesn't quite establish an "affected by"
relationship. One exception is that a composite type doesn't depend on
its associated relation, so a composite type over a range type doesn't
depend on the range type.
* Consider adding in some verification helpers that can verify that a
value is still valid (e.g. a range type that depends on a collation
might have corrupt values). We could have a collation verifier for
types that are collation-dependenent, or perhaps just go through the
input and output functions and catch any errors.
* Consider better tracking of which collation versions were active on
a particular object since the last REINDEX (or REFRESH MATERIALIZED
VIEW, TRUNCATE, or other command that would remove any trace of data
affected by the previous collation version).
Regards,
Jeff Davis
From eabc71acbce4c008e618a057c123ca15d8eb3eb5 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jda...@postgresql.org>
Date: Sat, 29 Oct 2022 12:38:42 -0700
Subject: [PATCH 1/4] Count the default collation as an unpinned dependency.
Upgrading the collation provider library can affect the default
collation in subtle ways, so track the dependency like any other. That
will at least preserve the information about which objects might be
affected, enabling administrator intervention.
---
src/backend/catalog/catalog.c | 12 +++++
src/backend/catalog/dependency.c | 28 +++++-------
src/backend/catalog/heap.c | 8 +---
src/backend/catalog/index.c | 4 +-
src/backend/catalog/pg_type.c | 6 +--
src/backend/commands/tablecmds.c | 3 +-
src/test/regress/expected/create_index.out | 52 +++++++++++++---------
7 files changed, 58 insertions(+), 55 deletions(-)
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 2abd6b007a..2cf3f38d1e 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -29,6 +29,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_largeobject.h"
@@ -355,6 +356,17 @@ IsPinnedObject(Oid classId, Oid objectId)
if (classId == DatabaseRelationId)
return false;
+ /*
+ * The default collation is never pinned. While logically the default
+ * collation should not change, the collation provider may change in
+ * subtle ways when upgraded. Recording the dependencies on the default
+ * collation makes it easier to understand what objects might be affected
+ * when that happens.
+ */
+ if (classId == CollationRelationId &&
+ objectId == DEFAULT_COLLATION_OID)
+ return false;
+
/*
* All other initdb-created objects are pinned. This is overkill (the
* system doesn't really depend on having every last weird datatype, for
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7f3e64b5ae..31ed7cd8a5 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1805,11 +1805,9 @@ find_expr_references_walker(Node *node,
/*
* We must also depend on the constant's collation: it could be
* different from the datatype's, if a CollateExpr was const-folded to
- * a simple constant. However we can save work in the most common
- * case where the collation is "default", since we know that's pinned.
+ * a simple constant.
*/
- if (OidIsValid(con->constcollid) &&
- con->constcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(con->constcollid))
add_object_address(OCLASS_COLLATION, con->constcollid, 0,
context->addrs);
@@ -1905,8 +1903,7 @@ find_expr_references_walker(Node *node,
add_object_address(OCLASS_TYPE, param->paramtype, 0,
context->addrs);
/* and its collation, just as for Consts */
- if (OidIsValid(param->paramcollid) &&
- param->paramcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(param->paramcollid))
add_object_address(OCLASS_COLLATION, param->paramcollid, 0,
context->addrs);
}
@@ -2009,8 +2006,7 @@ find_expr_references_walker(Node *node,
add_object_address(OCLASS_TYPE, fselect->resulttype, 0,
context->addrs);
/* the collation might not be referenced anywhere else, either */
- if (OidIsValid(fselect->resultcollid) &&
- fselect->resultcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(fselect->resultcollid))
add_object_address(OCLASS_COLLATION, fselect->resultcollid, 0,
context->addrs);
}
@@ -2040,8 +2036,7 @@ find_expr_references_walker(Node *node,
add_object_address(OCLASS_TYPE, relab->resulttype, 0,
context->addrs);
/* the collation might not be referenced anywhere else, either */
- if (OidIsValid(relab->resultcollid) &&
- relab->resultcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(relab->resultcollid))
add_object_address(OCLASS_COLLATION, relab->resultcollid, 0,
context->addrs);
}
@@ -2053,8 +2048,7 @@ find_expr_references_walker(Node *node,
add_object_address(OCLASS_TYPE, iocoerce->resulttype, 0,
context->addrs);
/* the collation might not be referenced anywhere else, either */
- if (OidIsValid(iocoerce->resultcollid) &&
- iocoerce->resultcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(iocoerce->resultcollid))
add_object_address(OCLASS_COLLATION, iocoerce->resultcollid, 0,
context->addrs);
}
@@ -2066,8 +2060,7 @@ find_expr_references_walker(Node *node,
add_object_address(OCLASS_TYPE, acoerce->resulttype, 0,
context->addrs);
/* the collation might not be referenced anywhere else, either */
- if (OidIsValid(acoerce->resultcollid) &&
- acoerce->resultcollid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(acoerce->resultcollid))
add_object_address(OCLASS_COLLATION, acoerce->resultcollid, 0,
context->addrs);
/* fall through to examine arguments */
@@ -2155,8 +2148,7 @@ find_expr_references_walker(Node *node,
if (OidIsValid(wc->endInRangeFunc))
add_object_address(OCLASS_PROC, wc->endInRangeFunc, 0,
context->addrs);
- if (OidIsValid(wc->inRangeColl) &&
- wc->inRangeColl != DEFAULT_COLLATION_OID)
+ if (OidIsValid(wc->inRangeColl))
add_object_address(OCLASS_COLLATION, wc->inRangeColl, 0,
context->addrs);
/* fall through to examine substructure */
@@ -2316,7 +2308,7 @@ find_expr_references_walker(Node *node,
{
Oid collid = lfirst_oid(ct);
- if (OidIsValid(collid) && collid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(collid))
add_object_address(OCLASS_COLLATION, collid, 0,
context->addrs);
}
@@ -2338,7 +2330,7 @@ find_expr_references_walker(Node *node,
{
Oid collid = lfirst_oid(ct);
- if (OidIsValid(collid) && collid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(collid))
add_object_address(OCLASS_COLLATION, collid, 0,
context->addrs);
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 5b49cc5a09..b0cae59a41 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -832,9 +832,7 @@ AddNewAttributeTuples(Oid new_rel_oid,
tupdesc->attrs[i].atttypid);
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
- /* The default collation is pinned, so don't bother recording it */
- if (OidIsValid(tupdesc->attrs[i].attcollation) &&
- tupdesc->attrs[i].attcollation != DEFAULT_COLLATION_OID)
+ if (OidIsValid(tupdesc->attrs[i].attcollation))
{
ObjectAddressSet(referenced, CollationRelationId,
tupdesc->attrs[i].attcollation);
@@ -3367,9 +3365,7 @@ StorePartitionKey(Relation rel,
ObjectAddressSet(referenced, OperatorClassRelationId, partopclass[i]);
add_exact_object_address(&referenced, addrs);
- /* The default collation is pinned, so don't bother recording it */
- if (OidIsValid(partcollation[i]) &&
- partcollation[i] != DEFAULT_COLLATION_OID)
+ if (OidIsValid(partcollation[i]))
{
ObjectAddressSet(referenced, CollationRelationId, partcollation[i]);
add_exact_object_address(&referenced, addrs);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 61f1d3926a..f835647379 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1142,11 +1142,9 @@ index_create(Relation heapRelation,
/* Store dependency on collations */
- /* The default collation is pinned, so don't bother recording it */
for (i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
{
- if (OidIsValid(collationObjectId[i]) &&
- collationObjectId[i] != DEFAULT_COLLATION_OID)
+ if (OidIsValid(collationObjectId[i]))
{
ObjectAddressSet(referenced, CollationRelationId,
collationObjectId[i]);
diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c
index 896d0146ca..51a7b0fa7a 100644
--- a/src/backend/catalog/pg_type.c
+++ b/src/backend/catalog/pg_type.c
@@ -680,11 +680,9 @@ GenerateTypeDependencies(HeapTuple typeTuple,
}
/*
- * Normal dependency from a domain to its collation. We know the default
- * collation is pinned, so don't bother recording it.
+ * Normal dependency from a domain to its collation.
*/
- if (OidIsValid(typeForm->typcollation) &&
- typeForm->typcollation != DEFAULT_COLLATION_OID)
+ if (OidIsValid(typeForm->typcollation))
{
ObjectAddressSet(referenced, CollationRelationId, typeForm->typcollation);
add_exact_object_address(&referenced, addrs_normal);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 20135ef1b0..fa3aeb5122 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7175,8 +7175,7 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
ObjectAddress myself,
referenced;
- /* We know the default collation is pinned, so don't bother recording it */
- if (OidIsValid(collid) && collid != DEFAULT_COLLATION_OID)
+ if (OidIsValid(collid))
{
myself.classId = RelationRelationId;
myself.objectId = relid;
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 6cd57e3eaa..cc45e489f0 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2095,17 +2095,21 @@ WHERE classid = 'pg_class'::regclass AND
'concur_reindex_ind4'::regclass,
'concur_reindex_matview'::regclass)
ORDER BY 1, 2;
- obj | objref | deptype
-------------------------------------------+------------------------------------------------------------+---------
- index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
- index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
- index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
- index concur_reindex_ind3 | table concur_reindex_tab | a
- index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
- index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
- materialized view concur_reindex_matview | schema public | n
- table concur_reindex_tab | schema public | n
-(8 rows)
+ obj | objref | deptype
+-------------------------------------------------------+------------------------------------------------------------+---------
+ column c2 of materialized view concur_reindex_matview | collation "default" | n
+ column c2 of table concur_reindex_tab | collation "default" | n
+ index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
+ index concur_reindex_ind2 | collation "default" | n
+ index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
+ index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
+ index concur_reindex_ind3 | table concur_reindex_tab | a
+ index concur_reindex_ind4 | collation "default" | n
+ index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
+ index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
+ materialized view concur_reindex_matview | schema public | n
+ table concur_reindex_tab | schema public | n
+(12 rows)
REINDEX INDEX CONCURRENTLY concur_reindex_ind1;
REINDEX TABLE CONCURRENTLY concur_reindex_tab;
@@ -2122,17 +2126,21 @@ WHERE classid = 'pg_class'::regclass AND
'concur_reindex_ind4'::regclass,
'concur_reindex_matview'::regclass)
ORDER BY 1, 2;
- obj | objref | deptype
-------------------------------------------+------------------------------------------------------------+---------
- index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
- index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
- index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
- index concur_reindex_ind3 | table concur_reindex_tab | a
- index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
- index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
- materialized view concur_reindex_matview | schema public | n
- table concur_reindex_tab | schema public | n
-(8 rows)
+ obj | objref | deptype
+-------------------------------------------------------+------------------------------------------------------------+---------
+ column c2 of materialized view concur_reindex_matview | collation "default" | n
+ column c2 of table concur_reindex_tab | collation "default" | n
+ index concur_reindex_ind1 | constraint concur_reindex_ind1 on table concur_reindex_tab | i
+ index concur_reindex_ind2 | collation "default" | n
+ index concur_reindex_ind2 | column c2 of table concur_reindex_tab | a
+ index concur_reindex_ind3 | column c1 of table concur_reindex_tab | a
+ index concur_reindex_ind3 | table concur_reindex_tab | a
+ index concur_reindex_ind4 | collation "default" | n
+ index concur_reindex_ind4 | column c1 of table concur_reindex_tab | a
+ index concur_reindex_ind4 | column c2 of table concur_reindex_tab | a
+ materialized view concur_reindex_matview | schema public | n
+ table concur_reindex_tab | schema public | n
+(12 rows)
-- Check that comments are preserved
CREATE TABLE testcomment (i int);
--
2.34.1
From 476ea078bdfdbc8ee2ba03ba3f0f451701b418aa Mon Sep 17 00:00:00 2001
From: Jeff Davis <jda...@postgresql.org>
Date: Sat, 29 Oct 2022 13:30:15 -0700
Subject: [PATCH 2/4] Enable pg_collation_actual_version() to work on the
default collation.
Previously, it would simply return NULL, which was less useful.
---
src/backend/commands/collationcmds.c | 67 ++++++++++++++++++++--------
1 file changed, 48 insertions(+), 19 deletions(-)
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index fcfc02d2ae..23468cf79d 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -22,6 +22,7 @@
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
#include "commands/alter.h"
#include "commands/collationcmds.h"
#include "commands/comment.h"
@@ -425,33 +426,61 @@ AlterCollation(AlterCollationStmt *stmt)
Datum
pg_collation_actual_version(PG_FUNCTION_ARGS)
{
- Oid collid = PG_GETARG_OID(0);
- HeapTuple tp;
- char collprovider;
- Datum datum;
- bool isnull;
- char *version;
+ Oid collid = PG_GETARG_OID(0);
+ char provider;
+ char *locale;
+ char *version;
+ Datum datum;
+ bool isnull;
+
+ if (collid == DEFAULT_COLLATION_OID)
+ {
+ /* retrieve from pg_database */
- tp = SearchSysCache1(COLLOID, ObjectIdGetDatum(collid));
- if (!HeapTupleIsValid(tp))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("collation with OID %u does not exist", collid)));
+ HeapTuple dbtup = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+ if (!HeapTupleIsValid(dbtup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with OID %u does not exist", MyDatabaseId)));
- collprovider = ((Form_pg_collation) GETSTRUCT(tp))->collprovider;
+ provider = ((Form_pg_database) GETSTRUCT(dbtup))->datlocprovider;
- if (collprovider != COLLPROVIDER_DEFAULT)
- {
- datum = SysCacheGetAttr(COLLOID, tp, collprovider == COLLPROVIDER_ICU ? Anum_pg_collation_colliculocale : Anum_pg_collation_collcollate, &isnull);
+ datum = SysCacheGetAttr(DATABASEOID, dbtup,
+ provider == COLLPROVIDER_ICU ?
+ Anum_pg_database_daticulocale : Anum_pg_database_datcollate,
+ &isnull);
if (isnull)
- elog(ERROR, "unexpected null in pg_collation");
- version = get_collation_actual_version(collprovider, TextDatumGetCString(datum));
+ elog(ERROR, "unexpected null in pg_database");
+
+ locale = TextDatumGetCString(datum);
+
+ ReleaseSysCache(dbtup);
}
else
- version = NULL;
+ {
+ /* retrieve from pg_collation */
+
+ HeapTuple colltp = SearchSysCache1(COLLOID, ObjectIdGetDatum(collid));
+ if (!HeapTupleIsValid(colltp))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("collation with OID %u does not exist", collid)));
+
+ provider = ((Form_pg_collation) GETSTRUCT(colltp))->collprovider;
+ Assert(provider != COLLPROVIDER_DEFAULT);
+ datum = SysCacheGetAttr(COLLOID, colltp,
+ provider == COLLPROVIDER_ICU ?
+ Anum_pg_collation_colliculocale : Anum_pg_collation_collcollate,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "unexpected null in pg_collation");
- ReleaseSysCache(tp);
+ locale = TextDatumGetCString(datum);
+
+ ReleaseSysCache(colltp);
+ }
+ version = get_collation_actual_version(provider, locale);
if (version)
PG_RETURN_TEXT_P(cstring_to_text(version));
else
--
2.34.1
From 05540853eb5738d35e143ffe02604e52f291d873 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jda...@postgresql.org>
Date: Sat, 29 Oct 2022 14:13:23 -0700
Subject: [PATCH 3/4] Fix ALTER COLLATION "default" REFRESH VERSION.
Issue a helpful error message rather than an internal error.
---
src/backend/commands/collationcmds.c | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index 23468cf79d..86fbc7fa01 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -366,6 +366,11 @@ AlterCollation(AlterCollationStmt *stmt)
rel = table_open(CollationRelationId, RowExclusiveLock);
collOid = get_collation_oid(stmt->collname, false);
+ if (collOid == DEFAULT_COLLATION_OID)
+ ereport(ERROR,
+ (errmsg("cannot refresh version of default collation"),
+ errhint("Use ALTER DATABASE ... REFRESH COLLATION VERSION instead.")));
+
if (!pg_collation_ownercheck(collOid, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_COLLATION,
NameListToString(stmt->collname));
--
2.34.1
From c5240d690af1d8ee17b84d2d9235800869a0cee7 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jda...@postgresql.org>
Date: Sat, 29 Oct 2022 14:31:31 -0700
Subject: [PATCH] Add views: pg_collation_versions and
pg_collation_dependencies.
These new views are useful to understand when a collation version
changes, and which dependent objects may be affected.
---
doc/src/sgml/system-views.sgml | 166 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 48 ++++++++
src/test/regress/expected/rules.out | 50 ++++++++
3 files changed, 264 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 1ca7c3f9bf..cf48073d2f 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -549,6 +549,172 @@
</para>
</sect1>
+ <sect1 id="view-pg-collation-dependencies">
+ <title><structname>pg_collation_dependencies</structname></title>
+
+ <indexterm zone="view-pg-collation-dependencies">
+ <primary>pg_collation_dependencies</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_collation_dependencies</structname> shows the
+ dependencies (including transitive dependencies) of collations. It is
+ intended to be used to understand the potential impact of a change to a
+ collation due, for example, to a change in the collation provider library.
+ </para>
+
+ <para>
+ By default, the <structname>pg_collation_dependencies</structname> view can
+ be read only by superusers.
+ </para>
+
+ <table>
+ <title><structname>pg_collation_dependencies</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>classid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the system catalog the dependent object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific dependent object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>refcollid</structfield> <type>regcollation</type>
+ </para>
+ <para>
+ The collation on which the object depends.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>description</structfield> <type>text</type>
+ </para>
+ <para>
+ The description of the dependent object.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-collation-versions">
+ <title><structname>pg_collation_versions</structname></title>
+
+ <indexterm zone="view-pg-collation-versions">
+ <primary>pg_collation_dependencies</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_collation_versions</structname> shows the current
+ version as well as the actual version (obtained from the collation
+ provider) for each collation. It is intended to detect changes in the
+ collation provider library.
+ </para>
+
+ <para>
+ By default, the <structname>pg_collation_versions</structname> view can be
+ read only by superusers.
+ </para>
+
+ <table>
+ <title><structname>pg_collation_versions</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>collation_name</structfield> <type>name</type>
+ </para>
+ <para>
+ The collation name
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>provider</structfield> <type>text</type>
+ </para>
+ <para>
+ The collation provider (<literal>default</literal>,
+ <literal>libc</literal>, or <literal>icu</literal>). </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>version</structfield> <type>text</type>
+ </para>
+ <para>
+ The version string in the catalog, i.e. the version at the time the
+ collation or database was created, or the version last refreshed.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>version</structfield> <type>text</type>
+ </para>
+ <para>
+ The current version string obtained from the collation provider library
+ for the collation's locale name.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-config">
<title><structname>pg_config</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2d8104b090..a283d9a3c6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1313,3 +1313,51 @@ CREATE VIEW pg_stat_subscription_stats AS
ss.stats_reset
FROM pg_subscription as s,
pg_stat_get_subscription_stats(s.oid) as ss;
+
+CREATE VIEW pg_collation_versions AS
+ SELECT
+ collname AS collation_name,
+ CASE collprovider
+ WHEN 'd' THEN 'default'
+ WHEN 'c' THEN 'libc'
+ WHEN 'i' THEN 'icu'
+ END AS provider,
+ CASE WHEN oid = 'default'::regcollation THEN
+ (SELECT datcollversion FROM pg_database
+ WHERE datname = current_database())
+ ELSE collversion
+ END AS version,
+ pg_collation_actual_version(oid) AS actual_version
+ FROM pg_collation;
+
+CREATE OR REPLACE VIEW pg_collation_dependencies AS
+ WITH RECURSIVE
+ collation_dependencies(classid, objid, objsubid, refcollid) AS (
+ select d.classid, d.objid, d.objsubid,
+ refobjid::regcollation as refcollid
+ from pg_depend d
+ where refclassid='pg_collation'::regclass
+ union
+ select d.classid, d.objid, d.objsubid, cd.refcollid
+ from pg_depend d, collation_dependencies cd
+ where cd.classid = d.refclassid
+ and cd.objid = d.refobjid
+ and cd.objsubid = d.refobjsubid
+ )
+ SELECT cd.classid, cd.objid, cd.objsubid, cd.refcollid,
+ pg_describe_object(cd.classid, cd.objid, cd.objsubid) as description
+ FROM collation_dependencies cd
+ WHERE
+ -- ignore system objects
+ cd.objid >= 16384
+ AND CASE WHEN cd.classid = 'pg_class'::regclass THEN
+ -- ignore TOAST tables
+ (SELECT relkind <> 't' FROM pg_class c WHERE cd.objid = c.oid)
+ WHEN cd.classid = 'pg_trigger'::regclass THEN
+ -- ignore array types
+ (SELECT NOT tgisinternal FROM pg_trigger tg WHERE cd.objid = tg.oid)
+ WHEN cd.classid = 'pg_type'::regclass THEN
+ -- ignore array types
+ (SELECT typelem = 0 FROM pg_type t WHERE cd.objid = t.oid)
+ ELSE TRUE
+ END;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index bfcd8ac9a0..93fc91a4e2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1312,6 +1312,56 @@ pg_backend_memory_contexts| SELECT pg_get_backend_memory_contexts.name,
pg_get_backend_memory_contexts.free_chunks,
pg_get_backend_memory_contexts.used_bytes
FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
+pg_collation_dependencies| WITH RECURSIVE collation_dependencies(classid, objid, objsubid, refcollid) AS (
+ SELECT d.classid,
+ d.objid,
+ d.objsubid,
+ (d.refobjid)::regcollation AS refcollid
+ FROM pg_depend d
+ WHERE (d.refclassid = ('pg_collation'::regclass)::oid)
+ UNION
+ SELECT d.classid,
+ d.objid,
+ d.objsubid,
+ cd_1.refcollid
+ FROM pg_depend d,
+ collation_dependencies cd_1
+ WHERE ((cd_1.classid = d.refclassid) AND (cd_1.objid = d.refobjid) AND (cd_1.objsubid = d.refobjsubid))
+ )
+ SELECT cd.classid,
+ cd.objid,
+ cd.objsubid,
+ cd.refcollid,
+ pg_describe_object(cd.classid, cd.objid, cd.objsubid) AS description
+ FROM collation_dependencies cd
+ WHERE ((cd.objid >= (16384)::oid) AND
+ CASE
+ WHEN (cd.classid = ('pg_class'::regclass)::oid) THEN ( SELECT (c.relkind <> 't'::"char")
+ FROM pg_class c
+ WHERE (cd.objid = c.oid))
+ WHEN (cd.classid = ('pg_trigger'::regclass)::oid) THEN ( SELECT (NOT tg.tgisinternal)
+ FROM pg_trigger tg
+ WHERE (cd.objid = tg.oid))
+ WHEN (cd.classid = ('pg_type'::regclass)::oid) THEN ( SELECT (t.typelem = (0)::oid)
+ FROM pg_type t
+ WHERE (cd.objid = t.oid))
+ ELSE true
+ END);
+pg_collation_versions| SELECT pg_collation.collname AS collation_name,
+ CASE pg_collation.collprovider
+ WHEN 'd'::"char" THEN 'default'::text
+ WHEN 'c'::"char" THEN 'libc'::text
+ WHEN 'i'::"char" THEN 'icu'::text
+ ELSE NULL::text
+ END AS provider,
+ CASE
+ WHEN (pg_collation.oid = ('"default"'::regcollation)::oid) THEN ( SELECT pg_database.datcollversion
+ FROM pg_database
+ WHERE (pg_database.datname = current_database()))
+ ELSE pg_collation.collversion
+ END AS version,
+ pg_collation_actual_version(pg_collation.oid) AS actual_version
+ FROM pg_collation;
pg_config| SELECT pg_config.name,
pg_config.setting
FROM pg_config() pg_config(name, setting);
--
2.34.1
