On Thu, 17 Feb 2022 at 07:56, Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:
>
> > Progress parameters are int64, so all of the new 'checkpoint start
> > location' (lsn = uint64), 'triggering backend PID' (int), 'elapsed
> > time' (store as start time in stat_progress, timestamp fits in 64
> > bits) and 'checkpoint or restartpoint?' (boolean) would each fit in a
> > current stat_progress parameter. Some processing would be required at
> > the view, but that's not impossible to overcome.
>
> Thank you for sharing the information. 'triggering backend PID' (int)
> - can be stored without any problem. 'checkpoint or restartpoint?'
> (boolean) - can be stored as a integer value like
> PROGRESS_CHECKPOINT_TYPE_CHECKPOINT(0) and
> PROGRESS_CHECKPOINT_TYPE_RESTARTPOINT(1). 'elapsed time' (store as
> start time in stat_progress, timestamp fits in 64 bits) - As
> Timestamptz is of type int64 internally, so we can store the timestamp
> value in the progres parameter and then expose a function like
> 'pg_stat_get_progress_checkpoint_elapsed' which takes int64 (not
> Timestamptz) as argument and then returns string representing the
> elapsed time.
No need to use a string there; I think exposing the checkpoint start
time is good enough. The conversion of int64 to timestamp[tz] can be
done in SQL (although I'm not sure that exposing the internal bitwise
representation of Interval should be exposed to that extent) [0].
Users can then extract the duration interval using now() - start_time,
which also allows the user to use their own preferred formatting.
> This function can be called in the view. Is it
> safe/advisable to use int64 type here rather than Timestamptz for this
> purpose?
Yes, this must be exposed through int64, as the sql-callable
pg_stat_get_progress_info only exposes bigint columns. Any
transformation function may return other types (see
pg_indexam_progress_phasename for an example of that).
> 'checkpoint start location' (lsn = uint64) - I feel we
> cannot use progress parameters for this case. As assigning uint64 to
> int64 type would be an issue for larger values and can lead to hidden
> bugs.
Not necessarily - we can (without much trouble) do a bitwise cast from
uint64 to int64, and then (in SQL) cast it back to a pg_lsn [1]. Not
very elegant, but it works quite well.
Kind regards,
Matthias van de Meent
[0] Assuming we don't care about the years past 294246 CE (2942467 is
when int64 overflows into negatives), the following works without any
precision losses: SELECT
to_timestamp((stat.my_int64::bigint/1000000)::float8) +
make_interval(0, 0, 0, 0, 0, 0, MOD(stat.my_int64, 1000000)::float8 /
1000000::float8) FROM (SELECT 1::bigint) AS stat(my_int64);
[1] SELECT '0/0'::pg_lsn + ((CASE WHEN stat.my_int64 < 0 THEN
pow(2::numeric, 64::numeric)::numeric ELSE 0::numeric END) +
stat.my_int64::numeric) FROM (SELECT -2::bigint /* 0xFFFFFFFF/FFFFFFFE
*/ AS my_bigint_lsn) AS stat(my_int64);
