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
________________________________
От: David G. Johnston <david.g.johns...@gmail.com>
Отправлено: 21 октября 2024 г. 16:55
Кому: Кириллов Вячеслав
Копия: pgsql-hack...@postgresql.org
Тема: Re: Question about VACUUM behavior with sub-transactions in stored
procedures
On Monday, October 21, 2024, Кириллов Вячеслав
<vkiril...@diasoft.ru<mailto:vkiril...@diasoft.ru>> wrote:
I have a question regarding the behavior of the auto VACUUM in PostgreSQL in
the context of using stored procedures with sub-transactions.
This is a general usage inquiry not suited to discussion on -hackers. We have
a -general mailing list to discuss how to use the product. This list is for
discussing patches.
Here is the scenario: we have several stored procedures that modify or update
table data. These procedures use sub-transactions, which are committed via
COMMIT.
This isn't how sub-transactions work. They are created mainly by save points
and are not independently committed (by the user in SQL). What you are using
are full transactions.
https://www.postgresql.org/docs/17/plpgsql-transactions.html
David J.
