Thursday, April 2, 2020

simple monitoring of PostgreSQL incremental statistics

Everybody should to know PostgreSQL's stats views: pg_stat_user_tables, pg_stat_database. There are lot of interesting values. For better interpretation we need a difference of these values in some time. It is impossible to get previous value with clean SQL, but in Postgres we can use PLpgSQL, and we can use custom configure variables as session variables. I wrote function buffer, that returns difference between current value and buffered, and save current value to buffer:

CREATE OR REPLACE FUNCTION public.buffer_dif(text, bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE r bigint;
BEGIN
  r := $2 - current_setting($1, true)::bigint;
  PERFORM set_config($1, $2::text, false);
  RETURN r;
END;
$function$

Now I can prepare query:
SELECT datname,
       buffer_dif('x.xact_commit_' || datname, xact_commit) AS commits, 
       buffer_dif('x.tup_inserted_' || datname, tup_inserted) AS inserted
  FROM pg_stat_database
 WHERE datname IS NOT NULL;

I can repeat this query every 1 second by psql command \watch:

^Cpostgres=# \watch 1
Čt 2. dubna 2020, 21:33:59 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:00 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:01 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Now I can see 1 sec differences of incremental metrics without any other tools.

Tuesday, March 31, 2020

Postgres 13, new family of polymorphic types - anycompatible

One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.

Example: I can write own isnull function:
CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
┌────────────┐
│   isnull   │
╞════════════╡
│ 2020-03-31 │
└────────────┘
(1 row)

-- but
postgres=# SELECT public.isnull(1, 1.1);
ERROR:  function public.isnull(integer, numeric) does not exist
LINE 1: SELECT public.isnull(1, 1.1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It is working for all supported types. One and big limit of type family any* is rigidity. It is working for all types, but for previous example, first and second parameter has to have exactly same types. Similar buildin functions are more tolerant - usually requires same type category only. This limit should be removed with new family of polymorphic types anycompatible. When there are more arguments with this type, then most common type is calculated and all arguments of anycompatible are casted to selected most common type:

CREATE OR REPLACE FUNCTION isnull(anycompatible, anycompatible)
RETURNS anycompatible AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(1, 1.1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, 1.1);
┌────────┐
│ isnull │
╞════════╡
│    1.1 │
└────────┘
(1 row)

Now, it is working as expected.

This feature is interesting mainly for authors of extensions that has to emulate some different databases (like Orafce or similar).

Friday, March 27, 2020

pspg 2.7.0 with inotify support

Long time I am playing with psql and I try to enhance and fix some limits of this client. I like it. The work with this client is pretty fast, and with pspg the result's browsing is almost comfortable. But still there is disadvantage - is not possible to see result and query in one time, and isn't possible to work with more results in one time. But with new inotify support these limits are removed. You can create file that will be used as input/output buffer. Now start pspg with this file. When this file will be changed (by psql), then pspg reread this file immediately.

[pavel@nemesis ~]$ touch ~/r1
[pavel@nemesis ~]$ touch ~/r2
[pavel@nemesis ~]$ touch ~/r3

Then start in three terminals pspg (I use old terminator or new tilix)
[pavel@nemesis ~]$ pspg -f ~/r1
[pavel@nemesis ~]$ pspg -f ~/r2
[pavel@nemesis ~]$ pspg -f ~/r3

and now, you can play
psql
postgres=# select * from pg_class \g ~/r1
postgres=# select * from pg_proc \g ~/r2
postgres=# select * from pg_database \g ~/r3

And I see queries and results


Monday, March 23, 2020

small update of ncurses-st-menu

ncurses-st-menu is small library over ncurses for pulldown and menubar menu. Stripped size of library has 43kB, so it is not extra light, but it is in category smaller libraries. One use case, that I would to support, is using in smaller application on smaller devices. For this purpose this library supports scrollable menu now. When the content of menu is longer then display, then the content will be scrollable - so cursor will be visible every time.

Sunday, March 1, 2020

private, public plpgsql functions

Lot of people does migration from Oracle's PL/SQL to PostgreSQL's PL/pgSQL. One question is how to migrate private functions? There is not a reply. Oracle's PL/SQL is little bit modified language ADA. This language is procedural and modular. The concept of private functions or public functions is very natural there. Nothing similar is in Postgres. Not in this moment. PLpgSQL functions are called by SQL API every time. So visibility for SQL engine should be same for all functions. Theoretically anybody case set SEARCH_PATH for any function manually and then functions from other schema will be invisible. But this design is pretty frail. Somebody can call functions from other schema by qualified name, and the often switch of SEARCH_PATH in significantly decrease readability of your code.

I think so some convention can help. For example, we can mark of any private functions by prefix '_'. With plpgsql_check we can check if these functions are used only inside functions with same schema. The plpgsql_check's function plpgsql_show_dependency_tb does this work.

CREATE SCHEMA packagex;

/*
 * private function, should be used only by functions from this schema
 */
CREATE OR REPLACE FUNCTION packagex._privatefx(a integer)
RETURNS integer AS $$
BEGIN
  RETURN a;
END;
$$ LANGUAGE plpgsql;

/*
 * public function - use private function correctly
 */
CREATE OR REPLACE FUNCTION packagex.publicf1(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

/*
 * public function - doesn't use private function correctly
 */
CREATE OR REPLACE FUNCTION public.publicf2(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

The function public.publicf2 is breaking our convention and I have to identify this function.

SELECT *
  FROM (SELECT pronamespace::regnamespace,
               proname,
               x.*
          FROM pg_proc,
               plpgsql_show_dependency_tb(oid) x
         WHERE prolang = (SELECT oid 
                            FROM pg_language 
                           WHERE lanname = 'plpgsql')
           AND pronamespace <> 'pg_catalog'::regnamespace) s
 WHERE starts_with(name, '_')
   AND schema::regnamespace <> pronamespace;

┌──────────────┬──────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ pronamespace │ proname  │   type   │  oid  │  schema  │    name    │  params   │
╞══════════════╪══════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ public       │ publicf2 │ FUNCTION │ 16411 │ packagex │ _privatefx │ (integer) │
└──────────────┴──────────┴──────────┴───────┴──────────┴────────────┴───────────┘
(1 row)

There is not nice possibility how to implement private functions in Postgres now, but we can introduce some conventions and we can check if these conventions are broken or not.

The function plpgsql_show_dependency_tb display all objects used by function:

SELECT pg_proc.oid::regprocedure AS function, 
       x.oid::regprocedure AS "called function"
  FROM pg_proc,
       plpgsql_show_dependency_tb(oid) x
 WHERE prolang = (SELECT oid 
                    FROM pg_language 
                   WHERE lanname = 'plpgsql')
   AND pronamespace <> 'pg_catalog'::regnamespace 
   AND x.type = 'FUNCTION';
┌────────────────────────────┬──────────────────────────────┐
│          function          │       called function        │
╞════════════════════════════╪══════════════════════════════╡
│ packagex.publicf1(integer) │ packagex._privatefx(integer) │
│ publicf2(integer)          │ packagex._privatefx(integer) │
└────────────────────────────┴──────────────────────────────┘
(2 rows)

Friday, February 14, 2020

plpgsql_check 1.9 calculates coverage metrics

Small note - I finished support of statement and branch coverage metrics calculations for plpgsql_check

https://github.com/okbob/plpgsql_check/commit/c6f9896ead0a0969db23c3062f1fe7ce5f38029b

Saturday, February 8, 2020

psql and gnuplot

psql from PostgreSQL 12 can produces CSV format. This format is well readable by gnuplot. So anybody can use it together:

\pset format csv
select i, sin(i) from generate_series(0, 6.3, 0.5) g(i) \g |gnuplot -p -e "set datafile separator ','; set key autotitle columnhead; plot '-'with boxes"

    1 +-+---------+-*******--+-----------+----------+-----------+--------+-+   
      +           + *     *******        +          +           +          +   
  0.8 +-+      ******     *     *                              sin *******-+   
      |        *    *     *     *                                          |   
  0.6 +-+      *    *     *     *******                                  +-+   
      |  *******    *     *     *     *                                    |   
  0.4 +-+*     *    *     *     *     *                                  +-+   
      |  *     *    *     *     *     *                                    |   
  0.2 +-+*     *    *     *     *     ******                             +-+   
    0 **********************************************************************   
      |                                    *     *     *     *    *     *  *   
 -0.2 +-+                                  *     *     *     *    *     *+-*   
      |                                    *     *     *     *    *     ****   
 -0.4 +-+                                  *******     *     *    *     *+-+   
      |                                          *     *     *    *     *  |   
 -0.6 +-+                                        *     *     *    *     *+-+   
      |                                          *     *     *    *******  |   
 -0.8 +-+                                        *******     *    *      +-+   
      +           +          +           +          +  *     *  + *        +   
   -1 +-+---------+----------+-----------+----------+--************------+-+   
      0           1          2           3          4           5          6  

postgres=# select i, sin(i) from generate_series(0, 6.3, 0.05) g(i) \g |gnuplot -p -e "set datafile separator ','; set key autotitle columnhead; set terminal dumb enhanced; plot '-'with boxes" 

                                                                               
                                                                               
    1 +-+-------+-*******-+---------+--------+---------+---------+-------+-+   
      +         ************        +        +         +         +         +   
  0.8 +-+     ****************                                 sin *******-+   
      |      ******************                                            |   
  0.6 +-+   ********************                                         +-+   
      |   ************************                                         |   
  0.4 +-+**************************                                      +-+   
      | ****************************                                       |   
  0.2 +******************************                                    +-+   
    0 ***************************************************************    +-+   
      |                              ********************************      |   
 -0.2 +-+                             ******************************     +-+   
      |                                ****************************        |   
 -0.4 +-+                               **************************       +-+   
      |                                  ***********************           |   
 -0.6 +-+                                 *********************          +-+   
      |                                     ******************             |   
 -0.8 +-+                                    ****************            +-+   
      +         +         +         +        + ************      +         +   
   -1 +-+-------+---------+---------+--------+---********--------+-------+-+   
      0         1         2         3        4         5         6         7   
                                                                               

postgres=# select i, sin(i) from generate_series(0, 6.3, 0.05) g(i) \g |gnuplot -p -e "set datafile separator ','; set key autotitle columnhead; set terminal dumb enhanced; plot '-'with lines ls 1" 

                                                                               
                                                                               
    1 +-+-------+-*******-+---------+--------+---------+---------+-------+-+   
      +         ***      ***        +        +         +         +         +   
  0.8 +-+     ***          ***                                 sin *******-+   
      |      **              **                                            |   
  0.6 +-+   *                  *                                         +-+   
      |   **                    **                                         |   
  0.4 +-+**                      **                                      +-+   
      | **                        **                                       |   
  0.2 +**                          **                                    +-+   
    0 **+                           **                              *    +-+   
      |                              **                            **      |   
 -0.2 +-+                             **                          **     +-+   
      |                                 *                        **        |   
 -0.4 +-+                                *                      **       +-+   
      |                                  **                    *           |   
 -0.6 +-+                                 **                 **          +-+   
      |                                     **              **             |   
 -0.8 +-+                                    **           ***            +-+   
      +         +         +         +        + ***     +***      +         +   
   -1 +-+-------+---------+---------+--------+---*******---------+-------+-+   
      0         1         2         3        4         5         6         7 



This can be little bit simplified with psql macros

postgres=# \set gnuplot '\\g | gnuplot -p -e "set datafile separator \',\'; set key autotitle columnhead; set terminal dumb enhanced; plot \'-\'with boxes"' 

postgres=# \pset format csv

postgres=# select i, sin(i) from generate_series(0, 6.3, 0.05) g(i) :gnuplot

                                                                               
                                                                               
    1 +-+-------+-*******-+---------+--------+---------+---------+-------+-+   
      +         ************        +        +         +         +         +   
  0.8 +-+     ****************                                 sin *******-+   
      |      ******************                                            |   
  0.6 +-+   ********************                                         +-+   
      |   ************************                                         |   
  0.4 +-+**************************                                      +-+   
      | ****************************                                       |   
  0.2 +******************************                                    +-+   
    0 ***************************************************************    +-+   
      |                              ********************************      |   
 -0.2 +-+                             ******************************     +-+   
      |                                ****************************        |   
 -0.4 +-+                               **************************       +-+   
      |                                  ***********************           |   
 -0.6 +-+                                 *********************          +-+   
      |                                     ******************             |   
 -0.8 +-+                                    ****************            +-+   
      +         +         +         +        + ************      +         +   
   -1 +-+-------+---------+---------+--------+---********--------+-------+-+   
      0         1         2         3        4         5         6         7