So, thinking on it myself some more, I think if there’s an option that *doesn’t* require the user to reason about the point at which the read happens in order to understand how the condition is applied would probably be better.
What do you think of the IF (Boolean expr) ABORT TRANSACTION idea? It’s compatible with more advanced IF functionality later, and probably not much trickier to implement? The COMMIT IF syntax is more succinct, but ambiguity isn’t ideal and we only get one chance to make this API right. From: Blake Eggleston <beggles...@apple.com> Date: Friday, 10 June 2022 at 18:56 To: dev@cassandra.apache.org <dev@cassandra.apache.org> Subject: Re: CEP-15 multi key transaction syntax Yeah I think that’s intuitive enough. I had been thinking about multiple condition branches, but was thinking about something closer to IF select.column=5 UPDATE ... SET ... WHERE key=1; ELSE IF select.column=6 UPDATE ... SET ... WHERE key=2; ELSE UPDATE ... SET ... WHERE key=3; ENDIF COMMIT TRANSACTION; Which would make the proposed COMMIT IF we're talking about now a shorthand. Of course this would be follow on work. On Jun 8, 2022, at 1:20 PM, bened...@apache.org<mailto:bened...@apache.org> wrote: I imagine that conditions would be evaluated against the state prior to the execution of statement against which it is being evaluated, but after the prior statements. I think that should be OK to reason about. i.e. we might have a contrived example like: BEGIN TRANSACTION UPDATE tbl SET a = 1 WHERE k = 1 AS q1 UPDATE tbl SET a = q1.a + 1 WHERE k = 1 AS q2 COMMIT TRANSACTION IF q1.a = 0 AND q2.a = 1 So q1 would read a = 0, but q2 would read a = 1 and set a = 2. I think this is probably adequately intuitive? It is a bit atypical to have conditions that wrap the whole transaction though. We have another option, of course, which is to offer IF x ROLLBACK TRANSACTION, which is closer to SQL, which would translate the above to: BEGIN TRANSACTION SELECT a FROM tbl WHERE k = 1 AS q0 IF q0.a != 0 ROLLBACK TRANSACTION UPDATE tbl SET a = 1 WHERE k = 1 AS q1 IF q1.a != 1 ROLLBACK TRANSACTION UPDATE tbl SET a = q1.a + 1 WHERE k = 1 AS q2 COMMIT TRANSACTION This is less succinct, but might be more familiar to users. We could also eschew the ability to read from UPDATE statements entirely in this scheme, as this would then look very much like SQL. From: Blake Eggleston <beggles...@apple.com<mailto:beggles...@apple.com>> Date: Wednesday, 8 June 2022 at 20:59 To: dev@cassandra.apache.org<mailto:dev@cassandra.apache.org> <dev@cassandra.apache.org<mailto:dev@cassandra.apache.org>> Subject: Re: CEP-15 multi key transaction syntax > It affects not just RETURNING but also conditions that are evaluated against > the row, and if we in future permit using the values from one select in a > function call / write to another table (which I imagine we will). I hadn’t thought about that... using intermediate or even post update values in condition evaluation or function calls seems like it would make it difficult to understand why a condition is or is not applying. On the other hand, it would powerful, especially when using things like database generated values in queries (auto incrementing integer clustering keys or server generated timeuuids being examples that come to mind). Additionally, if we return these values, I guess that would solve the visibility issues I’m worried about. Agreed intermediate values would be straightforward to calculate though. On Jun 6, 2022, at 4:33 PM, bened...@apache.org<mailto:bened...@apache.org> wrote: It affects not just RETURNING but also conditions that are evaluated against the row, and if we in future permit using the values from one select in a function call / write to another table (which I imagine we will). I think that for it to be intuitive we need it to make sense sequentially, which means either calculating it or restricting what can be stated (or abandoning the syntax). If we initially forbade multiple UPDATE/INSERT to the same key, but permitted overlapping DELETE (and as many SELECT as you like) that would perhaps make it simple enough? Require for now that SELECTS go first, then DELETE and then INSERT/UPDATE (or vice versa, depending what we want to make simple)? FWIW, I don’t think this is terribly onerous to calculate either, since it’s restricted to single rows we are updating, so we could simply maintain a collections of rows and upsert into them as we process the execution. Most transactions won’t need it, I suspect, so we don’t need to worry about perfect efficiency. From: Blake Eggleston <beggles...@apple.com<mailto:beggles...@apple.com>> Date: Tuesday, 7 June 2022 at 00:21 To: dev@cassandra.apache.org<mailto:dev@cassandra.apache.org> <dev@cassandra.apache.org<mailto:dev@cassandra.apache.org>> Subject: Re: CEP-15 multi key transaction syntax That's a good question. I'd lean towards returning the final state of things, although I could understand expecting to see intermediate state. Regarding range tombstones, we could require them to precede any updates like selects, but there's still the question of how to handle multiple updates to the same cell when the user has requested we return the post-update state of the cell. On Jun 6, 2022, at 4:00 PM, bened...@apache.org<mailto:bened...@apache.org> wrote: > if multiple updates end up touching the same cell, I’d expect the last one to > win Hmm, yes I suppose range tombstones are a plausible and reasonable thing to mix with inserts over the same key range. What’s your present thinking about the idea of handling returning the values as of a given point in the sequential execution then? The succinct syntax is I think highly desirable for user experience, but this does complicate it a bit if we want to remain intuitive. From: Blake Eggleston <beggles...@apple.com<mailto:beggles...@apple.com>> Date: Monday, 6 June 2022 at 23:17 To: dev@cassandra.apache.org<mailto:dev@cassandra.apache.org> <dev@cassandra.apache.org<mailto:dev@cassandra.apache.org>> Subject: Re: CEP-15 multi key transaction syntax Hi all, Thanks for all the input and questions so far. Glad people are excited about this! I didn’t have any free time to respond this weekend, although it looks like Benedict has responded to most of the questions so far, so if I don’t respond to a question you asked here, you can interpret that as “what Benedict said” :). Jeff, > Is there a new keyword for “partition (not) exists” or is it inferred by the > select? I'd intended this to be worked out from the select statement, ie: if the read/reference is null/empty, then it doesn't exist, whether you're interested in the partition, row, or cell. So I don't think we'd need an additional keyword there. I think that would address partition exists / not exists use cases? > And would you allow a transaction that had > 1 named select and no > modification statements, but commit if 1=1 ? Yes, an unconditional commit (ie: just COMMIT TRANSACTION; without an IF) would be part of the syntax. Also, running a txn that doesn’t contain updates wouldn’t be a problem. Patrick, I think Benedict answered your questions? Glad you got the joke :) Alex, > 1. Dependant SELECTs > 2. Dependant UPDATEs > 3. UPDATE from secondary index (or SASI) > 5. UPDATE with predicate on non-primary key The full primary key must be defined as part of the statement, and you can’t use column references to define them, so you wouldn’t be able to run these. > MVs To prevent being spread too thin, both in syntax design and implementation work, I’d like to limit read and write operations in the initial implementation to vanilla selects, updates, inserts, and deletes. Once we have a solid implementation of multi-key/table transactions supporting foundational operations, we can start figuring out how the more advanced pieces can be best supported. Not a great answer to your question, but a related tangent I should have included in my initial email. > ... RETURNING ... I like the idea of the returning statement, but to echo what Benedict said, I think any scheme for specifying data to be returned should apply the same to select and update statements, since updates can have underlying reads that the user may be interested in. I’d mentioned having an optional RETURN statement in addition to automatically returning selects in my original email. > ... WITH ... I like the idea of defining statement names at the beginning of a statement, since I could imagine mapping names to selects might get difficult if there are a lot of columns in the select or update, but beginning each statement with `WITH <name>` reduces readability imo. Maybe putting the name after the first term of the statement (ie: `SELECT * AS <name> WHERE...`, `UPDATE table AS <name> SET ...`, `INSERT INTO table AS <name> (...) VALUES (...);`) would be improve finding names without harming overall readability? Benedict, > I agree that SELECT statements should be required to go first. +1 > There only remains the issue of conditions imposed upon UPDATE/INSERT/DELETE > statements when there are multiple statements that affect the same primary > key. I think we can (and should) simply reject such queries for now, as it > doesn’t make much sense to have multiple statements for the same primary key > in the same transaction. Unfortunately, I think there are use cases for both multiple selects and updates for the same primary key in a txn. Selects aren’t as problematic, but if multiple updates end up touching the same cell, I’d expect the last one to win. This would make dealing with range tombstones a little trickier, since the default behavior of alternating updates and range tombstones affecting the same cells is not intuitive, but I don’t think it would be too bad. Something that’s come up a few times, and that I’ve also been thinking about is whether to return the values that were originally read, or the values written with the update to the client, and there are use cases for both. I don’t remember who suggested it, but I think returning the original values from named select statements, and the post-update values from named update statements is a good way to handle both. Also, while returning the contents of the mutation would be the easiest, implementation wise, swapping cell values from the updates named read would be most useful, since a txn won’t always result in an update, in which case we’d just return the select. Thanks, Blake On Jun 6, 2022, at 9:41 AM, Henrik Ingo <henrik.i...@datastax.com<mailto:henrik.i...@datastax.com>> wrote: On Mon, Jun 6, 2022 at 5:28 PM bened...@apache.org<mailto:bened...@apache.org> <bened...@apache.org<mailto:bened...@apache.org>> wrote: > One way to make it obvious is to require the user to explicitly type the > SELECTs and then to require that all SELECTs appear before > UPDATE/INSERT/DELETE. Yes, I agree that SELECT statements should be required to go first. However, I think this is sufficient and we can retain the shorter format for RETURNING. There only remains the issue of conditions imposed upon UPDATE/INSERT/DELETE statements when there are multiple statements that affect the same primary key. I think we can (and should) simply reject such queries for now, as it doesn’t make much sense to have multiple statements for the same primary key in the same transaction. I guess I was thinking ahead to a future where and UPDATE write set may or may not intersect with a previous update due to allowing WHERE clause to use secondary keys, etc. That said, I'm not saying we SHOULD require explicit SELECT statements for every update. I'm sure that would be annoying more than useful.I was just following a train of thought. > Returning the "result" from an UPDATE presents the question should it be the > data at the start of the transaction or end state? I am inclined to only return the new values (as proposed by Alex) for the purpose of returning new auto-increment values etc. If you require the prior value, SELECT is available to express this. That's a great point! > I was thinking the following coordinator-side implementation would allow to > use also old drivers I am inclined to return just the first result set to old clients. I think it’s fine to require a client upgrade to get multiple result sets. Possibly. I just wanted to share an idea for consideration. IMO the temp table idea might not be too hard to implement*, but sure the syntax does feel a bit bolted on. *) I'm maybe the wrong person to judge that, of course :-) henrik -- Henrik Ingo +358 40 569 7354<tel:358405697354>
