Re: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices

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.