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:

Wednesday, June 13, 2018

Article about PostgreSQL 11

My new article is in Czech language, but Google translator can help.

Tuesday, June 5, 2018

plpgsql_check can identify variables with wrong type used in predicates and breaks index usage

Simple example:
create table bigtable(id bigint, ...)

...
declare _id numeric;
begin
  _id := ...
  FOR r IN SELECT * FROM bigtable WHERE id = _id
  LOOP
     ...

In this case, PostgreSQL newer use index due different type of query parameter (type of parameter is defined by type of PLpgSQL variable) and table attribute. This time this error is more usual due migration from Oracle. Id in tables are declared as bigint, int, but variables in functions are often declared as numeric.

PLpgSQL can identify some symptom of this issue - implicit cast inside predicate - and can throw performance warning. See commit.

Example:
create table bigtable(id bigint, v varchar);
create or replace function test()
returns void as $$
declare
  r record;
  _id numeric;
begin
  select * into r from bigtable where id = _id;
  for r in select * from bigtable where _id = id
  loop
  end loop;
  if (exists(select * from bigtable where id = _id)) then
  end if;
end;
$$ language plpgsql;

select * from plpgsql_check_function('test()', performance_warnings => true);
                                                    plpgsql_check_function
-------------------------------------------------------------------------------------------------------------------------------
 performance:42804:6:SQL statement:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: select *        from bigtable where id = _id
 --                                             ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 performance:42804:7:FOR over SELECT rows:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: select * from bigtable where _id = id
 --                                 ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 performance:42804:10:IF:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: SELECT (exists(select * from bigtable where id = _id))
 --                                                     ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 warning:00000:3:DECLARE:never read variable "r"
(16 rows)

Tuesday, May 22, 2018

How to don't use PL/pgSQL and other fatal errors when PL/pgSQL is used

I wrote article Jak nepoužívat PL/pgSQL (případně PL/SQL) – fatální chyby při vývoji. The article is in Czech language, but google translator can be used.

Thursday, May 17, 2018

dude's test speed

I did one indicative benchmark of popular interpret languages, and I was surprised how modern PHP is fast now. This test is pretty simple and stupid, syntactical, unrealistic, and I know it. It say nothing about speed any interpret in practical usage. But can be interesting to see, how surprisingly some interprets are near to C, and what engines can be used for intensive numeric calculations.
void main()
{
   long int s = 0;
   int i;

   for (i 0; i < 10000000; i++)
     s := i;

   printf("%ld\n", s);
}
optimized C3ms
LuaJIT20ms
unoptimized C30ms
Lua100ms
PHP200ms
JavaScript V8 engine500ms
Perl600ms
JavaScrip Mozilla900ms
Python21200ms
Python31700ms
PostgreSQL SQL1700ms
PLpgSQL2200ms

I repeat, this test has very small value for life - Only C language from this list is designed for heavy numeric operations. All other languages has designed for different purposes and in their typical domain the typical bottleneck will be elsewhere than in simple numeric calculation. But can be interesting, how modern computers are fast - for example PLpgSQL is designed as SQL glue (I know, so it is absolutely without any optimization, and expr evaluation is really expensive there (due repeated security, database checks) - I hope so nobody use PLpgSQL for heavy numeric calculations, and still), and it is able do 10M operations in 2 sec.

Thursday, May 10, 2018

ncurses-st-menu library is available

I finished all work on CUA menu (menubar and pulldown menu) ncurses library. This library allows skins, shadows, supports accelerators, mouse. Look to demo.c for info about usage. Download from Github.