Using non ACID storage as workaround instead missing autonomous transactions

When I was younger, the culture war (in my bubble) was about transactional versus non-transactional engines, Postgres versus MySQL (MyISAM). Surely, I preferred the transactional concept. Data integrity and crash safety is super important.  But it is not without costs. It was visible 30 years ago, when MySQL was a super fast and PostgreSQL super slow database. Today on more powerful computers it is visible too, not too strong, but still it is visible. And we still use non-transactional storages a lot of - applications logs. 

 There are some cases when performance wins over consistency, and it can be acceptable. When I thought about non-transactional storages, I got one idea. It can be great replacement for missing autonomous transactions. But how to test it. Fortunately I found a csv_tam storage implemented by Alexey Gordeev.  This storage is mostly a concept with a lot of limits. But the idea is great - csv is a strong protocol - it is not block based, it has no row headers - so it can be very hard to support transactions. On second hand, it is primitive, and without any buffering and with forcing syncing after any row, it is mostly crash safe (against Postgres crash). Sure - it is not as safe as block storage ensured by WAL, but can be safe enough - billions applications use this safety for logging today. 

I did fork and fixed build on pg 17+. Now all types are supported and writing from parallel writes should be safe. It doesn't write to WAL, so these tables cannot be backuped and cannot be replicated - what can be a nice game to support it. It is not easy to do that in a non-block format. But for testing it is enough, and I believe so this extension is very simple, so it is enough for non critical environments. It is really very very simple. 

Postgres has not autonomous transactions. There are some workarounds like using dblink or pg_background.  As usual any workaround has some disadvantages and limits. pg_background looks good, but at the end, it doesn't ensure 100% success in write (under high load) - although there will be space on IO. So I wrote another workaround - using a transactional engine. Not all transactional engines are not same. If I remember well, MyISAM is non-transactional and non crash safe. Aria engine is non-transactional, but crash safe. csv_tam storage is non transactional and mostly crash safe. For fully crash safety it needs fault tolerant reading (which is now possible, and should not be too hard to implement). csv_tam supports only inserts, and truncating. Nothing more. Thanks to this it is mostly crash safe.

(2026-04-03 07:44:37) postgres=# create extension csv_tam ;
CREATE EXTENSION

(2026-04-03 07:47:10) postgres=# create table log(ts timestamp with time zone, message varchar);
CREATE TABLE

