pspg 5.2.0 released

https://github.com/okbob/pspg/releases/tag/5.2.0

There are only two, but I hope important, features.

First feature is "progressive data load". Before this release, pspg loaded all rows before first print to screen. Now, with progressive data load, only 500 rows are loaded, these rows are printed to screen, and repeatedly next 2000 rows are loaded. Although the load should not be complete, almost all pspg commands can be used.  

pspg is designed for browsing tabular data. But it can be used for plain text too. This is important - psql can produce lot of data in plain text format - (\? \h). Now, pspg can highlight some parts of these documents.

pspg 5.1.2 was released, psql \watch command now supports pspg

Today I released pspg 5.1.2. Mostly this is bugfix and refactoring release, but there is one, I hope, interesting function. You can try to press Ctrl o for temporal switch to terminal's primary screen. In primary screen you can see psql session. After pressing any key, the terminal switch to alternative screen with pspg.

Thanks to Tomas Munro work, the psql \watch command will supports pagers (in PostgreSQL 15). In this time only pspg can do this work (in streaming mode). When you set environment variable PSQL_WATCH_PAGER, the \watch command redirects otputs to specified pager (for pspg export PSQL_WATCH_PAGER="pspg --stream". Next you can run command:

select * from pg_stat_database \watch 5

or

select * from pg_stat_activity where state='active' \watch 1

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)

pspg 5.0.1 is released

I released pspg 5.0.1. There are some new interesting features: 

1.  there is new possibility of execution some commands - via internal command line 
2.  there are new backslash commands - \copy, \save, \order, \search
3.  there is a possibility to limit searching to selected area
4.  there is new color theme PaperColor  

 

Article about prepared PostgreSQL 14

I wrote an article about PostgreSQL 14. It is in Czech language - but on this page is google translator button, so I hope so more peple can read it.

pspg 4.6.1 bugfix release

This release fix critical segfault crash introduced in pspg 4.6.0. https://github.com/okbob/pspg/releases/tag/4.6.1

pspg 4.6.0 released

I released pspg 4.6.0 There are few interesting features against lastly mentioned here 4.3.0 release: 

• fix stream mode on apple 
• new option --menu-always - top bar menu will be visible always
• fix some visual artefacts for some special cases when user uses style with border = 1
• new query stream mode.

Query stream mode is designed for using from text editors, that can start pspg in separate terminal in permanent mode. In query stream mode the pspg works like Postgres client, reads queries from pipe, executes it and draws an result. The queries are separated by Group Separated char (GS - 0x1D).