Hello,
Back on this "feature", I did some experiments by changing the code of our PostgreSQL driver (db connector)...
We have our own programming language and VM/runtime.
With a config setting (I don't want to enable this by default), our driver now automatically adds a SAVEPOINT before any SQL statement executed in a transaction block, and issues an automatic ROLLBACK TO SAVEPOINT in case of SQL error, or RELEASE SAVEPOINT, if not SQL error occurred (I think last one is useless, but I prefer to cleanup)
From a functional point of view, it does the job, and we get now the same behavior as with other DB engines.
However, depending on the SQL statements in the transaction, the execution time can increase by x10 to x20
I have tested with PostgreSQL 14.1:
Same code takes 12.6 seconds with PostgreSQL, while it takes 0.5 to 2 seconds with other DB engines.
The code (written on our own programming language) is basically doing this:
BEGIN WORK
FOR x=1 TO 1000
DELETE FROM tab2 WHERE pkey = 1001
DELETE FROM tab1 WHERE pkey = 102
INSERT INTO tab1 VALUES ( 102, 'bbbb' )
INSERT INTO tab2 VALUES ( 1001, 'xxx1', 101 )
SAVEPOINT sp101
UPDATE tab1 SET name = 'zzzz' WHERE pkey = 102
ROLLBACK WORK TO SAVEPOINT sp101
END FOR
COMMIT WORK
So, I was wondering if someone can comment on the cost of a ROLLBACK TO SAVEPOINT...
Yes, this should not occur often.
But imagine some code that tries to INSERT or UPDATE rows, relies in DB constraints like UNIQUE to try other values, or relies on FOREIGN KEY constraints to DELETE some rows and in case of SQL error wants to continue the TX by deleting other rows...
I just want to anticipate customers complains that it's slower as expected.
Seb