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

On Fri, 09 Jul 2021 at 19:02, Japin Li <japinli(at)hotmail(dot)com> wrote:
> 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);
> }
> }

Attached fixes it by call pgstat_report_stat() when we a in COMMIT AND CHAIN mode.
Any thoughts?

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