public inbox for [email protected]
help / color / mirror / Atom feeddocs: Include database collation check on SQL from alter_collation.sgml
2+ messages / 1 participants
[nested] [flat]
* docs: Include database collation check on SQL from alter_collation.sgml
@ 2026-04-09 18:00 Matheus Alcantara <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Matheus Alcantara @ 2026-04-09 18:00 UTC (permalink / raw)
To: [email protected]
Hi,
The ALTER COLLATION documentation section include a SQL that can be used
to identity all collations in the current database that need to be
refreshed due to a collation version miss match and the objects that
depend on them. However if there is objects that use the database
collation these objects are not returned by the query.
The attached patch change the query to include the database collation
check to report collation version miss match for objects that use the
database default collation as they are not stored on pg_depend.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From e44674dfaee27bc449e0f3de65a0925851a598a3 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Thu, 9 Apr 2026 14:09:05 -0300
Subject: [PATCH v1 1/2] docs: Include database collation check on SQL from
ALTER COLLATION
Previously the alter_collation.sgml documentation section include a SQL
that can be used to identity all collations in the current database that
need to be refreshed due to a collation version miss match and the
objects that depend on them. However if there is objects that use the
database collation these objects are not returned by the query.
This commit improve the query on alter_collation.sgml to include the
database collation version check too to report such cases.
---
doc/src/sgml/ref/alter_collation.sgml | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml
index a40a31442a8..f0c3af20b95 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -164,7 +164,13 @@ SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
- WHERE c.collversion <> pg_collation_actual_version(c.oid)
+ JOIN pg_database db ON db.datname = current_database()
+ WHERE c.collprovider IN ('d', 'c')
+ AND (
+ (c.collprovider = 'd' AND db.datcollversion <> pg_database_collation_actual_version(db.oid))
+ OR
+ (c.collprovider = 'c' AND c.collversion <> pg_collation_actual_version(c.oid))
+ )
ORDER BY 1, 2;
]]></programlisting></para>
</refsect1>
--
2.53.0
Attachments:
[text/plain] v1-0001-docs-Include-database-collation-check-on-SQL-from.patch (1.7K, 2-v1-0001-docs-Include-database-collation-check-on-SQL-from.patch)
download | inline diff:
From e44674dfaee27bc449e0f3de65a0925851a598a3 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Thu, 9 Apr 2026 14:09:05 -0300
Subject: [PATCH v1 1/2] docs: Include database collation check on SQL from
ALTER COLLATION
Previously the alter_collation.sgml documentation section include a SQL
that can be used to identity all collations in the current database that
need to be refreshed due to a collation version miss match and the
objects that depend on them. However if there is objects that use the
database collation these objects are not returned by the query.
This commit improve the query on alter_collation.sgml to include the
database collation version check too to report such cases.
---
doc/src/sgml/ref/alter_collation.sgml | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml
index a40a31442a8..f0c3af20b95 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -164,7 +164,13 @@ SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
- WHERE c.collversion <> pg_collation_actual_version(c.oid)
+ JOIN pg_database db ON db.datname = current_database()
+ WHERE c.collprovider IN ('d', 'c')
+ AND (
+ (c.collprovider = 'd' AND db.datcollversion <> pg_database_collation_actual_version(db.oid))
+ OR
+ (c.collprovider = 'c' AND c.collversion <> pg_collation_actual_version(c.oid))
+ )
ORDER BY 1, 2;
]]></programlisting></para>
</refsect1>
--
2.53.0
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: docs: Include database collation check on SQL from alter_collation.sgml
@ 2026-04-13 21:08 Matheus Alcantara <[email protected]>
parent: Matheus Alcantara <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Matheus Alcantara @ 2026-04-13 21:08 UTC (permalink / raw)
To: [email protected]
On Thu Apr 9, 2026 at 3:00 PM -03, Matheus Alcantara wrote:
> Hi,
>
> The ALTER COLLATION documentation section include a SQL that can be used
> to identity all collations in the current database that need to be
> refreshed due to a collation version miss match and the objects that
> depend on them. However if there is objects that use the database
> collation these objects are not returned by the query.
>
> The attached patch change the query to include the database collation
> check to report collation version miss match for objects that use the
> database default collation as they are not stored on pg_depend.
>
The proposed SQL on the v1 patch still don't handle the cases where an
index is using the database default collation.
The new v2 attached handle it properly. The new query is based on the
query suggested on Locale data changes wiki page [1] but with some
changes to check the collation version mismatch.
[1] https://wiki.postgresql.org/wiki/Locale_data_changes
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From d7fec1cf714d7688b256edcdbd3a683d923a6465 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Thu, 9 Apr 2026 14:09:05 -0300
Subject: [PATCH v2] docs: Include database collation check on SQL from ALTER
COLLATION
Previously the alter_collation.sgml documentation section include a SQL
that can be used to identity all collations in the current database that
need to be refreshed due to a collation version miss match and the
objects that depend on them. However if there is objects that use the
database collation these objects are not returned by the query.
This commit improve the query on alter_collation.sgml to include the
database collation version check too to report such cases.
---
doc/src/sgml/ref/alter_collation.sgml | 26 ++++++++++++++++++++------
1 file changed, 20 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml
index a40a31442a8..6c40d1fb26f 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -160,12 +160,26 @@ HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg
The following query can be used to identify all collations in the current
database that need to be refreshed and the objects that depend on them:
<programlisting><![CDATA[
-SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
- pg_describe_object(classid, objid, objsubid) AS "Object"
- FROM pg_depend d JOIN pg_collation c
- ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
- WHERE c.collversion <> pg_collation_actual_version(c.oid)
- ORDER BY 1, 2;
+SELECT DISTINCT
+ currentdb AS database,
+ indrelid::regclass::text AS table,
+ indexrelid::regclass::text AS index,
+ collname,
+ collprovider,
+ CASE WHEN collprovider='d' THEN datcollversion ELSE collversion END AS collation_version,
+ CASE WHEN collprovider='d' THEN pg_database_collation_actual_version(d.oid) ELSE pg_collation_actual_version(c.oid) END AS actual_collation_version,
+ pg_get_indexdef(indexrelid) AS index_definition
+FROM
+ (SELECT indexrelid, indrelid, indcollation[i] coll, current_database() AS currentdb FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
+ JOIN pg_collation c ON coll=c.oid
+ JOIN pg_database d ON currentdb=d.datname
+WHERE
+ (
+ ((collprovider='d') AND (d.datlocprovider='c') AND d.datcollversion <> pg_database_collation_actual_version(d.oid))
+ OR
+ ((collprovider='c') AND collversion <> pg_collation_actual_version(c.oid) )
+ ) AND
+ collname NOT IN ('C', 'POSIX');
]]></programlisting></para>
</refsect1>
--
2.53.0
Attachments:
[text/plain] v2-0001-docs-Include-database-collation-check-on-SQL-from.patch (2.6K, 2-v2-0001-docs-Include-database-collation-check-on-SQL-from.patch)
download | inline diff:
From d7fec1cf714d7688b256edcdbd3a683d923a6465 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Thu, 9 Apr 2026 14:09:05 -0300
Subject: [PATCH v2] docs: Include database collation check on SQL from ALTER
COLLATION
Previously the alter_collation.sgml documentation section include a SQL
that can be used to identity all collations in the current database that
need to be refreshed due to a collation version miss match and the
objects that depend on them. However if there is objects that use the
database collation these objects are not returned by the query.
This commit improve the query on alter_collation.sgml to include the
database collation version check too to report such cases.
---
doc/src/sgml/ref/alter_collation.sgml | 26 ++++++++++++++++++++------
1 file changed, 20 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml
index a40a31442a8..6c40d1fb26f 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -160,12 +160,26 @@ HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg
The following query can be used to identify all collations in the current
database that need to be refreshed and the objects that depend on them:
<programlisting><![CDATA[
-SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
- pg_describe_object(classid, objid, objsubid) AS "Object"
- FROM pg_depend d JOIN pg_collation c
- ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
- WHERE c.collversion <> pg_collation_actual_version(c.oid)
- ORDER BY 1, 2;
+SELECT DISTINCT
+ currentdb AS database,
+ indrelid::regclass::text AS table,
+ indexrelid::regclass::text AS index,
+ collname,
+ collprovider,
+ CASE WHEN collprovider='d' THEN datcollversion ELSE collversion END AS collation_version,
+ CASE WHEN collprovider='d' THEN pg_database_collation_actual_version(d.oid) ELSE pg_collation_actual_version(c.oid) END AS actual_collation_version,
+ pg_get_indexdef(indexrelid) AS index_definition
+FROM
+ (SELECT indexrelid, indrelid, indcollation[i] coll, current_database() AS currentdb FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
+ JOIN pg_collation c ON coll=c.oid
+ JOIN pg_database d ON currentdb=d.datname
+WHERE
+ (
+ ((collprovider='d') AND (d.datlocprovider='c') AND d.datcollversion <> pg_database_collation_actual_version(d.oid))
+ OR
+ ((collprovider='c') AND collversion <> pg_collation_actual_version(c.oid) )
+ ) AND
+ collname NOT IN ('C', 'POSIX');
]]></programlisting></para>
</refsect1>
--
2.53.0
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-13 21:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-09 18:00 docs: Include database collation check on SQL from alter_collation.sgml Matheus Alcantara <[email protected]>
2026-04-13 21:08 ` Matheus Alcantara <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox