agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Correct on-access VM setting heuristic 2+ messages / 1 participants [nested] [flat]
* pgsql: Correct on-access VM setting heuristic @ 2026-09-23 20:40 Melanie Plageman <melanieplageman@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Melanie Plageman @ 2026-09-23 20:40 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Correct on-access VM setting heuristic The heuristic to avoid setting the VM during on-access pruning when doing so would emit an extra FPI missed a few cases. First it missed temp and unlogged tables. Those will never emit an FPI, so they can always set the VM if the page is all-visible. It also missed that if hint bits are not WAL-logged, setting only the VM passes REGBUF_NO_IMAGE for an already WAL-logged heap page, forbidding a heap FPI in the WAL record. A page that has never been WAL-logged still requires a heap FPI. The third is more subtle: If the page is all-visible, the new prune xid will be InvalidTransactionId. On-access pruning only executes when the current pd_prune_xid is valid and visible. So, when on-access pruning finds the page all-visible, it will always clear pd_prune_xid, modifying the page. Knowing this means we can set the VM without emitting an extra heap FPI in more cases. When hint bits are WAL-logged, if the heap buffer is clean, modifying pd_prune_xid will emit an FPI if one is required. There is no reason to try to avoid an FPI by not setting the VM. So, set the VM in this case. However, when the heap buffer is already dirty, modifying pd_prune_xid can avoid an FPI; so if the page hasn't been logged since the last checkpoint, setting it all-visible will emit an extra heap page FPI. We will still avoid setting the VM in this case. This is being backpatched to 19 because since 378a216187ae pd_prune_xid is set on insert, and if we execute a prune cycle and skip setting the VM because of an incorrect heuristic, we have added new wasted work in PG 19. This commit adds some tests covering these cases. It also updates one of the existing temp table tests to avoid exceeding the pin limit. Setting the VM and FSM on-access when querying temp tables takes more local pins and can run into the limit with fewer heap buffers pinned. Reported-by: Melanie Plageman <melanieplageman@gmail.com> Author: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Andrey Borodin <x4mmm@yandex-team.ru> Discussion: https://postgr.es/m/CAAKRu_amj7qLF4c=9ijd=708Fu2G8gg-2EqwBu=aCdAHU2sPHg@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/4a2f5533f3ceb20727686ed5ef38107d6b295e33 Modified Files -------------- contrib/pg_visibility/expected/pg_visibility.out | 78 ++++++++++++++++++++++++ contrib/pg_visibility/sql/pg_visibility.sql | 36 +++++++++++ src/backend/access/heap/pruneheap.c | 37 ++++++++--- src/test/regress/expected/temp.out | 2 +- src/test/regress/sql/temp.sql | 2 +- 5 files changed, 146 insertions(+), 9 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Correct on-access VM setting heuristic @ 2026-09-23 20:44 Melanie Plageman <melanieplageman@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Melanie Plageman @ 2026-09-23 20:44 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Correct on-access VM setting heuristic The heuristic to avoid setting the VM during on-access pruning when doing so would emit an extra FPI missed a few cases. First it missed temp and unlogged tables. Those will never emit an FPI, so they can always set the VM if the page is all-visible. It also missed that if hint bits are not WAL-logged, setting only the VM passes REGBUF_NO_IMAGE for an already WAL-logged heap page, forbidding a heap FPI in the WAL record. A page that has never been WAL-logged still requires a heap FPI. The third is more subtle: If the page is all-visible, the new prune xid will be InvalidTransactionId. On-access pruning only executes when the current pd_prune_xid is valid and visible. So, when on-access pruning finds the page all-visible, it will always clear pd_prune_xid, modifying the page. Knowing this means we can set the VM without emitting an extra heap FPI in more cases. When hint bits are WAL-logged, if the heap buffer is clean, modifying pd_prune_xid will emit an FPI if one is required. There is no reason to try to avoid an FPI by not setting the VM. So, set the VM in this case. However, when the heap buffer is already dirty, modifying pd_prune_xid can avoid an FPI; so if the page hasn't been logged since the last checkpoint, setting it all-visible will emit an extra heap page FPI. We will still avoid setting the VM in this case. This is being backpatched to 19 because since 378a216187ae pd_prune_xid is set on insert, and if we execute a prune cycle and skip setting the VM because of an incorrect heuristic, we have added new wasted work in PG 19. This commit adds some tests covering these cases. It also updates one of the existing temp table tests to avoid exceeding the pin limit. Setting the VM and FSM on-access when querying temp tables takes more local pins and can run into the limit with fewer heap buffers pinned. Reported-by: Melanie Plageman <melanieplageman@gmail.com> Author: Melanie Plageman <melanieplageman@gmail.com> Reviewed-by: Andrey Borodin <x4mmm@yandex-team.ru> Discussion: https://postgr.es/m/CAAKRu_amj7qLF4c=9ijd=708Fu2G8gg-2EqwBu=aCdAHU2sPHg@mail.gmail.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/13243ae44cf6d81f666223d3385bdf0a824cd07d Modified Files -------------- contrib/pg_visibility/expected/pg_visibility.out | 78 ++++++++++++++++++++++++ contrib/pg_visibility/sql/pg_visibility.sql | 36 +++++++++++ src/backend/access/heap/pruneheap.c | 37 ++++++++--- src/test/regress/expected/temp.out | 2 +- src/test/regress/sql/temp.sql | 2 +- 5 files changed, 146 insertions(+), 9 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-23 20:44 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-09-23 20:40 pgsql: Correct on-access VM setting heuristic Melanie Plageman <melanieplageman@gmail.com> 2026-09-23 20:44 pgsql: Correct on-access VM setting heuristic Melanie Plageman <melanieplageman@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox