Wednesday, March 20, 2024

compiled win x64 dll of orafce-4.9.3 and plpgsql_check-2.7.4 for PostgreSQL 15 and 16

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 15 and PostgreSQL 16.


  1. download or and extract files
  2. copy related dll file to PostgreSQL lib directory (NN is number of pg release)
    orafce-NN.dll -> "c:\Program Files\PostgreSQL\NN\lib"
  3. remove suffix "-15" or "-16" from dll file
    orafce-NN.dll -> orafce.dll
  4. copy *.sql and *.control files to extension directory
    *.sql, *.control -> "c:\Program Files\PostgreSQL\NN\share\extension"
  5. execute with super user rights SQL command CREATE EXTENSION

Tuesday, March 5, 2024

How fast is plpgsql?

Ten years ago I did some speed tests of plpgsql. I did same tests on same computer, and now, the plpgsql is about 3-4 times faster. There is still significant overhead against native (without plpgsql function) query, but it is reduced (little bit). I would to compare again with Python

create table foo(a int, b int, c int, d int, e int);
insert into foo select random()*100, random()*100, random()*100, random()*100, random()*100 from generate_series(1,100000);

CREATE OR REPLACE FUNCTION public.myleast1(VARIADIC integer[])
AS $function$
declare result int;
a int;
  foreach a in array $1
    if result is null then 
      result := a; 
    elseif a < result then
      result := a;
    end if;
  end loop;
  return result;

CREATE OR REPLACE FUNCTION public.myleast6(VARIADIC a integer[])
AS $function$
r = None
for x in a:
   if r is None or x < r:
      r = x
return r

(2024-03-05 12:25:10) postgres=# select count(*) filter (where a = least(a,b,c,d,e)) from foo;
│ count │
│ 20544 │
(1 row)

Time: 15,265 ms
(2024-03-05 12:25:53) postgres=# select count(*) filter (where a = myleast1(a,b,c,d,e)) from foo;
│ count │
│ 20544 │
(1 row)

Time: 221,726 ms
(2024-03-05 12:25:59) postgres=# select count(*) filter (where a = myleast6(a,b,c,d,e)) from foo;
│ count │
│ 20544 │
(1 row)

Time: 282,732 ms

Ten years ago plpgsql was about 30% slower, now it is about 20% faster.

Attention - this benchmark is pretty unrealistic - the best benefit of stored procedures is when they are used like glue of SQL statements. On second hand, can be interesting to know the overhead of stored procedures against native C implementation - this is like worst case.

Sunday, March 3, 2024

using jq for processing PostgreSQL logs in json format

PostgreSQL supports logging in json format. From my perspective json logs are badly readable, but allows machine processing, and with good tools, it is beautifully simple.

There are more tools for json processing - I use jq.

For simple analyze of errors in log, I can use sequence of commands:

For transformation to csv and viewing it in pspg:

cat postgresql-Sun.json | \
jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] | @csv' \
| pspg --csv

With these tools the work with log is "almost" effective and friendly (pspg supports sorting, searching, clipboard).