Re: [HACKERS] CLUSTER command progress monitor

Hi Michael, Alvaro and Robert!

On 2019/08/14 11:52, Michael Paquier wrote:
> On Wed, Aug 14, 2019 at 11:38:01AM +0900, Tatsuro Yamada wrote:
>> On 2019/08/13 14:40, Tatsuro Yamada wrote:
>>> On 2019/08/02 3:43, Alvaro Herrera wrote:
>>>> Hmm, I'm trying this out now and I don't see the index_rebuild_count
>>>> ever go up.  I think it's because the indexes are built using parallel
>>>> index build ... or maybe it was the table AM changes that moved things
>>>> around, not sure.  There's a period at the end when the CLUSTER command
>>>> keeps working, but it's gone from pg_stat_progress_cluster.
>>>
>>> Thanks for your report.
>>> I'll investigate it. :)
>>
>> I did "git bisect" and found the commit:
>>
>>   03f9e5cba0ee1633af4abe734504df50af46fbd8
>>   Report progress of REINDEX operations
> 
> I am adding an open item for this one.
> --
> Michael


Okay, I checked it on the wiki.

   https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items
   - index_rebuild_count in CLUSTER reporting never increments

To be clear, 03f9e5cb broke CLUSTER progress reporting, but
I investigated little more and share my ideas to fix the problem.

* Call stack
========================================
cluster_rel
   pgstat_progress_start_command(CLUSTER) *A1
   rebuild_relation
     finish_heap_swap
       reindex_relation
         reindex_index
           pgstat_progress_start_command(CREATE_INDEX) *B1
           pgstat_progress_end_command() *B2
         pgstat_progress_update_param(INDEX_REBUILD_COUNT, i) <- failed :(
   pgstat_progress_end_command() *A2

   Note
     These are sets:
       A1 and A2,
       B1 and B2
========================================


* Ideas to fix
   There are Three options, I guess.
========================================
   1. Call pgstat_progress_start_command(CLUSTER) again
      before pgstat_progress_update_param(INDEX_REBUILD_COUNT, i).

   2. Add "save and restore" functions for the following two
      variables of MyBeentry in pgstat.c.
         - st_progress_command
         - st_progress_command_target

   3. Use Hash or List to store multiple values for the two
      variables in pgstat.c.
========================================


I tried 1. and it shown index_rebuild_count, but it also shown
"initializing" phase again and other columns were empty. So, it is
not suitable to fix the problem. :(
I'm going to try 2. and 3., but, unfortunately, I can't get enough
time to do that after PGConf.Asia 2019.
If we selected 3., it affects following these progress reporting
features: VACUUM, CLUSTER, CREATE_INDEX and ANALYZE. But it's okay,
I suppose. Any comments welcome! :)


Thanks,
Tatsuro Yamada