query to find indexes affected by locales upgrade does not work

by

The question:

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment