Thursday, October 11, 2018

New functionality of plpgsql_extension

I am working on new release of plpgsql_check extension -

Interesting new function is possibility to return list of used relation and functions. With these information is easy to generate dependency graph:

postgres=# \sf fx
 LANGUAGE plpgsql
AS $function$
  perform upper(((plus(a) + 200) * 100)::text) from xx;
postgres=# select * from plpgsql_show_dependency_tb('fx()');
│   type   │  oid  │ schema │ name │  params   │
│ FUNCTION │ 18310 │ public │ plus │ (integer) │
│ RELATION │ 24576 │ public │ xx   │           │
(2 rows)

Monday, October 8, 2018

Pager for data

Any Unix like systems has great feature - a pager. The history of pagers is pretty old. Originally was used manually. The result of some programs can be redirected to any program with pipe operator.

ls -la | more

One, most simple pager is more. It can scroll only in one direction. Usually this is default pager in Unix systems.

Much more powerful pager is less. Has lot of advantages and functionality - mainly possibility to scroll in two direction, strong navigation inside result. It can display long lines, and allow horizontal scrolling. This pager is necessary for comfortable usage of psql console (note: it is works with mysql, mariadb or vertica console).

export PAGER="less -S"
psql postgres
postgres=>select * from pg_class;

It is good to know some less commands:
  • g - move to document begin,
  • G - move to document end,
  • / - search string,
  • n - next occurrence of string,
  • N - previous occurrence of string,
  • q - quit.
less is very rich pager has special support for displaying man pages. But this feature we cannot to use for database data. For tabular data, there are special pager pspg (mysql, pgcli are supported too). This pager can freeze n first columns, and m first rows. It knows almost all keyboard commands of less pager, and append some new (based on mcedit keywords).
  • Alt k - new bookmark
  • Alt i - previous bookmark
  • Alt j - next bookmark
  • Alt n - show row numbers
  • Alt l - go to line
  • Ctrl Home - move to document begin
  • Ctrl End - move to document end
  • F9 - menu
  • F10 - quit
The usage of pspg is same:
export PAGER=pspg
psql postgres
This pager is available from PostgreSQL community repository or from git

Sunday, September 2, 2018

New features for pspg

I wrote some (I useful) features for pspg

  • possibility to show/hide row numbers
  • possibility to hide/show row cursor

Load from GitHub 1.6.0 release.

Monday, August 27, 2018

New release of pspg pager

I redesigned some work with mouse - ncurses native implementation is simple, but slow by design.

A default layout of pspg is based on old Norton Commander layout. It is good for beginners, because almost all controls are visible. Probably, when you work with pspg longer time, then you prefer more visible content against auxiliary lines. The lines (bars) can be disabled now - you can run pspg with option --no-bars. The pspg is available from github

Tuesday, July 24, 2018

Safe programming in PL/pgSQL

PL/pgSQL is verbose practical language based on Oracle's PL/SQL, that is based on years ago very popular programming language ADA. ADA language is pretty strong nice language used now mostly in critical software. Still ADA is widely used, but only few people who uses PL/SQL knows, so they use ADA (with only few modifications).

Unfortunately not all on PL/pgSQL is great. Some language features can hides some begginer's issues. These features are active by default, and cannot be disabled due possible compatibility break. There was a ideas about new PL/pgSQL2 language design. I agree with some these ideas, but I was strongly against forking main language for stored procedures in Postgres. It was based on not good common experience with Perl6 and Python3. Although I believe so the switch can be more gently in PostgreSQL, due different technology, fork of language raise some fears about support custom code in future, and there should be really strong arguments for this change.

More - almost all issues in PL/pgSQL code can detect plpgsql_check - . It is really strong tool, and a usage is very simple. Now, some checks has PL/pgSQL self. Tomáš Vondra committed my patch with extra checks for common issues in PL/pgSQL. These checks can raise warning or error (it is configurable).

New checks are related to assign query result to variables. Sometimes, the query can returns more rows (only first rows is used,but it can be unexpected row). Sometimes query returns too less columns and some variables are setted to NULL, or in different case, when query returns more columns, then some values can be ignored. Usually this is bug, but in default settings - no warning is raised. PL/pgSQL is too tolerant.

With new extra checks these issues can be simply detected:
set plpgsql.extra_warnings to 'too_many_rows';
do $$
declare x int;
  select v from generate_series(1,2) g(v) into x;
WARNING:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1
set plpgsql.extra_warnings to 'strict_multi_assignment';
do $$
  x int;
  y int;
  select 1 into x, y;
  select 1,2 into x, y;
  select 1,2,3 into x, y;
WARNING:  number of source and target fields in assignment do not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
WARNING:  number of source and target fields in assignment do not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.

Attention: too_many_rows detect when result has more than one row. It doesn't detect situation, when result is empty. Don't forget check auto variable NOT_FOUND after any SELECT INTO.

Use these extra check or use plpgsql_check.

Wednesday, July 18, 2018

New release of pspg - 1.2.0

This release can be fully configured from menu (themes can be selected from menu too). The setting can be persistent to file ~/.pspgconf:

Monday, July 9, 2018

pspg has menu

The pager pspg has more than 30 keyboard short cuts, what can be hard to use for beginners without Linux command line knowleadge.

For this reason I enhanced pspg about menus. Unfortunately I didn't find any library that implements menu on ncurses platform. Native ncurses menus doesn't support mouse well, and has little bit different philosophy than CUA. So I wrote own st-menu library. This library is available for generic and free usage. pspg can be use as referential usage of this library.

Few screenshots: