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

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);
                }
            }


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