Pages

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:

  1. 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?

    ReplyDelete
  2. @PL, yes, fixed - thank you for notice

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

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

    Regards.
    G

    ReplyDelete
  4. 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.

    ReplyDelete