Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index. - Mailing list pgsql-hackers

From Pavel Borisov
Subject Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.
Date
Msg-id CALT9ZEHYM_zye46Y1ZmC=Sq5Ky-=uUgOjtQ_31Mzu1DbGn3=6g@mail.gmail.com
Whole thread Raw
In response to Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.  (Mark Dilger <mark.dilger@enterprisedb.com>)
Responses Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.
List pgsql-hackers
Hi, Mark!
The documentation in https://www.postgresql.org/docs/devel/amcheck.html#AMCHECK-FUNCTIONS is ambiguous:

"bt_index_check does not verify invariants that span child/parent relationships, but will verify the presence of all heap tuples as index tuples within the index when heapallindexed is true. When checkunique is true bt_index_check will check that no more than one among duplicate entries in unique index is visible. When a routine, lightweight test for corruption is required in a live production environment, using bt_index_check often provides the best trade-off between thoroughness of verification and limiting the impact on application performance and availability."

The second sentence, "When checkunique is true bt_index_check will check that no more than one among duplicate entries in unique index is visible." is not strictly true, as it won't check if the violation spans a page boundary.  
Amcheck with checkunique option does check uniqueness violation between pages. But it doesn't warranty detection of cross page uniqueness violations in extremely rare cases when the first equal index entry on the next page corresponds to tuple that is not visible (e.g. dead). In this, I followed the Peter's notion [1] that checking across a number of dead equal entries that could theoretically span even across many pages is an unneeded code complication and amcheck is not a tool that provides any warranty when checking an index.

I'm not against docs modification in any way that clarifies its exact usage and limitations.

Kind regards,
Pavel Borisov

pgsql-hackers by date:

Previous
From: "Imseih (AWS), Sami"
Date:
Subject: Re: problems with "Shared Memory and Semaphores" section of docs
Next
From: Robert Haas
Date:
Subject: Re: Comments on Custom RMGRs