Saturday, June 26, 2021

calculating derivation from pg_stat_database and watch statement

The one from important Postgres's stats view is pg_stat_database. We can see in this view lot of important cumullative metrics. But sometimes, we can prefer to see these metric related to last seconds. It is not hard to calculate it:
CREATE OR REPLACE FUNCTION public.tps(text)
RETURNS
   TABLE(xact_commit integer,
         xact_rollback integer,
         tup_returned integer,
         tup_fetched integer,
         tup_inserted integer,
         tup_updated integer,
         tup_deleted integer)
AS $$
DECLARE
  csdb pg_stat_database;
  psdb pg_stat_database;
  pts timestamp with time zone;
  sec numeric;
BEGIN
  SELECT * FROM pg_stat_database WHERE datname = $1 INTO csdb;

  psdb := current_setting('tps.vector', true)::pg_stat_database;
  pts := current_setting('tps.ts', true)::timestamp with time zone;

  sec := EXTRACT(epoch FROM (current_timestamp - pts));

  xact_commit := (csdb.xact_commit - coalesce(psdb.xact_commit, 0)) / sec;
  xact_rollback := (csdb.xact_rollback - coalesce(psdb.xact_rollback, 0)) / sec;
  tup_returned := (csdb.tup_returned - coalesce(psdb.tup_returned, 0)) / sec;
  tup_fetched := (csdb.tup_fetched - coalesce(psdb.tup_fetched, 0)) / sec;
  tup_inserted := (csdb.tup_inserted - coalesce(psdb.tup_inserted, 0)) / sec;
  tup_deleted := (csdb.tup_deleted - coalesce(psdb.tup_deleted, 0)) / sec;
  tup_updated := (csdb.tup_updated - coalesce(psdb.tup_updated, 0)) / sec;

  PERFORM set_config('tps.vector', csdb::text, false);
  PERFORM set_config('tps.ts', current_timestamp::text, false);

  RETURN NEXT;
END
$$ LANGUAGE plpgsql;
An usage is very simple:
postgres=# SELECT * FROM tps('postgres');
┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           0 │             0 │            0 │           0 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)

postgres=# \watch 2
                                Ne 27. června 2021, 05:57:41 (every 2s)

┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           1 │             0 │          406 │          19 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)

                                Ne 27. června 2021, 05:57:43 (every 2s)

┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           0 │             0 │           32 │          32 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)

5 Comments:

At June 27, 2021 at 12:04 AM , Blogger PL said...

Thanks!

I did a similar thing as a psql script ))

P.S. May be csdb instead of curr_stat_db in the set_config statement?

 
At June 27, 2021 at 12:17 AM , Blogger Pavel Stěhule said...

@PL, yes, fixed - thank you for notice

 
At June 27, 2021 at 8:50 AM , Anonymous Anonymous said...

Hi,
whats the min version this will work ? Tried on my 9.6 and got

$$ LANGUAGE plpgsql;
"
LINE 10: AS $function$
^

Regards.
G

 
At June 27, 2021 at 10:53 AM , Blogger Pavel Stěhule said...

It was buggy - I did some edition, but I did few errors there. Now, it is fixed and it should to work without any problems on Postgres 9.6. For Postgres 9.5 there should be done small change.

 
At June 28, 2021 at 6:54 AM , Anonymous Anonymous said...

Thanks

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home