Fork me on GitHub
#sql
<
2021-09-10
>
snorremd12:09:44

What would people prefer in their code base (if using next.jdbc with postgres) for dealing with aggregated records? 1. jsonb_agg to create lists of JSONB pgobjects for each record in the result sets, and then decoding JSON and converting strings to correct types 2. Leave result set flat with duplicates, but get correct types for all columns, and then use partition-by on the sorted column to aggregate the values in Clojure code

isak16:09:47

1, because then it is easier to extend to work with additional aggregation. E.g., doing what you are doing, except for more than one project/customer/etc. But it depends how complex the json conversion is. Usually trivial IME, but maybe it is different in your app.

👍 2
vemv23:09:55

I'm noticing https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setTransactionIsolation(int) is flaky, whether one invokes it directly via interop or via next.jdbc (e.g. {:isolation :read-committed}). Via interop, sometimes I set it but it's not visible until I wait some time, or retry setting it a few times. Via next.jdbc, I can perceive org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction. I don't get these so easily, but still frequently enough to fail a CI build. So I cannot rely on this feature for production so far. Does any of this ring a bell?

vemv23:09:53

I'm using postgres 12. It's dockerized. The JVM resides outside docker.

vemv23:09:52

maybe it has to do with the fact that connections are pooled :thinking_face: via Hikari

hiredman00:09:53

You are not getting the same connection object from the pool every time

hiredman00:09:39

If you set the tx level you need to hang on to and use the same connection and commit before releasing it to the pool

vemv13:09:13

Yeah sounds like a good lead however it might not explain everything. Particularly Cannot change transaction isolation level in the middle of a transaction which arises at the beginning of a tx. Will try a few things

hiredman18:09:05

It does explain it, a connection with a previously started transaction was returned to the pool, then it was taken back out, and then you tried to start a transaction with it

vemv20:09:51

how would that be even a remotely clean api? I'd expect {:isolation :read-committed} to have a limited extent, e.g. any effect is undone by the end of the tx in all cases

vemv20:09:04

anyway I'm not interested in who's wrong

hiredman21:09:24

The transaction level is basically a flag set on the connection, so you need to be using the same connection for a transaction

hiredman21:09:37

In general clojure wrappers will set the flag, run your code, then commit or rollback

hiredman21:09:02

If you are using interop directly and not keeping the same connection object for your entire transaction, you are returning connections with that flag set to the connection pool, where they will be handed back to some other called asking for a connection

pesterhazy10:02:59

Recently my coworkers ran into exactly this issue this week • Same PSQLException "Cannot change transaction isolation level in the middle of a transaction" • Happens in prod every couple of days but when it happens it does so in bursts of a 100 exceptions or so (causing an alert) • From what we can tell, we're always using with-db-transaction correctly (which should revert back the isolation level in a finally clause). We're never executing "set transaction isolation level repeatable read" manually Some more data on our side • We're using an older version of the conman library, which brings in (an older version of) hikari-cp. We're also using [org.clojure/java.jdbc "0.7.10"] (but conman seems to pull in next.jdbc as well?) Your description really helped us understand the issue better @hiredman, although we haven't been able to pin down which component is misbehaving (clojure.java.jdbc, connection pool, clojure wrappers, our code). Any help appreciated Were you able to fix the issue @vemv?

pesterhazy11:02:30

Clearly a "dirty" connection (i.e. one with an ongoing transaction) gets added back to the pool and causes problems. But the question is, how is this possible if with-db-connection always cleans up after itself?

vemv11:02:23

IIRC one has to set the isolation level twice: once at Hikari level, then again at clojure jdbc lib level. YMMV, my intent was to explicitly enforce a certain isolation level. Also I think I was using next.jdbc.

pesterhazy11:02:29

Hm, we want to use different isolation levels depending on the request type

vemv11:02:37

Perhaps it would be a good idea to have different pools, with different isolation levels each Might not be optimal in terms of consumed resources, but assuming it fixes the exceptions, it gives you a good baseline to iterate on

💡 1
pesterhazy12:02:51

Good idea. Although I'm wondering if that isn't papering over a real problem, given that dirty connections with open transactions are being reused

seancorfield18:02:28

@U06F82LES Perhaps it might be worth adding custom checkin/checkout testers for your connection pool so you could report on connections going back into the pool, or coming out of the pool, that are not in their expected state, viz a viz transaction status?

seancorfield18:02:41

If anyone can create a small repro of this -- for either c.j.j or next.jdbc -- I'd be very interested in digging in to see if it's a bug in either library or something more subtle.

pesterhazy22:02:10

I didn't know about custom checkin tester, that sounds useful. Couldn't find anything in hikaricp but will take another look

pesterhazy22:02:00

Haven't been able to repro yet. It happens intermittently

pesterhazy22:02:48

How could a dirty connection be returned to the pool? Maybe due to some edge case, like a statement killed because of a timeout. That's just speculation though

hiredman22:02:21

it may also be a symptom of what is effectively "use after free", continuing to use a connection object after it has been returned to the pool

hiredman22:02:23

I would look very closely at anything multithreaded, for example (with-db-connection [con ...] (future something-with-con))

pesterhazy22:02:00

@hiredman can you read my mind?

pesterhazy22:02:17

I was just looking at code that does something like that

pesterhazy22:02:18

A dynamic var *db* is conveyed to the background thread by future, and it's possible that this thread hangs on to the connection

hiredman22:02:44

it is generally a really bad idea to do things like that, you can't really safely share connections or transactions between threads. a hikaricp connection has a bunch of mutable fields, no locks, the fields aren't even volatile

pesterhazy22:02:51

Yeah. Dynamic vars can be a real footgun (at least when used with mutable state)

pesterhazy22:02:01

It's easy to make a mistake because if *db* refers to a datasource, it's AFAIK fine to share it with a background thread; but if it refers to a connection (or a connection pool ProxyConnection), that shouldn't be shared with other threads.

✔️ 1
pesterhazy22:02:24

Clojure's jdbc libs hide the distinction between data source and connection because query can accept either one

hiredman22:02:29

there is also a thing where if you are mixing binding-conveying functions and non-binding-conveying functions on the same threads, the non-binding-conveying functions may see the bindings of whatever the last binding-converying function to run was https://ask.clojure.org/index.php/11533/should-dynamic-bindings-persist-across-thread-pool-uses

pesterhazy22:02:42

> If that same thread is subsequently re-used for another purpose, the dynamic bindings remain in place from the previous use of the thread. That sounds pretty scary

pesterhazy09:02:18

We were able to reproduce the problem and get the exception by repeatedly calling (conman/with-transaction [db-conn {:isolation :repeatable-read}] (println (query "select 1")) (future (println (query "select 2"))))

pesterhazy09:02:11

So your hypothesis is getting more and more likely @hiredman

pesterhazy10:02:59

Recently my coworkers ran into exactly this issue this week • Same PSQLException "Cannot change transaction isolation level in the middle of a transaction" • Happens in prod every couple of days but when it happens it does so in bursts of a 100 exceptions or so (causing an alert) • From what we can tell, we're always using with-db-transaction correctly (which should revert back the isolation level in a finally clause). We're never executing "set transaction isolation level repeatable read" manually Some more data on our side • We're using an older version of the conman library, which brings in (an older version of) hikari-cp. We're also using [org.clojure/java.jdbc "0.7.10"] (but conman seems to pull in next.jdbc as well?) Your description really helped us understand the issue better @hiredman, although we haven't been able to pin down which component is misbehaving (clojure.java.jdbc, connection pool, clojure wrappers, our code). Any help appreciated Were you able to fix the issue @vemv?