Re: Question about VACUUM behavior with sub-transactions in stored procedures - Mailing list pgsql-hackers

From Кириллов Вячеслав
Subject Re: Question about VACUUM behavior with sub-transactions in stored procedures
Date
Msg-id 1730274211449.38554@diasoft.ru
Whole thread Raw
In response to Question about VACUUM behavior with sub-transactions in stored procedures  (Кириллов Вячеслав <vkirillov@diasoft.ru>)
List pgsql-hackers
Hello everyone,
I’d like to revisit the topic of auto VACUUM’s interaction with stored procedures that perform transactions, with a more technical clarification as suggested earlier.

Let’s consider the behavior of VACUUM and system table updates after transaction commits in procedures that frequently open and commit transactions.
As I understand, statistics updates in PostgreSQL, which VACUUM later analyzes, are performed in pgstat_report_stat, called within db/src/backend/tcop/postgres.c in the PostgresMain function. Specifically:
stats_timeout = pgstat_report_stat(false);
if (stats_timeout > 0) { if (!get_timeout_active(IDLE_STATS_UPDATE_TIMEOUT)) enable_timeout_after(IDLE_STATS_UPDATE_TIMEOUT, stats_timeout); } else { /* all stats flushed, no need for the timeout */ if (get_timeout_active(IDLE_STATS_UPDATE_TIMEOUT)) disable_timeout(IDLE_STATS_UPDATE_TIMEOUT, false); }
Inside procedures, when _SPI_commit is called in db/src/backend/executor/spi.c, the main command responsible for completing a transaction is CommitTransactionCommand(). My question is the following:​
  1. Is it expected behavior that system table updates are deferred until all nested transactions are complete? This would mean that auto VACUUM might not account for dead tuples accumulated during procedure execution until the entire main transaction is finished.
  2. Is it possible or advisable to call pgstat_report_stat after each CommitTransactionCommand() within procedures so that auto VACUUM can track intermediate changes and prevent an accumulation of dead rows?
  3. To what extent would this approach be viable in terms of performance and correctness?
 
I look forward to any insights and advice you can offer on this matter.

Best regards,
Vyacheslav Kirillov​


pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: unicode test programs don't build with meson
Next
From: Peter Eisentraut
Date:
Subject: Re: Allowing pg_recvlogical to create temporary replication slots