SQLite ON CONFLICT and transaction issues

[vtzi]

SQLite has ON CONFLICT clause that is very handy to bulk insert/update with a single sql statement where otherwise you'll need to begin/commit transaction and do some bookkeeping and handling of conflicts.

https://www.sqlite.org/lang_conflict.html

When trying to use this with SQLPage I get a parsing error:

Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:

UPDATE OR REPLACE doctags SET tag = trim(lower(:new_tag))
                 ⬆️
Backtrace
sql parser error: Expected: SET, found: doctags at Line: 13, Column: 19
SQLPage could not parse and prepare this SQL statement

I guess that's not SQLPage but rather sqlx issue but would be nice to get reported somewhere.

On a related note - what is the good way to handle errors within multi-statement transactions?
E.g.

BEGIN TRANSACTION;
DELETE ...
UPDATE ...
..etc
COMMIT;

My experience with SQLite, is that an error inside the transaction leaves the connection with an open transaction (which later can lead to way more serious issues). It'll be good to be able to rollback on errors (or simpler from implementation pov - close/reopen the connection which should invoke the rollback).

[lovasoa]

Hi ! Thanks for reporting the parsing issue. I opened a pull request to fix it upstream: apache/datafusion-sqlparser-rs#1530

[lovasoa]

For the transaction issue, let me dig into it in more details, and I'll come back to you about it soon.

[lovasoa]

My fix for the update or replace bug was just approved in the parser. It should get into SQLPage on December the 11th apache/datafusion-sqlparser-rs#1517

[vtzi]

Thank you! My current use case is mostly read-only with very simple inserts/updates and this replace or update solves almost 100% of my needs.

However the second issue is really serious if I'm not mistaken that on error the connection is being returned to the pool with an open transaction.

[bever1337]

Is the remaining question in your discussion related to mine @vtzi ?

#710

I'm having trouble learning how sqlpage manages transactions. The goal being to create and drop temporary tables in a single transaction. I'm using postgres, not sqlite, but observe that sqlpage does not commit.

[vtzi]

Kind of related but not the same, imo.

Basically we as developers are responsible for transaction management.
In your case just wrapping everything in begin/commit is enough BUT ONLY IF there are no errors (e.g. constraint violation or whatever) within the transaction. In case of errors these are getting reported and the control never reaches commit (or rollback if present). The end result is that connection with an open transaction is returned to the pool and will be reused by other requests - leading to very confusing and hard to reason about errors.

[bever1337]

The end result is that connection with an open transaction is returned to the pool and will be reused by other requests - leading to very confusing and hard to reason about errors.

It looks like this is sql engine specific. Is that right? In postgres, the entire transaction is rolled back and aborted on exception. IIUC a raised exception should be safe. Thanks for catching me up.

https://www.postgresql.org/docs/7.3/plpgsql-errors-and-messages.html

PostgreSQL does not have a very smart exception handling model. Whenever the parser, planner/optimizer or executor decide that a statement cannot be processed any longer, the whole transaction gets aborted and the system jumps back into the main loop to get the next query from the client application.

https://www.sqlite.org/lang_transaction.html

For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction.

So the SQLPage solution needs to be flexible enough for auto-abort and not-so-auto-abort