bt_index_parent_check and concurrently build indexes

Mihail Nikalayeu <michail.nikolaev@gmail.com>

From: Michail Nikolaev <michail.nikolaev@gmail.com>

To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>

Date: 2024-12-09T20:51:24Z

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 →

amcheck: Fix snapshot usage in bt_index_parent_check
- e1a327dc4dcb 17.8 landed
- dcddd6987197 15.16 landed
- 607a67c5e657 14.21 landed
- 3c83a2a0ace9 18.2 landed
- 1f28982e4094 19 (unreleased) landed
- 098a1fab8a9b 16.12 landed
- df93f94dda51 18.2 landed
- ce2f575b7cf4 17.8 landed
- 6bd469d26aca 19 (unreleased) landed
Teach contrib/amcheck to check the unique constraint violation
- 5ae2087202af 17.0 cited

Attachments

v1-0001-test-to-reproduce-issue-with-bt_index_parent_chec.patch (application/x-patch) patch v1-0001

Hello, everyone!

While working on [0], I encountered an issue involving a missing tuple in
an index that was built concurrently. The problem only occurred once, but
it caused me a significant amount of frustration. :)

After some time, I managed to find a way to reproduce the issue. It turns
out that bt_index_parent_check is not suitable for validating indexes built
concurrently. The reason is that bt_index_parent_check uses SnapshotAny
during the heap scan, whereas an MVCC snapshot is used for the index build.

I’ve attached a patch that reproduces the issue (it incorrectly reports the
index as invalid, even though it is actually valid).

I’m unsure of the best way to address this issue, but here are some
possible options:
* Simply update the documentation.
* Issue a WARNING if !tupleIsAlive.
* Modify bt_index_parent_check to use an MVCC snapshot for the heap scan

Best regards,
Mikhail.

[0]: https://commitfest.postgresql.org/51/4971/