On Fri, Dec 22, 2017 at 12:53 PM, rihad <ri...@mail.ru> wrote: > 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. > > > What platform are you on, how was PostgreSQL installed, and exactly how was pg_upgrade executed? (or is, since it's reproducible)
-- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
