Re: Statistics updates is delayed when using `commit and chain`

Le lun. 12 juil. 2021 à 17:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> John Naylor <john(dot)naylor(at)enterprisedb(dot)com> writes:
> > I understand this functionality to be tracking what happens at the
> physical
> > level, in which case it is correct. In any case, the bug reported is
> clear
> > and changing that behavior is the focus here. About the patch:
>
> I do not think this is a bug at all. The behavior is, and always has
> been, that we report stats when we are about to wait for client input
> and are not inside a transaction. Likewise for NOTIFY. The proposed
> patch randomly changes that in a way that is very likely to break
> clients.
>
>
Hello Tom,

I understand your point of view. After re-reading the comments above
`pgstat_report_stat`, I found out it was pretty clear this function needs
to be called from the `postgres.c` file (in our case) and not from the
`xact.c` file and outside a transaction.
Anyhow, I still think we might want to change the behaviour of Postgres or
the documentation.

I have a customer using only `commit and chain` instead of `commit` for
their own reasons. When they do, the table's statistics are never updated.
So unless I'm mistaken, it means that the autovacuum daemon is never
triggered by the number of changes which leads to bloat.

So either `commit and chain` is badly used by the customer and, in that
case, we need to update the documentation to point out a correct use of
`commit and chain` or we need to change Postgres so that it gathers stats
after the commit but before the new transaction starts. I might need to
look for the SQL standard's definition of `commit and chain`, maybe the
start of the new transaction could be moved to another place so that we
come back to `postgres.c` outside of a transaction to gather the stats or
maybe gathering the stats can be moved after the end of the previous
transaction and before the start of a new transaction.

Have a nice day,

Lætitia