Horton

Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.

Pavel Borisov <pashkin.elfe@gmail.com>

View thread
From: Pavel Borisov <pashkin.elfe@gmail.com>
To: Mark Dilger <mark.dilger@enterprisedb.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Noah Misch <noah@leadboat.com>, Peter Eisentraut <peter@eisentraut.org>, Aleksander Alekseev <aleksander@timescale.com>, Postgres hackers <pgsql-hackers@lists.postgresql.org>, Maxim Orlov <orlovmg@gmail.com>, Andres Freund <andres@anarazel.de>, Greg Stark <stark@mit.edu>, Julien Rouhaud <rjuju123@gmail.com>, David Steele <david@pgmasters.net>, Peter Geoghegan <pg@bowt.ie>, Maxim Orlov <m.orlov@postgrespro.ru>, lubennikovaav@gmail.com
Date: 2024-05-17T18:51:38Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. amcheck: Optimize speed of checking for unique constraint violation

  2. amcheck: Report an error when the next page to a leaf is not a leaf

  3. amcheck: Don't load the right sibling page into BtreeCheckState

  4. amcheck: Refactoring the storage of the last visible entry

  5. Teach contrib/amcheck to check the unique constraint violation

  6. Add macros in hash and btree AMs to get the special area of their pages

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

[1]
https://www.postgresql.org/message-id/CAH2-Wz%3DttG__BTZ-r5ccopBRb5evjg%3DzsF_o_3C5h4zRBA_LjQ%40mail.gmail.com