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

On 12/22/2017 05:32 PM, Peter Geoghegan wrote:
> On Fri, Dec 22, 2017 at 11:56 AM, rihad <rihad@mail.ru> wrote:
>> I forgot to mention the real problem: the mentioned unique constraint didn't
>> work and allowed duplicate rows to get inserted into the table until the
>> duplicates were manually removed the the index was rebuilt.
> You should run amcheck functions on both environments, against all
> indexes, to see where the inconsistency arose, and to isolate any
> other inconsistencies that may have been missed. While amcheck is
> available from contrib in Postgres 10, you can get a version that will
> work on other versions through OS packages for most major Linux
> distributions. See:
>
> https://github.com/petergeoghegan/amcheck
>
> Note also that only this external version has the "heapallindexed" check.
>
Hm, interesting. It doesn't look like FreeBSD ports include the amcheck 
extension, but I could still use the versions bundled with postgres 
10.1-contrib.

Also, the version included doesn't allow a second boolean argument.

I first ran it on a reindexed index, which didn't show any problems, as 
expected.

Then I ran it on an unfixed broken index.

foo=# create extension amcheck;
CREATE EXTENSION

foo=# select bt_index_check('index_translations_on_locale_and_key');
  bt_index_check
----------------

(1 row)

foo=# select bt_index_check('index_users_on_email_and_type');
ERROR:  item order invariant violated for index 
"index_users_on_email_and_type"
DETAIL:  Lower index tid=(3,25) (points to index tid=(26,1)) higher 
index tid=(3,26) (points to index tid=(27,1)) page lsn=0/0.