From: | rihad <rihad(at)mail(dot)ru> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices |
Date: | 2017-12-22 15:01:58 |
Message-ID: | 36a1c887-cfdd-14f2-9b65-d2ac5440eed0@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/22/2017 05:32 PM, Peter Geoghegan wrote:
> On Fri, Dec 22, 2017 at 11:56 AM, rihad <rihad(at)mail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | rihad | 2017-12-22 15:07:48 | Re: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices |
Previous Message | Laurenz Albe | 2017-12-22 14:55:00 | Re: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices |