bugfix release of pspg - 5.3.5

you can download source code from https://github.com/okbob/pspg/releases/tag/5.3.5

orafce_mail

I wrote new Postgres's extension - orafce_mail. This extension enhancing an orafce about possibility to send mail from Postgres. I tested this extension against my gmail account, and it is work well. Just you need to generate special application's password, and that is all. This extension use library libcurl, and the features and possibilities of this extension is limitted by this library. It uses relativly new features of this library, so it cannot be linked with older versions of libcurl. The usage is simple:

You have to set configure variables:

set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to 'pavel.stehule@gmail.com:yourgoogleapppassword';

After that you can send an mail without an attachment:

call utl_mail.send(sender => 'pavel.stehule@gmail.com',
                   recipients => 'pavel.stehule@gmail.com',
                   subject => 'ahoj, nazdar, žlutý kůň',
                   message => e'test, \nžlutý kůň');

or mail with an attachment:

do $$
declare
  myimage bytea = (select img from foo limit 1);
begin
  call utl_mail.send_attach_raw(sender => 'pavel.stehule@gmail.com',
                                recipients => 'pavel.stehule@gmail.com',
                                subject => 'mail with picture',
                                message => 'I am sending some picture',
                                attachment => myimage,
                                att_mime_type => 'image/png',
                                att_filename => 'screenshot.png');
end
$$;

The implemented API is almost compatible with Oracle's package utl_mail and dbms_mail. This extension requires PostgreSQL 10 and higher (with procedures support).

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.