pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / 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