Transaction and SQL errors

Am Mon, Apr 04, 2022 at 11:33:14AM +0000 schrieb Sebastien Flaesch:

Is there any plan to have an equivalent of psql's

set ON_ERROR_ROLLBACK on

in the DB engine?

That is already what happens.

SQL fails, transaction rolls back.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch
<sebastien.flaesch@4js.com> wrote:

Any arguments I could give to programmers? Maybe:
"You better replay all SQL of the whole transaction... (and make them as short as possible!)"
?

There are SAVEPOINTs [1]https://www.postgresql.org/docs/current/sql-savepoint.html.

David

[1]: https://www.postgresql.org/docs/current/sql-savepoint.html

Hi Karsten,

No... see the doc: https://www.postgresql.org/docs/14/app-psql.html

ON_ERROR_ROLLBACK

When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues.

Seb

Hi David,

I know savepoints are supported by PostgreSQL, but what about existing/legacy code that has tons of lines that rely on the behavior of other DB engines?

Would you ask programmers to put a savepoint / release savepoint around each SQL statement inside a TX?

Seb
________________________________
From: David Rowley <dgrowleyml@gmail.com>
Sent: Monday, April 4, 2022 1:44 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Transaction and SQL errors

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch
<sebastien.flaesch@4js.com> wrote:

Any arguments I could give to programmers? Maybe:
"You better replay all SQL of the whole transaction... (and make them as short as possible!)"
?

There are SAVEPOINTs [1]https://urldefense.com/v3/__https://www.postgresql.org/docs/current/sql-savepoint.html__;!!I_DbfM1H!SJaCAE7hUHxRte8uMaa9RB8byL3QsmxkBRzk9POp0N8sLvjhpL5AWoiH-7MEuTkZMcrl$.

David

[1]: https://urldefense.com/v3/__https://www.postgresql.org/docs/current/sql-savepoint.html__;!!I_DbfM1H!SJaCAE7hUHxRte8uMaa9RB8byL3QsmxkBRzk9POp0N8sLvjhpL5AWoiH-7MEuTkZMcrl$

On Monday, April 4, 2022 1:44:40 PM CEST David Rowley wrote:

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch

<sebastien.flaesch@4js.com> wrote:

Any arguments I could give to programmers? Maybe:
"You better replay all SQL of the whole transaction... (and make them as
short as possible!)" ?

There are SAVEPOINTs [1].

David

[1] https://www.postgresql.org/docs/current/sql-savepoint.html

Thank you! :)
I wasn't aware of this yet, but it will make some of my code easier to read .

--
Joost

On Mon, Apr 4, 2022 at 5:41 AM Sebastien Flaesch <sebastien.flaesch@4js.com>
wrote:

I know savepoints are supported by PostgreSQL, but what about
existing/legacy code that has tons of lines that rely on the behavior of
other DB engines?

Would you ask programmers to put a savepoint / release savepoint around
each SQL statement inside a TX?

That does have the benefit of being a relatively straight-forward solution.

The answer to your original question is no. To my knowledge there is
presently no one who has broadcast their intent to add this feature, now or
in the foreseeable future.

Personally, I can see where it has significant value for psql in
interactive mode because people make typos. Application code doesn't.
That removes a whole class of problems where the feature provides benefit.

Whether "everybody is doing it" overcomes "but it's not a good thing to do"
remains to be seen but at least for now we are just saying no to an
automatic rollback setting.

David J.

David,

Personally, I can see where it has significant value for psql in interactive mode because people make typos. Application code doesn't. That removes a whole class of problems where the feature provides benefit.

Sure, application code must not have typos, but I prefer to let the DB engine check for SQL constraints.

Imagine the following case:

BEGIN WORK
... (some other SQL) ...
DELETE FROM items WHERE item_id = 12823 -- Can raise foreign key error
if sql-error then
...

To me it's better than:

BEGIN WORK
...
SELECT ... FROM orders WHERE item_id = 12823
if not-found then -- make sure we get no SQL error than cancels TX!
DELETE FROM items WHERE item_id = 12823
endif
...

... and not even sure it's valid atomic code depending on isolation level...

A good argument for PostgreSQL's behavior would be that it's better to cancel the whole transaction and restart all SQL commands.

However, legacy code is often spaghetti code where one function starts the TX, then calls other functions doing SQL ... ( yes, good candidate for savepoints usage! )

Anyway, thanks for the info, nothing planed short term, and that's what I was asking for.

Cheers!
Seb

Le 04/04/2022 à 18:20, Sebastien Flaesch a écrit :

David,

Personally, I can see where it has significant value for psql in
interactive mode because people make typos.  Application code
doesn't.  That removes a whole class of problems where the feature
provides benefit.

Sure, application code must not have typos, but I prefer to let the DB
engine check for SQL constraints.

Imagine the following case:

BEGIN WORK
... (some other SQL) ...
DELETE FROM items WHERE item_id = 12823  -- Can raise foreign key
error if sql-error then
...

To me it's better than:

BEGIN WORK
...
SELECT ... FROM orders WHERE item_id = 12823
if not-found then -- make sure we get no SQL error than cancels TX!
  DELETE FROM items WHERE item_id = 12823
endif
...

... and not even sure it's valid atomic code depending on isolation
level...

A good argument for PostgreSQL's behavior would be that it's better to
cancel the whole transaction and restart all SQL commands.

However, legacy code is often spaghetti code where one function starts
the TX, then calls other functions doing SQL ... ( yes, good candidate
for savepoints usage! )

Anyway, thanks for the info, nothing planed short term, and that's
what I was asking for.

Cheers!
Seb

FYI there was a discussion [1]/messages/by-id/983d80b3-d187-127a-2de5-38c92ccb38ab@darold.net and a patch to allow this feature through
an extension because having this implemented in core will probably never
happen. Everything was ready but the final status is Rejected so I don't
think you might expect any planned work on this feature in any term. But
who knows, things can evolve.

[1]: /messages/by-id/983d80b3-d187-127a-2de5-38c92ccb38ab@darold.net
/messages/by-id/983d80b3-d187-127a-2de5-38c92ccb38ab@darold.net

--
Gilles Darold
http://www.darold.net/

Hello Gilles and thanks for that link!
I will definitively add that info to our issue tracker/db.
Seb

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