pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
4+ messages / 1 participants
[nested] [flat]

* pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
@ 2021-12-08 02:02  Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Paquier @ 2021-12-08 02:02 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/f99870dd867331f576a84e37438da86a866559c4

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(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
@ 2021-12-08 02:02  Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Paquier @ 2021-12-08 02:02 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_12_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/5ed74d874f4964af3cef0991d9406bedfc59281a

Modified Files
--------------
src/backend/access/heap/tuptoaster.c | 16 ++++++++++------
1 file changed, 10 insertions(+), 6 deletions(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
@ 2021-12-08 02:02  Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Paquier @ 2021-12-08 02:02 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/64ab21f0e5deb80ff67f953c6e3c8640a007a929

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(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY
@ 2021-12-08 02:02  Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Paquier @ 2021-12-08 02:02 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2021-12-08 02:02 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-08 02:02 pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY Michael Paquier <michael@paquier.xyz>
2021-12-08 02:02 pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY Michael Paquier <michael@paquier.xyz>
2021-12-08 02:02 pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY Michael Paquier <michael@paquier.xyz>
2021-12-08 02:02 pgsql: Fix corruption of toast indexes with REINDEX CONCURRENTLY Michael Paquier <michael@paquier.xyz>

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