query to find indexes affected by locales upgrade does not work

All we need is an easy explanation of the problem, so here it is.

While upgrading a system from Debian stretch to Debian buster, I got a warning about collation changes with a link to https://wiki.postgresql.org/wiki/Locale_data_changes the database is currently running postgresql 9.6 from Debian stretch.

That page tells me I can find out which indexes in my database are affected by using the query

SELECT 
    indrelid::regclass::text, 
    indexrelid::regclass::text, 
    collname, 
    pg_get_indexdef(indexrelid) 
FROM 
    (SELECT indexrelid, 
            indrelid, 
            indcollation[i] coll 
     FROM 
            pg_index, generate_subscripts(indcollation, 1) g(i)) s 
JOIN 
    pg_collation c ON coll=c.oid
WHERE 
    collprovider IN ('d', 'c') 
    AND collname NOT IN ('C', 'POSIX');

However when I connect to my database and run the query I get.

ERROR: column "collprovider" does not exist
LINE 4: WHERE collprovider IN (‘d’, ‘c’) AND collname NOT IN (‘C’, ‘…

How can I find out what if-any indexes in my databases are affected?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I got an answer in #postgresql on irc.

<Myon> PostgreSQL too old for that query
<Myon> just remove "collprovider and"

After removing "collprovider IN (‘d’, ‘c’) AND" from the query it ran successfully.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply