pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paquier <michael@paquier.xyz>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
Date: Wed, 08 Dec 2021 02:02:40 +0000
Message-ID: <E1mumI4-0001Zt-QI@gemulon.postgresql.org> (raw)

Fix corruption of toast indexes with REINDEX CONCURRENTLY

REINDEX CONCURRENTLY run on a toast index or a toast relation could
corrupt the target indexes rebuilt, as a backend running in parallel
that manipulates toast values would directly release the lock on the
toast relation when its local operation is done, rather than releasing
the lock once the transaction that manipulated the toast values
committed.

The fix done here is simple: we now hold a ROW EXCLUSIVE lock on the
toast relation when saving or deleting a toast value until the
transaction working on them is committed, so as a concurrent reindex
happening in parallel would be able to wait for any activity and see any
new rows inserted (or deleted).

An isolation test is added to check after the case fixed here, which is
a bit fancy by design as it relies on allow_system_table_mods to rename
the toast table and its index to fixed names.  This way, it is possible
to reindex them directly without any dependency on the OID of the
underlying relation.  Note that this could not use a DO block either, as
REINDEX CONCURRENTLY cannot be run in a transaction block.  The test is
backpatched down to 13, where it is possible, thanks to c4a7a39, to use
allow_system_table_mods in a test suite.

Reported-by: Alexey Ermakov
Analyzed-by: Andres Freund, Noah Misch
Author: Michael Paquier
Reviewed-by: Nathan Bossart
Discussion: https://postgr.es/m/17268-d2fb426e0895abd4@postgresql.org
Backpatch-through: 12

Branch
------
REL_13_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/9acea52ea3d48627f12b9a2b4498197d3b05fe2e

Modified Files
--------------
src/backend/access/common/toast_internals.c        |  16 +-
.../expected/reindex-concurrently-toast.out        | 775 +++++++++++++++++++++
src/test/isolation/isolation_schedule              |   1 +
.../specs/reindex-concurrently-toast.spec          | 119 ++++
4 files changed, 905 insertions(+), 6 deletions(-)



view thread (4+ messages)

Message-ID: <E1mumI4-0001Zt-QI@gemulon.postgresql.org>
Permalink:  ../E1mumI4-0001Zt-QI@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1mumI4-0001Zt-QI@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: michael@paquier.xyz
  Subject: Re: pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
  In-Reply-To: <E1mumI4-0001Zt-QI@gemulon.postgresql.org>
  References: <E1mumI4-0001Zt-QI@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox