pgcli integrated support for pspg

pgcli is nice TUI client for Postgres. Against psql it has significantly modern look with more aggressive autocomplete support.

When I tested this client, I found one significant issue (against psql). It was pretty slow when larger than small result had to be displayed. The most slow operation is result formatting to tabular format. This is hard task for interpret language and pgcli is written in Python. I proposed a move this operation to pspg. Yesterday a necessary patch was committed.

It requires some pgcli configuration - see ~/.config/pgcli/config file:

pager = pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header=on --double-header
table_format = csv
row_limit = 0

Now, the result is processed with similar speed like in psql and work with pgcli is much more comfortable. Thanks to all authors of pgcli.

split string to table

Sometimes life can be funny. This week there was a question about XMLTABLE function in Postgres's maling list. It was related to migration from Oracle to Postgres.

CREATE TABLE user_pool_clean (
    fk_user_pool_pk bytea NOT NULL,
    user_id character varying(255) NOT NULL,
    email_address character varying(250),
    is_mil numeric,
    is_civ numeric,
    is_ctr numeric,
    is_gov numeric,
    is_edu numeric,
    role_id character varying(50),
    user_profile_id character varying(50),
    service_branch_id character varying(50),
    mil_pay_grade_id character varying(50),
    my_auds character varying(4000),
    my_orgs character varying(4000),
    processed character(1) DEFAULT 'N'::bpchar NOT NULL
);

insert into user_pool_clean
values('995CECDC1881375DE05312A270C7CF56','10015706','noemail@fter.dbrefresh.mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');

insert into user_pool_clean
values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@fter.dbrefresh.mil',0,0,0,0,0,'1','3','700015','11','
1705562,1708486','1710621','Y');


SQL> SELECT upc.is_mil,TRIM(column_value) src
         FROM   user_pool_clean upc
               ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));

    IS_MIL SRC
---------- ---------------
     0       1705562              --------O/P from the oracle database
     0        1708486
    0        1706882

It is hard to understand, why somebody used a xmltable function. After some googling, I found, so this is a workaround for generating rows from some string. The original input string is modified to XQuery sequence format. When xmltable has not any other clauses with only one input parameter, then this parameter is used as XQuery expression. Unfortunately nothing similar is possible in Postgres. Postgres uses library libxml2 for xml support, and this library supports only older XPath language and doesn't support more powerful but more complex XQuery language.

Fortunately, Postgres has more tools for split string to rows (it can be used cleanly without workarounds)

First possibility is using string_to_array and unnest functions:

postgres=# select is_mil, unnest(string_to_array(my_auds, ',')) from user_pool_clean ;
┌────────┬─────────┐
│ is_mil │ unnest  │
╞════════╪═════════╡
│      0 │ 1706882 │
│      0 │        ↵│
│        │ 1705562 │
│      0 │ 1708486 │
└────────┴─────────┘
(3 rows)

The result is broken by new line char (probably it is cleaned by XQuery evaluation, because new line is ignored in XML), so we should to clean this result:

postgres=# select is_mil, trim(e' \n' from unnest(string_to_array(my_auds, ','))) from user_pool_clean ;
┌────────┬─────────┐
│ is_mil │  btrim  │
╞════════╪═════════╡
│      0 │ 1706882 │
│      0 │ 1705562 │
│      0 │ 1708486 │
└────────┴─────────┘
(3 rows)

Now, the result is perfect. Is nice to see the power of postgres table functions (and possibilities). The form with lateral join like original query is possible too:

postgres=# select is_mil, trim(e' \n' from  u) 
             from user_pool_clean,
                  unnest(string_to_array(my_auds, ',')) u ;
┌────────┬─────────┐
│ is_mil │  btrim  │
╞════════╪═════════╡
│      0 │ 1706882 │
│      0 │ 1705562 │
│      0 │ 1708486 │
└────────┴─────────┘
(3 rows)

Another way, but maybe little bit slower (but with more possibilities) is using regexp_split_to_table function:

postgres=# select is_mil, trim(e' \n' from  u) 
            from user_pool_clean, 
                 regexp_split_to_table(my_auds, ',') u ;
┌────────┬─────────┐
│ is_mil │  btrim  │
╞════════╪═════════╡
│      0 │ 1706882 │
│      0 │ 1705562 │
│      0 │ 1708486 │
└────────┴─────────┘
(3 rows)

new pspg - version 3.0.2 with named pipe support

Today I released version 3.0.2. The ui is same like older versions. New is a possibility to communicate with client via named pipe. Now pspg can work well with redirected content (\o command in psql and psql watch mode is supported too.

Example:

#create named pipe
mkfifo ~/pipe

#use psql
[pavel@nemesis ~]$ psql
psql (13devel)
Type "help" for help.

postgres=# \o ~/pokus
postgres=# select * from obce limit 10;
postgres=# --redirect content to pipe
postgres=# \o ~/pipe
postgres=# select current_timestamp;
postgres=# -- repeat it every 1sec
postgres=# \watch 1

In other terminal - run pspg and read from pipe

pspg -f ~/pipe

By default pspg is ending when writer program is closed. This behave can be changed with option --hold-stream to 1 (reopen) or 2 (nonstop opened).

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.