Re: Statistics updates is delayed when using `commit and chain` - Mailing list pgsql-bugs

On Fri, 09 Jul 2021 at 19:02, Japin Li <japinli@hotmail.com> wrote:
> On Fri, 09 Jul 2021 at 17:05, Lætitia Avrot <laetitia.avrot@gmail.com> wrote:
>> Hello,
>>
>> With a customer, we found out that when using `commit and chain`, statistics on the table were not updated. Here are
thesteps 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
transactionand 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
beingtriggered 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.