I've been waiting for this email! I'll echo what Jeff said about how exciting this is for the project.
On the SELECT inside the transaction: In the first example, I'm making an assumption that you are doing a select on a partition key and only expect one result but is any valid CQL SELECT allowed here? If 'model' were a non-partition key column name and was indexed, then you could potentially have multiple rows returned and that isn't an allowed operation. Are only partition key lookups allowed or is there some logic looking for only one row? I'm asking because I can see in reverse time series models where you can select the latest temperature SELECT temperature FROM weather_station WHERE id=1234 AND DATE='2022-06-04' LIMIT 1; (also, horrible example. Everyone knows that the return value for a Pinto.is_running will always evaluate to FALSE) On COMMIT TRANSACTION: So much to unpack here. In the case that the condition is met, is the mutation applied at that point, or has it already happened and there is something like a rollback segment? What is the case when the condition is not met and what is presented to the end-user? More importantly, what happens with respect to the A & I in ACID when the transaction is applied? If UPDATE is used, returning the number of rows changed would be helpful. Is this something that can be done interactively in cqlsh or does it all have to be submitted in one statement block? I'll stop here for now. Patrick On Sat, Jun 4, 2022 at 3:34 PM bened...@apache.org <bened...@apache.org> wrote: > > The returned result set is after the updates are applied? > > Returning the prior values is probably more powerful, as you can perform > unconditional updates and respond to the prior state, that you otherwise > would not know. It’s also simpler to implement. > > > > My inclination is to require that SELECT statements are declared first, so > that we leave open the option of (in future) supporting SELECT statements > in any place in the transaction, returning the values as of their position > in a sequential execution of the statements. > > > > > And would you allow a transaction that had > 1 named select and no > modification statements, but commit if 1=1 ? > > > > My preference is that the IF condition is anyway optional, as it is much > more obvious to a user than concocting some always-true condition. But yes, > read-only transactions involving multiple tables will definitely be > supported. > > > > > > *From: *Jeff Jirsa <jji...@gmail.com> > *Date: *Saturday, 4 June 2022 at 22:49 > *To: *dev@cassandra.apache.org <dev@cassandra.apache.org> > *Subject: *Re: CEP-15 multi key transaction syntax > > > And would you allow a transaction that had > 1 named select and no > modification statements, but commit if 1=1 ? > > > On Jun 4, 2022, at 2:45 PM, Jeff Jirsa <jji...@gmail.com> wrote: > > > > > > > >> On Jun 3, 2022, at 8:39 AM, Blake Eggleston <beggles...@apple.com> > wrote: > >> > >> Hi dev@, > > > > First, I’m ridiculously excited to see this. > > > >> > >> I’ve been working on a draft syntax for Accord transactions and wanted > to bring what I have to the dev list to solicit feedback and build > consensus before moving forward with it. The proposed transaction syntax is > intended to be an extended batch syntax. Basically batches with selects, > and an optional condition at the end. To facilitate conditions against an > arbitrary number of select statements, you can also name the statements, > and reference columns in the results. To cut down on the number of > operations needed, select values can also be used in updates, including > some math operations. Parameterization of literals is supported the same as > other statements. > >> > >> Here's an example selecting a row from 2 tables, and issuing updates > for each row if a condition is met: > >> > >> BEGIN TRANSACTION; > >> SELECT * FROM users WHERE name='blake' AS user; > >> SELECT * from cars WHERE model='pinto' AS car; > >> UPDATE users SET miles_driven = user.miles_driven + 30 WHERE > name='blake'; > >> UPDATE cars SET miles_driven = car.miles_driven + 30 WHERE > model='pinto'; > >> COMMIT TRANSACTION IF car.is_running; > >> > >> This can be simplified by naming the updates with an AS <name> syntax. > If updates are named, a corresponding read is generated behind the scenes > and its values inform the update. > >> > >> Here's an example, the query is functionally identical to the previous > query. In the case of the user update, a read is still performed behind the > scenes to enable the calculation of miles_driven + 30, but doesn't need to > be named since it's not referenced anywhere else. > >> > >> BEGIN TRANSACTION; > >> UPDATE users SET miles_driven += 30 WHERE name='blake'; > >> UPDATE cars SET miles_driven += 30 WHERE model='pinto' AS car; > >> COMMIT TRANSACTION IF car.is_running; > >> > >> Here’s another example, performing the canonical bank transfer: > >> > >> BEGIN TRANSACTION; > >> UPDATE accounts SET balance += 100 WHERE name='blake' AS blake; > >> UPDATE accounts SET balance -= 100 WHERE name='benedict' AS benedict; > >> COMMIT TRANSACTION IF blake EXISTS AND benedict.balance >= 100; > >> > >> As you can see from the examples, column values can be referenced via a > dot syntax, ie: <select_name>.<column> -> select1.value. Since the read > portion of the transaction is performed before evaluating conditions or > applying updates, values read can be freely applied to non-primary key > values in updates. Select statements used either in checking a condition or > creating an update must be restricted to a single row, either by specifying > the full primary key or a limit of 1. Multi-row selects are allowed, but > only for returning data to the client (see below). > >> > >> For evaluating conditions, = & != are available for all types, <, <=, > >, >= are available for numerical types, and EXISTS, NOT EXISTS can be used > for partitions, rows, and values. If any column references cannot be > satisfied by the result of the reads, the condition implicitly fails. This > prevents having to include a bunch of exists statements. > > > > Is there a new keyword for “partition (not) exists” or is it inferred by > the select? > > > >> > >> On completion, an operation would return a boolean value indicating the > operation had been applied, and a result set for each named select (but not > named update). We could also support an optional RETURN keyword, which > would allow the user to only return specific named selects (ie: RETURN > select1, select2). > >> > > > > The returned result set is after the updates are applied? > > > > > >> Let me know what you think! > >> > >> Blake >
