On 12/22/2017 05:32 PM, Peter Geoghegan wrote:
On Fri, Dec 22, 2017 at 11:56 AM, rihad <rihad@xxxxxxx> 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.
