pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Revert changes to CONCURRENTLY that "sped up" Xmin advance 2+ messages / 1 participants [nested] [flat]
* pgsql: Revert changes to CONCURRENTLY that "sped up" Xmin advance @ 2022-05-31 19:25 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 2+ messages in thread From: Alvaro Herrera @ 2022-05-31 19:25 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Revert changes to CONCURRENTLY that "sped up" Xmin advance This reverts commit d9d076222f5b "VACUUM: ignore indexing operations with CONCURRENTLY". These changes caused indexes created with the CONCURRENTLY option to miss heap tuples that were HOT-updated and HOT-pruned during the index creation. Before these changes, HOT pruning would have been prevented by the Xmin of the transaction creating the index, but because this change was precisely to allow the Xmin to move forward ignoring that backend, now other backends scanning the table can prune them. This is not a problem for VACUUM (which requires a lock that conflicts with a CREATE INDEX CONCURRENTLY operation), but HOT-prune can definitely occur. In other words, Xmin advancement was sped up, but at the cost of corrupting the resulting index. Regrettably, this means that the new feature in PG14 that RIC/CIC on very large tables no longer force VACUUM to retain very old tuples goes away. We might try to implement it again in a later release, but for now the risk of indexes missing tuples is too high and there's no easy fix. Backpatch to 14, where this change appeared. Reported-by: Peter Slavov <pet.slavov@gmail.com> Diagnosys-by: Andrey Borodin <x4mmm@yandex-team.ru> Diagnosys-by: Michael Paquier <michael@paquier.xyz> Diagnosys-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/17485-396609c6925b982d%40postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e28bb885196916b0a3d898ae4f2be0e38108d81b Modified Files -------------- doc/src/sgml/ref/create_index.sgml | 2 -- doc/src/sgml/ref/reindex.sgml | 2 -- src/backend/storage/ipc/procarray.c | 39 +++++++------------------------------ 3 files changed, 7 insertions(+), 36 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Revert changes to CONCURRENTLY that "sped up" Xmin advance @ 2022-05-31 19:25 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 2+ messages in thread From: Alvaro Herrera @ 2022-05-31 19:25 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Revert changes to CONCURRENTLY that "sped up" Xmin advance This reverts commit d9d076222f5b "VACUUM: ignore indexing operations with CONCURRENTLY". These changes caused indexes created with the CONCURRENTLY option to miss heap tuples that were HOT-updated and HOT-pruned during the index creation. Before these changes, HOT pruning would have been prevented by the Xmin of the transaction creating the index, but because this change was precisely to allow the Xmin to move forward ignoring that backend, now other backends scanning the table can prune them. This is not a problem for VACUUM (which requires a lock that conflicts with a CREATE INDEX CONCURRENTLY operation), but HOT-prune can definitely occur. In other words, Xmin advancement was sped up, but at the cost of corrupting the resulting index. Regrettably, this means that the new feature in PG14 that RIC/CIC on very large tables no longer force VACUUM to retain very old tuples goes away. We might try to implement it again in a later release, but for now the risk of indexes missing tuples is too high and there's no easy fix. Backpatch to 14, where this change appeared. Reported-by: Peter Slavov <pet.slavov@gmail.com> Diagnosys-by: Andrey Borodin <x4mmm@yandex-team.ru> Diagnosys-by: Michael Paquier <michael@paquier.xyz> Diagnosys-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/17485-396609c6925b982d%40postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/042b584c7f7d6216c54359c0ee0f613ba3b3d9c2 Modified Files -------------- doc/src/sgml/ref/create_index.sgml | 2 -- doc/src/sgml/ref/reindex.sgml | 2 -- src/backend/storage/ipc/procarray.c | 40 +++++++------------------------------ 3 files changed, 7 insertions(+), 37 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-05-31 19:25 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-05-31 19:25 pgsql: Revert changes to CONCURRENTLY that "sped up" Xmin advance Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-31 19:25 pgsql: Revert changes to CONCURRENTLY that "sped up" Xmin advance Alvaro Herrera <alvherre@alvh.no-ip.org>
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