Making SQLite behave more like Postgres on transaction error?

Question

Note: RaiseError is set to false.

$dbh->begin_work;
$dbh->do("..."); # sql1, ok
$dbh->do("..."); # sql2, fails (e.g. syntax error)
$dbh->do("..."); # sql3, ok
$dbh->commit;

This will result in the effects of sql1 and sql3 to be committed, which is not desirable, since in this case I want the SQL statements to succeed/fail together. Currently my workaround is this:

eval {
    local $dbh->{RaiseError} = 1;
    $dbh->begin_work;
    $dbh->do("..."); # sql1, ok
    $dbh->do("..."); # sql2, fails (e.g. syntax error)
    $dbh->do("..."); # sql3, ok
    $dbh->commit;
};
$dbh->rollback if $@; # needed, RaiseError does not automatically rollback

but I don't quite like it. Is there another easier alternative? I prefer Postgres' behavior:

$dbh->begin_work;
$dbh->do("..."); # sql1, ok
$dbh->do("..."); # sql2, fails (e.g. syntax error)
$dbh->do("..."); # sql3, ok but fail because transaction status is now aborted
$dbh->commit;    # becomes rollback

Wow, that's kind of nasty. I've got kind of used to Pg's behaviour. Perl doesn't have exceptions, so the driver can't throw an exeption like it does in most languages. It seems you could check for an error return at each call, but you want to avoid that and have calls ignored when the tx enters an error state, right? — Craig Ringer
– Craig Ringer, Commented Aug 22, 2012 at 13:14
Well, Perl does have exceptions, it's called die(), which can throw objects as well as strings. By using eval + RaiseError, I don't have to check the result of every statement. But the construction is 3-4 lines long; I was looking for a shorter alternative. — Steven Haryanto
– Steven Haryanto, Commented Aug 22, 2012 at 13:20
Ah, sorry; my Perl isn't great, I'm coming to this from the Pg side. The following article suggests that the exception handling strategy is the best you'll get portably. Unless: Since RaiseError uses die(), can you re-define die() to give it acccess to $dbh and have it auto-rollback the tx and invalidate $dbh? — Craig Ringer
– Craig Ringer, Commented Aug 22, 2012 at 13:25
I guess you could do something like that, but using RaiseError/die requires keeping the eval {} block. As you suggested in the answer section, I'm afraid a more "magical" solution necessitates writing some wrapper or new feature in DBD::SQLite. — Steven Haryanto
– Steven Haryanto, Commented Aug 22, 2012 at 16:08
Yep, I suspect it's wrapper-or-patch time unless there's some feature hidden in the DBD::SQLite sources or an underdocumented feature in SQLite's C API for the purpose. If DBD::SQLite is well written it shouldn't be too hard to add, as there should only be a few call sites for actual execution of queries that you'd need to wrap in a transaction-health test. — Craig Ringer
– Craig Ringer, Commented Aug 22, 2012 at 23:30

Craig Ringer · Accepted Answer · 2012-08-22 13:46:05Z

One option is to implement a wrapper around DBD::SQLite's db handle, one that keeps track of the error state and ignores commands after an error, just like Pg does. I didn't see any sign that DBD::SQLite can do that, or that SQLite supports such a mode natively, so you probably do have to implement it in a wrapper.

I initially thought that you might want ON CONFLICT ROLLBACK instead of the default ON CONFLICT ABORT, but more reading suggests it won't do the job.

You can set the conflict and error handling resolution strategy. The docs suggest that it works at the table or per statement level, but the syntax map shows that it's really column-by-column, applying to things like NOT NULL constraints.

I'm not sure ON CONFLICT ROLLBACK will leave the tx invalid, and I'm not sure it'll work for all errors either. The docs are kind of unclear. It might roll it back and go into autocommit mode for the next statement, which would be just as bad.

Collectives™ on Stack Overflow

Making SQLite behave more like Postgres on transaction error?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related