5 ms·

Oh that "returning" is nice; in current SQLite I'm doing a second query to get that: SELECT last_insert_rowid()

edoceo 6y ago
Yea, this one and column removal are my two favourites. And it makes it easier to switch this and PG
masklinn 6y ago
Especially since last_insert_rowid() can never be free of race condition, and is basically useless whenever ON CONFLICT is involved.
- scottlamb 6y ago
  > Especially since last_insert_rowid() can never be free of race condition I think that's overstating it. It's per-connection, not per-database. If you don't share connections between threads, it's fine. If you keep the connection locked / checked out for the span of the two statement, it's fine. You're probably doing the latter anyway if you use transactions. Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
  - masklinn 6y ago
    > Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one. That… seems normal? Returning just one row would make no sense, how would even a trivial `RETURNING id` work otherwise?
    - scottlamb 6y ago
      Sure, but I'm comparing to last_insert_rowid(), which can't do that. Also, last_insert_rowid() (by definition) doesn't work with "without rowid" tables, where returning can.
      - masklinn 6y ago
        Ah I see, I was a bit confused because I'm used to RETURNING from pg and I've been waiting for sqlite to add it for a very long time. Especially since ON CONFLICT was added as it made the issue even worse.
bob1029 6y ago
This sounds incredible. I am going to have to do some testing. Right now, we lock on a single SQLiteConnection instance because of the fact that insert & id retrieval are 2 separate invocations. If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.
- masklinn 6y ago
  > If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level. Not only would you have to ensure you're always setting the db to SQLITE_OPEN_FULLMUTEX, https://sqlite.org/c3ref/errcode.html https://sqlite.org/c3ref/errcode.html and https://sqlite.org/c3ref/changes.html https://sqlite.org/c3ref/changes.html are still not coherent in multithreaded contexts. Calling them won't corrupt anything, but they'll return garbage. The latter probably doesn't matter much, but the former seems quite relevant if you want to handle errors beyond "this failed".
  - bob1029 6y ago
    Correct - We open our databases in serialized mode and access a single connection from multiple threads. We actually are not concerned with handling detailed error information. Our usage of these databases is very well bounded. All of the SQL that will ever execute against them is contained in 1 series of constant declarations, and the mappers ensure request data is well-formed before attempting inserts & updates. SQLite is really just there to provide identity and persistence for our business objects. We are still able to use these fields effectively in local development and unit testing, since in these contexts there is only ever 1 thread hitting the database at a time.
fastball 6y ago
I'm actually surprised it took SQLite this long to implement RETURNING, such a useful SQL language feature.
- wnoise 6y ago
  It's not part of any standard, so they'd need to pick what to implement. They docs say they're modeling it on postgres's, which is a perfectly reasonable extension. https://www.sqlite.org/lang_returning.html https://www.sqlite.org/lang_returning.html
  - fastball 6y ago
    True, though SQL isn't much of a standard anyway. I literally don't know of a single SQL database that implements the SQL standard to spec with no fiddling. Or more precisely, the vast majority of SQL databases out of the box behave differently, so anything more complex than a basically SELECT + JOIN generally isn't very cross-compatible.
pimeys 6y ago
Now only MySQL is missing `RETURNING` from the relational databases that still matter. Postgres had it for ages, SQL Server has it with OUTPUT (with a caveat of breaking when using triggers) and SQLite finally added them on this release. Using `RETURNING` is so much nicer than spending four round-trips to `BEGIN`, `INSERT`, `SELECT` and `COMMIT` otherwise...

Yea, this one and column removal are my two favourites. And it makes it easier to switch this and PG

Especially since last_insert_rowid() can never be free of race condition, and is basically useless whenever ON CONFLICT is involved.

scottlamb 6y ago
> Especially since last_insert_rowid() can never be free of race condition I think that's overstating it. It's per-connection, not per-database. If you don't share connections between threads, it's fine. If you keep the connection locked / checked out for the span of the two statement, it's fine. You're probably doing the latter anyway if you use transactions. Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
- masklinn 6y ago
  > Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one. That… seems normal? Returning just one row would make no sense, how would even a trivial `RETURNING id` work otherwise?
  - scottlamb 6y ago
    Sure, but I'm comparing to last_insert_rowid(), which can't do that. Also, last_insert_rowid() (by definition) doesn't work with "without rowid" tables, where returning can.
    - masklinn 6y ago
      Ah I see, I was a bit confused because I'm used to RETURNING from pg and I've been waiting for sqlite to add it for a very long time. Especially since ON CONFLICT was added as it made the issue even worse.

bob1029 6y ago

This sounds incredible. I am going to have to do some testing. Right now, we lock on a single SQLiteConnection instance because of the fact that insert & id retrieval are 2 separate invocations. If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.

masklinn 6y ago
> If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level. Not only would you have to ensure you're always setting the db to SQLITE_OPEN_FULLMUTEX, https://sqlite.org/c3ref/errcode.html https://sqlite.org/c3ref/errcode.html and https://sqlite.org/c3ref/changes.html https://sqlite.org/c3ref/changes.html are still not coherent in multithreaded contexts. Calling them won't corrupt anything, but they'll return garbage. The latter probably doesn't matter much, but the former seems quite relevant if you want to handle errors beyond "this failed".
- bob1029 6y ago
  Correct - We open our databases in serialized mode and access a single connection from multiple threads. We actually are not concerned with handling detailed error information. Our usage of these databases is very well bounded. All of the SQL that will ever execute against them is contained in 1 series of constant declarations, and the mappers ensure request data is well-formed before attempting inserts & updates. SQLite is really just there to provide identity and persistence for our business objects. We are still able to use these fields effectively in local development and unit testing, since in these contexts there is only ever 1 thread hitting the database at a time.

fastball 6y ago

I'm actually surprised it took SQLite this long to implement RETURNING, such a useful SQL language feature.

wnoise 6y ago
It's not part of any standard, so they'd need to pick what to implement. They docs say they're modeling it on postgres's, which is a perfectly reasonable extension. https://www.sqlite.org/lang_returning.html https://www.sqlite.org/lang_returning.html
- fastball 6y ago
  True, though SQL isn't much of a standard anyway. I literally don't know of a single SQL database that implements the SQL standard to spec with no fiddling. Or more precisely, the vast majority of SQL databases out of the box behave differently, so anything more complex than a basically SELECT + JOIN generally isn't very cross-compatible.

pimeys 6y ago

Now only MySQL is missing `RETURNING` from the relational databases that still matter. Postgres had it for ages, SQL Server has it with OUTPUT (with a caveat of breaking when using triggers) and SQLite finally added them on this release. Using `RETURNING` is so much nicer than spending four round-trips to `BEGIN`, `INSERT`, `SELECT` and `COMMIT` otherwise...