query to find indexes affected by locales upgrade does not work



  • 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?



  • I got an answer in #postgresql on irc.

    PostgreSQL too old for that query
    just remove "collprovider and"

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2