> 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
