Hmm…Looks like I either need to write a pg function, or re-express the transaction as a CTE https://stackoverflow.com/questions/12463846/get-values-from-returning-within-a-transaction

I would take several steps back

I believe your example up above would not be valid with most jdbc drivers

Wow — why not?

what they take to execute is a single sql statement

something ending in ';'

and you don't need to include the ';' because they only execute a single statement at a time

Ah, that’s interesting! I knew about jdbc/with-transaction, etc, but I didn’t know that I had to send one statement at a time.

begin/commit is for running transactions, which there are existing tools for like with-transaction

I wanted to write it in one go, to avoid the following kind of bug: Clojure sends the request “BEGIN;” Some stuff happens, then clojure crashes Now, postgres would be hung up on this transaction, forever waiting on “COMMIT;”

I could be wrong, but I used to work with someone who wrote a migration library, and he wanted to express migrations as .sql files potentially containing multiple bits of sql, and he had a devil of a time splitting the sql in such a way as to be able to feed it to a jdbc driver

I am not super familiar with postgres internals, but I believe if the client's connection dies that aborts any outstanding transactions for that connection

Okay, I think you may be right! Thank you @U0NCTKEV8. I’ll do some more research on transactions in jdbc

Perhaps the best approach would be to create one giant CTE. At least this way there won’t be too many round trips

we used that migration lib at previous job. and comments could throw off the whole thing when it no longer could figure out the boundaries between statements. migratus?

yep

@U0C5DE6RK Use with-transaction instead of trying to write your own in SQL 🙂

Learned something new today, thanks team! So, my mental model right now: • postgres processes one statement at time • This means that for a transaction, there are multiple round trips. There’s a small chance that a gc pause or something could lock up the transaction for a bit. But crashes mean transactions are abandoned • Given this, if I can express a transaction as a CTE, it’s a better idea to do that, as it means one round trip. Does this sound right?

It's up to the driver whether there actually are multiple round-trips or not -- and that's really not relevant in the real world.

I mean, obviously you don't want a transaction to span a "long" period of time, but unless you have a pretty insane level of DB ops, a little GC pause is unlucky to have much impact.

Gotcha, makes sense! Thank you Sean.

Like a lot of things, the "simple, obvious" approach will just work up to a fairly reasonable scale. It's better to do things the right/idiomatic way in general unless you have a very specific situation that needs a "hack".

You indicated that you hadn't encountered with-transaction before today(?) so I will ask: is there something I can improve in the next.jdbc docs that would have made that more visible or obvious @U0C5DE6RK?

@U04V70XH6 I love the next.jdbc docs and have seen + used with-transaction before. Here my confusion was more about how sending statements to postgres worked. My mental model was something like: • with-transaction is great if I also want to access intermediate results inside clojure • But if I want to do a fast transaction, I should use begin commit in one string, as that only means one round trip Turns out the second bullet was mistaken, and it’s not appreciably faster.

Ah, interesting. I have to be honest: that is not a line of thinking that would have occurred to me!😁

😆 — sometimes I build a whole world in my head, and forget check if the setting for gravity was correct. Thanks for the help team.