Обсуждение: progress reporting views and TimestampTz fields

Hi,

I'm thinking to change the progress reporting views like
pg_stat_progress_vacuum so that they also report the time when
the target command was started and the time when the phase was
last changed. IMO this is very helpful to estimate the remaining
time required to complete the current phase. For example,
if pg_stat_progress_vacuum reports that the current phase
"scanning heap" started 1 hour before and the progress percentage
is 50%, we can imagine the remaining time of this phase would be
approximately 1 hour. Of course, this is not the exact estimation,
but would be helpful as a hint for operations. Thought?

    ProgressCommandType st_progress_command;
    Oid            st_progress_command_target;
    int64        st_progress_param[PGSTAT_NUM_PROGRESS_PARAM];

We cannnot add those timestamp fields simply in the progress
reporting views because the type of the fields in PgBackendStatus
struct is only int64 for now, as the above. So I'm thinking to add
new TimestampTz fields (maybe four fields are enough even for
future usager?) into PgBackendStatus and make pg_stat_get_progress_info()
report those fields as timestamp. This change leads to increase
in the size of PgBackendStatus, as demerit. But I like this approach
because it's simple and intuitive.

Another idea is to store TimestampTz values in int64 fields
(for example, always store TimestampTz values in the last two int64
fields) and make pg_stat_get_progress_info() report not only int64
but also those TimestampTz fields. This approach doesn't increase
the struct size, but is a bit tricky. Also int64 fields that TimestampTz
values will be stored into might be already used to store int64 values
in some existing extensions. If we need to handle this case, further
tricky way might need to be implemented. That sounds not good.

Therefore, I'd like to implement the first idea that I described, to
add the timestamp fields in the progress reporting view. Thought?

Regards,

-- 
Fujii Masao
NTT DATA CORPORATION
Advanced Platform Technology Group
Research and Development Headquarters

On 25/02/2020 09:13, Fujii Masao wrote:
> Hi,
> 
> I'm thinking to change the progress reporting views like
> pg_stat_progress_vacuum so that they also report the time when
> the target command was started and the time when the phase was
> last changed. IMO this is very helpful to estimate the remaining
> time required to complete the current phase. For example,
> if pg_stat_progress_vacuum reports that the current phase
> "scanning heap" started 1 hour before and the progress percentage
> is 50%, we can imagine the remaining time of this phase would be
> approximately 1 hour. Of course, this is not the exact estimation,
> but would be helpful as a hint for operations. Thought?
> 
>     ProgressCommandType st_progress_command;
>     Oid            st_progress_command_target;
>     int64        st_progress_param[PGSTAT_NUM_PROGRESS_PARAM];
> 
> We cannnot add those timestamp fields simply in the progress
> reporting views because the type of the fields in PgBackendStatus
> struct is only int64 for now, as the above. So I'm thinking to add
> new TimestampTz fields (maybe four fields are enough even for
> future usager?) into PgBackendStatus and make pg_stat_get_progress_info()
> report those fields as timestamp. This change leads to increase
> in the size of PgBackendStatus, as demerit. But I like this approach
> because it's simple and intuitive.
> 
> Another idea is to store TimestampTz values in int64 fields
> (for example, always store TimestampTz values in the last two int64
> fields) and make pg_stat_get_progress_info() report not only int64
> but also those TimestampTz fields. This approach doesn't increase
> the struct size, but is a bit tricky. Also int64 fields that TimestampTz
> values will be stored into might be already used to store int64 values
> in some existing extensions. If we need to handle this case, further
> tricky way might need to be implemented. That sounds not good.
> 
> Therefore, I'd like to implement the first idea that I described, to
> add the timestamp fields in the progress reporting view. Thought?

+1 on the idea.  No opinion on the implementation.
-- 
Vik Fearing