Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction and SQL errors

Transaction and SQL errors

From
Sebastien Flaesch
Date:
Hello!

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

set ON_ERROR_ROLLBACK on

in the DB engine?

Most other DB engines have this behavior built-in, that makes migration to PostgreSQL more difficult when there are complex long transactions in the application code.

I do often suggest programmers to trust the DB engine regarding table constraints, and let the engine check unique / foreign keys, etc. However, these errors cancel the whole transaction and user code can't correct the last SQL statement and try a new execution, without losing everything since TX started...

Any thoughts?

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

Seb

Re: Transaction and SQL errors

From
Karsten Hilbert
Date:
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

Re: Transaction and SQL errors

From
David Rowley
Date:
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

Re: Transaction and SQL errors

From
Sebastien Flaesch
Date:
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

Re: Transaction and SQL errors

From
Sebastien Flaesch
Date:
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].

David

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

Re: Transaction and SQL errors

From
"J. Roeleveld"
Date:
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

Re: Transaction and SQL errors

From
"David G. Johnston"
Date:
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.

Re: Transaction and SQL errors

From
Sebastien Flaesch
Date:
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

Re: Transaction and SQL errors

From
Gilles Darold
Date:
Le 04/04/2022 à 18:20, Sebastien Flaesch a écrit :
P {margin-top:0;margin-bottom:0;}
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] 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] https://www.postgresql.org/message-id/983d80b3-d187-127a-2de5-38c92ccb38ab%40darold.net  

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

Re: Transaction and SQL errors

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

Re: Transaction and SQL errors

From
Sebastien Flaesch
Date:
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