pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices - Mailing list pgsql-general

Hi there, this is a reproducible error. We recently pg_upgraded our 
production database to 10.1 from 9.6.6. The upgrade runs fine with the 
suggestion to analyze all data.

[rihad@postgres-10-test]$ cat analyze_new_cluster.sh
#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy.  When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo

echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics 
generation.'
echo

echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo '    "/10.1/bin/vacuumdb" --all --analyze-only'
echo

"/10.1/bin/vacuumdb" --all --analyze-in-stages
echo

echo 'Done'


which we run after the upgrade. It doesn't matter if we do the analyze 
so in a test environment with no activity or run it concurrently with 
the already started production queries.


The problem:


Some of our tables have indices of the form:


     "index_translations_on_locale_and_key" UNIQUE, btree (locale, key)

  locale     | character varying(255)      |           | |
  key        | character varying(255)      |           |          |


or


     "index_users_on_email_and_type" UNIQUE, btree (email, type)

  email           | character varying(255)      |           | not null | 
''::character varying
  type            | character varying           |           | not null |


(these are different tables)

Trying to find data using the specified indices fails to find matching rows:


foo=# select * from translations where locale='de' and 
key='extranet.options.places.age_brackets_hints.a';
  id | locale | key | value | created_at | updated_at | resolved
----+--------+-----+-------+------------+------------+----------
(0 rows)

foo=# explain select * from translations where locale='de' and 
key='extranet.options.places.age_brackets_hints.a';
                                                        QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
  Index Scan using index_translations_on_locale_and_key on translations  
(cost=0.41..2.63 rows=1 width=234)
    Index Cond: (((locale)::text = 'de'::text) AND ((key)::text = 
'extranet.options.places.age_brackets_hints.a'::text))
(2 rows)


reindexing the table fixes the issue:


foo=# reindex index index_translations_on_locale_and_key ;
REINDEX
foo=# select * from translations where locale='de' and 
key='extranet.options.places.age_brackets_hints.a';
    id   | locale |                     key |                        
value                        | created_at         |         
updated_at         | resolved

--------+--------+----------------------------------------------+-----------------------------------------------------+----------------------------+----------------------------+----------
  136373 | de     | extranet.options.places.age_brackets_hints.a | Alter 
für einen vollen Gast-Tarif, z.B ab 12 Jahre  | 2017-08-22 
11:27:27.774259 | 2017-09-02 09:05:45.244927 | f
(1 row)

foo=# explain select * from translations where locale='de' and 
key='extranet.options.places.age_brackets_hints.a';
                                                        QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
  Index Scan using index_translations_on_locale_and_key on translations  
(cost=0.41..2.63 rows=1 width=234)
    Index Cond: (((locale)::text = 'de'::text) AND ((key)::text = 
'extranet.options.places.age_brackets_hints.a'::text))
(2 rows)



The upgrade guide states that only hash indices should be rebuilt after 
the upgrade, not btree ones.