PostgreSQL 的 pg_collation_actual_version 函数-CSDN博客

本文链接：https://blog.csdn.net/lee_vincent1/article/details/147859570

PostgreSQL 的 pg_collation_actual_version 函数

pg_collation_actual_version 是 PostgreSQL 中用于检查排序规则实际版本信息的函数，主要与 ICU (International Components for Unicode) 排序规则相关。

函数基本概念

函数定义

pg_collation_actual_version(collation_oid oid) RETURNS text

参数说明

collation_oid : 排序规则的系统标识符（OID）

返回值

返回排序规则的实际版本字符串，如果排序规则不是 ICU 类型则返回 NULL

功能描述

此函数用于检查 ICU 排序规则的实际版本，与 pg_collation 目录中记录的预期版本进行对比，常用于检测排序规则是否因 ICU 库升级而发生变化。

使用场景

场景1：检查排序规则版本

-- 查找所有ICU排序规则及其版本信息
SELECT 
    c.oid,
    c.collname,
    c.collversion AS expected_version,
    pg_collation_actual_version(c.oid) AS actual_version
FROM 
    pg_collation c
WHERE 
    c.collprovider = 'i'  -- 'i'表示ICU排序规则
ORDER BY 
    c.collname;

场景2：检测版本不匹配的排序规则

-- 找出实际版本与预期版本不匹配的排序规则
SELECT 
    c.oid,
    c.collname,
    c.collversion AS expected_version,
    pg_collation_actual_version(c.oid) AS actual_version
FROM 
    pg_collation c
WHERE 
    c.collprovider = 'i' AND
    pg_collation_actual_version(c.oid) IS DISTINCT FROM c.collversion;

ICU 排序规则版本管理

PostgreSQL 使用 ICU 库提供国际化排序规则支持，版本管理机制如下：

预期版本 (collversion): 存储在 pg_collation 系统目录中
实际版本: 通过 pg_collation_actual_version() 函数获取
版本不匹配处理:
- 当 ICU 库升级后，实际版本可能变化
- 需要重新创建依赖这些排序规则的索引、物化视图等对象

实际应用案例

案例1：ICU 升级后的数据库检查

-- 在ICU库升级后运行此检查
DO $$
DECLARE
    mismatch_count integer;
BEGIN
    SELECT count(*) INTO mismatch_count
    FROM pg_collation
    WHERE collprovider = 'i' AND
          pg_collation_actual_version(oid) IS DISTINCT FROM collversion;
    
    IF mismatch_count > 0 THEN
        RAISE WARNING 'Found % ICU collations with version mismatch', mismatch_count;
        RAISE NOTICE 'Run REINDEX DATABASE and refresh materialized views that use these collations';
    ELSE
        RAISE NOTICE 'All ICU collation versions match';
    END IF;
END $$;

案例2：自动化重建受影响对象

-- 生成重建索引的脚本
SELECT 
    format('REINDEX INDEX CONCURRENTLY %I.%I;', 
           n.nspname, c.relname) AS reindex_command
FROM 
    pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_collation col ON col.oid = ANY(i.indcollation)
WHERE 
    col.collprovider = 'i' AND
    pg_collation_actual_version(col.oid) IS DISTINCT FROM col.collversion;

对象	描述
pg_collation	系统目录，存储所有排序规则信息
pg_import_system_collations()	导入系统排序规则函数
collprovider 列	排序规则提供者 (i=ICU, c=libc, d=数据库默认)

注意事项

版本变化影响:
- 排序规则版本变化可能导致排序结果不同
- 影响索引扫描结果的顺序
- 可能导致 DISTINCT 、 GROUP BY 、 ORDER BY 等操作结果变化
维护窗口:
- 重建索引可能需要停机或影响性能
- 建议在维护窗口执行相关操作
云数据库考虑:
- 在云托管服务中，ICU 版本可能由服务商控制
- 检查服务商文档了解特定版本管理策略
PostgreSQL 版本差异:
- ICU 支持从 PostgreSQL 10 开始完善
- 函数在不同版本中可能有细微差异