(2026-04-03 07:52:22) postgres=# \sf foo
CREATE OR REPLACE FUNCTION public.foo(integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
begin
  return 0/$1;
exception when others then
  insert into log values(current_timestamp, sqlerrm);
  raise; -- reraise error
end;
$function$

(2026-04-03 07:49:15) postgres=# select foo(0);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function foo(integer) line 3 at RETURN
(2026-04-03 07:49:20) postgres=# select * from log;
┌────┬─────────┐
│ ts │ message │
╞════╪═════════╡
└────┴─────────┘
(0 rows)

-- it doesn't work because we used classic heap (transactional) storage

(2026-04-03 07:49:51) postgres=# create table log(ts timestamp with time zone, message varchar) using csv_tam;
CREATE TABLE

(2026-04-03 07:49:58) postgres=# select foo(0);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function foo(integer) line 3 at RETURN
(2026-04-03 07:50:01) postgres=# select * from log;
┌───────────────────────────────┬──────────────────┐
│              ts               │     message      │
╞═══════════════════════════════╪══════════════════╡
│ 2026-04-03 07:50:01.437296+02 │ division by zero │
└───────────────────────────────┴──────────────────┘
(1 row)

fresh dll of orafce and plpgsql_check for PostgreSQL 17 and PostgreSQL 18

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 17 and PostgreSQL 18 - I used Microsoft Visual C 2022.

Setup:

1. download orafce-4.16.3-x86_64-windows.zip or plpgsql_check-2.8.5-x86_64-windows.zip 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 "x64-NN" 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
   CREATE EXTENSION orafce;
    

Note: plpgsql_check and Orafce are noncommercial extensions for PostgreSQL. These extensions are available on AVS, Azure, Google Cloud. It can be easy installed on Linux from community repositories, but these repositories has not build for Windows.

Article about PostgreSQL 18

I wrote an article about PostgreSQL 18.  It is in Czech language, but translators from Czech to English, German, ... works relatively well today.

 https://www.root.cz/clanky/postgresql-18-tricet-let-otevreneho-vyvoje-databaze/

How to fix Hibernate bug by conditional index

Yesterday I found significant grow of seq read tuples. After some investigation I found query with strange predicate:

WHERE 1 = case when pb1_0.parent_id is not null then 0 end

It is really strange, and I had to ask, who wrote it.

The reply is - Hibernate. It is a transformation of predicate parent_id = (?) when the list of id is empty.

Unfortunately, PostgreSQL is not able to detect so this predicate is always false, and then the repeated execution ended in repeated full scans.

Fortunately, Postgres has simple workaround - conditional index

CREATE INDEX ON TABLE pb(id)
  WHERE 1 = case when pb1_0.parent_id is not null then 0 end

This index is always empty, and then index scan is fast.

This issue should be fixed in more recent versions of Hibernate where predicate 1=0 is generated instead.

fresh plpgsql_check 2.7.15 for PostgreSQL 16, 17 for MS Windows

 

I compiled and uploaded zip files plpgsql_check for PostgreSQL 16 and PostgreSQL 17 - I used Microsoft Visual C 2022.

Setup:

1. download plpgsql_check-2.7.15-x86_64-windows.zip and extract files 
2. copy related dll file to PostgreSQL lib directory (NN is number of pg release)
   plpgsql_check_NN.dll -> "c:\Program Files\PostgreSQL\NN\lib"
3. copy *.sql and *.control files to extension directory (the version number) should be removed.
   *.sql, *.control -> "c:\Program Files\PostgreSQL\NN\share\extension"
4. execute with super user rights SQL command CREATE EXTENSION
   CREATE EXTENSION plpgsql_check;

plpgsql_check, raise warning when syntax of plpgsql expression is not pure

Originally the PL/pgSQL was very simple language and integration procedural language with SQL was very primitive. Very specific feature of PL/pgSQL is translation of every expression to SQL. Thanks to this translation, the PL/pgSQL is very simple and really fully integrated with SQL engine. Bad side is possibility to write dirty or broken code, that is not detected.

Every expression is translated to queries. So expression `10` is translated to `SELECT 10`. The PL/pgSQL allows dirty expressions like `a FROM foo WHERE x = 10`. Although this syntax is ugly, it was very popular and it is supported and will be supported for ever. 

Unfortunately, this support of this syntax with removing some limits in PostgreSQL 14, allows new kind of bugs (see https://www.postgresql.org/message-id/CALyvM2bp_CXMH_Gyq87pmHJRuZDEhV40u9VP8rX=CAnEt2wUXg@mail.gmail.com).

When somebody write code:

DECLARE var int;
BEGIN
  var := 10
  DELETE FROM tab WHERE varx = 20;
END;

This code on PostgreSQL 13 and older fails, but on PostgreSQL 14 and higher just quietly does nothing. Why?

The problem is in missing semicolon after `10`. PL/pgSQL translates the code to query:

`SELECT 10 DELETE FROM tab WHERE varx = 20`. This query fails on older Postgres, because `DELETE` is keyword and requires usage of `AS` keyword. But PostgreSQL 14 doesn't need it - It allows to usage a keywords like column names without necessity to use `AS` keyword.

I wrote a patch to Postgres - that implements new extra check that can detect this issue https://commitfest.postgresql.org/52/5044/. But this patch is waiting for review half year. I am not sure how much of plpgsql's developers using extra checks.

Today I found a way, how it is possible to detect this issue without necessity to modify SQL parser, and I wrote new check to plpgsql_check (it is merged in master branch):

(2025-02-05 22:17:04) postgres=# create or replace function foofoo()
returns void as $$
declare x int;
begin
  x := 1 delete from foo where x = 10 ;
end;
$$ language plpgsql;
CREATE FUNCTION
(2025-02-05 22:17:07) postgres=# select plpgsql_check_function('foofoo()');
┌────────────────────────────────────────────────────────────────────┐
│                       plpgsql_check_function                       │
╞════════════════════════════════════════════════════════════════════╡
│ warning extra:00000:4:assignment:expression is not pure expression │
│ Query: x := 1 delete from foo where x = 10                         │
│ --     ^                                                           │
│ Detail: there is a possibility of unwanted behave                  │
│ Context: at assignment to variable "x" declared on line 2          │
│ warning extra:00000:2:DECLARE:never read variable "x"              │
└────────────────────────────────────────────────────────────────────┘
(6 rows)

compiled win x64 dll of orafce-4.14.0 and plpgsql_check-2.7.12 for PostgreSQL 16 and 17

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 16 and PostgreSQL 17 - I used Microsoft Visual C 2022.

Setup:

1. download orafce-4.14.0-x64.zip or plpgsql_check-2.7.12-x64.zip 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 "x64-16" or "x64-17" 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
   CREATE EXTENSION orafce;

This was first time when I used meson build system, and I was able to run regress tests. Fortunately the meson reduces lot of monkey work, unfortunately not all - I didn't find a way how to use ninja install with UAC.