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

On Fri, 09 Jul 2021 at 17:05, Lætitia Avrot <laetitia(dot)avrot(at)gmail(dot)com> wrote:
> Hello,
>
> With a customer, we found out that when using `commit and chain`, statistics on the table were not updated. Here are the steps to reproduce (My customer saw this on Postgres 13, I confirmed it under current main version):
>
> laetitia=# laetitia=# select n_tup_ins from pg_stat_all_tables where relname = 'test';
>
> n_tup_ins
>
> -----------
>
> 17
>
> (1 row)
>
> laetitia=# begin;
>
> BEGIN
>
> laetitia=*# insert into test (value) values ('bla');
>
> INSERT 0 1
>
> laetitia=*# commit and chain;
>
> COMMIT
>
> laetitia=*# select n_tup_ins from pg_stat_all_tables where relname = 'test';
>
> n_tup_ins
>
> -----------
>
> 17
>
> (1 row)
>
> laetitia=*# commit;
>
> COMMIT
>
> laetitia=# select n_tup_ins from pg_stat_all_tables where relname = 'test';
>
> n_tup_ins
>
> -----------
>
> 18
>
> (1 row)
>
> Before issuing the last `commit`, I used another connection to check the value of the statistics from another transaction and it was not updated:
>
> laetitia=# select n_tup_ins from pg_stat_all_tables where relname = 'test';
>
> n_tup_ins
>
> -----------
>
> 17
>
> (1 row)
>
> Maybe it's not a bug and it's on purpose but I can't understand what would prevent the statistics collector from being triggered after a `commti and chain`.
>

After some analyze, I find the table statistics updated only when not within
a transaction. If you use COMMIT AND CHAIN, we still in a transaction, so the
statistics do not updated.

See src/backend/tcop/postgres.c:

else if (IsTransactionOrTransactionBlock()) <-------- After call COMMIT AND CHAIN, we come here.
{
set_ps_display("idle in transaction");
pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL);

/* Start the idle-in-transaction timer */
if (IdleInTransactionSessionTimeout > 0)
{
idle_in_transaction_timeout_enabled = true;
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
IdleInTransactionSessionTimeout);
}
}
else <-------- After call COMMIT, we come here.
{
/* Send out notify signals and transmit self-notifies */
ProcessCompletedNotifies();

/*
* Also process incoming notifies, if any. This is mostly to
* ensure stable behavior in tests: if any notifies were
* received during the just-finished transaction, they'll be
* seen by the client before ReadyForQuery is.
*/
if (notifyInterruptPending)
ProcessNotifyInterrupt();

pgstat_report_stat(false); <-------- Update statistics.

set_ps_display("idle");
pgstat_report_activity(STATE_IDLE, NULL);

/* Start the idle-session timer */
if (IdleSessionTimeout > 0)
{
idle_session_timeout_enabled = true;
enable_timeout_after(IDLE_SESSION_TIMEOUT,
IdleSessionTimeout);
}
}

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.