Re: COLLATION update in 13.1

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

On Mon, Feb 24, 2025 at 6:53 AM Matthias Apitz <gurucubano@xxxxxxxxxxxxxx> wrote:

[snip] 

pgsql -Usisis sisis

sisis=# REINDEX (VERBOSE) DATABASE sisis;
sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
ALTER COLLATION

Correct?

Just reindex those with text columns.

create or replace view dba.all_indices_types as
    select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as table_name
            , ndcl.relname as index_name
            , array_agg(ty.typname order by att.attnum) as index_types
    from pg_class ndcl
        inner join pg_index nd
            on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
        inner join pg_class tbcl
            on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
        inner join pg_attribute att
            on att.attrelid = nd.indexrelid
        inner join pg_type ty
            on att.atttypid = ty.oid
    where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
    group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
            , ndcl.relname
    order by 1, 2;

select * from dba.all_indices_types where index_types && '{"text","varchar","char"}';

(This view might not handle indices on the parents of declared-partition tables.)

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!