> From: Lassi Kortela <xxxxxx@lassi.io> > Subject: Re: John Cowan's preliminary notes on a SQLite oriented Simple SQL (ssql) API > Date: Thursday, September 12, 2019 8:46 AM > > Once again, thanks to John for the draft. > >> [ This is Peter Bex: ] > >> [ Let's not use transaction objects. ] >> >> - I would extend transaction support to include a "level". Like I >> mentioned in my other mail, you can nest transactions via >> savepoints which can be individually rolled back or "committed" >> (which is a no-op). > The main thing is that we need to have an API where transactions can > be nested arbitrarily without manually counting the nesting depth > (or even knowing what the depth is). In my experience, gets > troublesome to manage even a relatively small codebase if you can't > wield "with-transaction" blocks around freely. > > I don't know how to get the best support for this pattern from the > DB engines, but whatever it takes, I'd almost rate it a must.... I agree, and we may have an tentative consensus on this. > From: Peter Bex <xxxxxx@more-magic.net> > Subject: Re: John Cowan's preliminary notes on a SQLite oriented Simple SQL (ssql) API > Date: Saturday, September 14, 2019 4:41 AM > > On Sat, Sep 14, 2019 at 12:49:48AM -0400, John Cowan wrote: >> On Thu, Sep 12, 2019 at 8:11 AM Peter Bex <xxxxxx@more-magic.net> wrote: > > [...] > > You can provide a nested API that goes up to any level (the Postgres > egg does this). The important thing is that you always rollback > savepoints one by one. Here's a nesting of three levels: > > BEGIN TRANSACTION; > ... > SAVEPOINT s1; > ... > SAVEPOINT s2; > ... > RELEASE SAVEPOINT s2; > ... > RELEASE SAVEPOINT s1; > ... > COMMIT TRANSACTION; > > You can also rollback the innermost savepoint s2 without also > rolling back s1 or the surrounding "proper" transaction. So as far > as I'm concerned, these semantics are a perfect fit for a nested > "with-transaction" operator. So you'd do it like http://wiki.call-cc.org/eggref/5/postgresql > (with-transaction CONN THUNK [isolation: LEVEL] [access: MODE]) > Execute THUNK within a BEGIN TRANSACTION block, and return the value > of thunk. > The transaction is committed if thunk returns a true value. If an > exception occurs during thunk, or thunk returns #f, or the commit > fails, the transaction will be rolled back. If this rollback fails, > that is a critical error and you should likely abort. > Nested applications of with-transaction are supported -- only those > statements executed within THUNK are committed or rolled back by any > with-transaction call, as you would expect. Without looking at any code, what are the mechanics of rolling back an inner savepoint? Do you execute a bare statement (ugly) and then return non-#f from the inner with-transaction's thunk? And I have to say that I really don't like the code style you get with naive use of this (with THUNK) paradigm, even before it gets nested. While making things very clear in one way, it's also very ugly, and it radically distinguishes the operations in the THUNKs compared to single ones which get by with auto-commit mode. (Requiring the wrapping those with a (with-fake-transaction THUNK) will fix the distinguishing issue while making *everything* ugly.) A further argument is that if my above partly aesthetic claim is correct, the ugly ceremony required will to an extent discourage the use of transactions, which would be bad.... I suppose I want something that looks more like how you'd plainly write it out, but with with-transaction magic decorating it instead intermingling with BEGIN TRANSACTION etc. operations. Wouldn't fixing that calls for macro magic? How much can and should we require (fancy?) macros in an SRFI? Note to Lassi: the more I think about it, plus glancing at this egg, I don't think I'll have more than outline level API results in 2-3 days, and probably trivially obvious ones. This is the part of the stack a user will intimately work (unless there's a DSL or whatever on top), and it needs to support N different databases and maybe even non-RDMBS databases as well. And do it well, handling as many quirks in a general fashion as possible, see my next message. - Harold
