On Thu, 17 Feb 2022 at 07:56, Nitin Jadhav <nitinjadhavpostg...@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);