Fork me on GitHub
#sql
<
2019-05-08
>
dominicm10:05:55

I'm trying to get behavior where if I perform a query, and the result of that query changes before I commit, then my transaction fails (and I will retry). I'm doing this so I can ensure that a property on another table holds when I perform my insert (there's a maximum that can be inserted based on a counter).

dominicm11:05:05

The problem is that I don't have e.g. a balance column, I have something like "max_rows" and I'm inserting new rows into another table if the count is below a certain threshold.

Chris Reyes12:05:26

Which database are you using? I know Postgres has an AFTER UPDATE trigger you could use to check that constraint and rollback the insert. https://www.postgresql.org/docs/current/sql-createtrigger.html

dominicm12:05:35

I'm using postgres, but there is a strong chance the database will change to cockroach. Would AFTER UPDATE involve a manual rollback, rather than cancelling the transaction?

Chris Reyes12:05:26

Nope, I believe it cancels the transaction

Chris Reyes12:05:46

When I tested locally for something unrelated, when my AFTER UPDATE trigger failed, the insert was reverted

Chris Reyes13:05:04

You might want to look into the CONSTRAINT option as well

dominicm13:05:24

Looks like I'm looking for the SERIALIZE isolation level, but he drawback seems to be that anything else that may write to the same table also needs to run in a serialize transaction.

dominicm13:05:02

https://github.com/cockroachdb/cockroach/issues/28296 looks like this isn't something that would be available :)

dominicm13:05:35

realistically, nothing should ever write to this other table without running inside a serialization transaction anyway, I was just a little surprised I guess, and that made me try and find something defensive.

dominicm13:05:22

if worst comes to worse, I'll add a "count" column, which has to be changed simultaneously. That kind of constraint is easy to write. And is exactly what transactions are for. The only problem is that reverting becomes harder, but woe is me :)

the2bears17:05:15

Has anyone encountered issues with OpenJDK11 and java.sql.Timestamp?

Exception in thread "main" java.lang.ExceptionInInitializerError
    at java.base/java.lang.Class.forName0(Native Method)
    at java.base/java.lang.Class.forName(Class.java:374)
    at clojure.lang.RT.classForName(RT.java:2183)
    at clojure.lang.RT.classForName(RT.java:2192)
    at clojure.lang.RT.loadClassForName(RT.java:2211)
    at clojure.lang.RT.load(RT.java:445)
    at clojure.lang.RT.load(RT.java:421)
    at clojure.core$load$fn__7846.invoke(core.clj:6008)
    at clojure.core$load.invokeStatic(core.clj:6007)
    at clojure.core$load.doInvoke(core.clj:5991)
    at clojure.lang.RestFn.invoke(RestFn.java:408)
    at clojure.core__init.load(Unknown Source)
    at clojure.core__init.<clinit>(Unknown Source)
    at java.base/java.lang.Class.forName0(Native Method)
    at java.base/java.lang.Class.forName(Class.java:374)
    at clojure.lang.RT.classForName(RT.java:2183)
    at clojure.lang.RT.classForName(RT.java:2192)
    at clojure.lang.RT.loadClassForName(RT.java:2211)
    at clojure.lang.RT.load(RT.java:445)
    at clojure.lang.RT.load(RT.java:421)
    at clojure.lang.RT.doInit(RT.java:463)
    at clojure.lang.RT.<clinit>(RT.java:333)
    at clojure.main.<clinit>(main.java:20)
Caused by: java.lang.ClassNotFoundException: java/sql/Timestamp
    at java.base/java.lang.Class.forName0(Native Method)
    at java.base/java.lang.Class.forName(Class.java:374)
    at clojure.lang.RT.classForName(RT.java:2183)
    at clojure.lang.RT.classForNameNonLoading(RT.java:2196)
    at clojure.instant$loading__7732__auto____9257.invoke(instant.clj:9)
    at clojure.instant__init.load(Unknown Source)
    at clojure.instant__init.<clinit>(Unknown Source)
    ... 23 more

the2bears17:05:42

Tried a couple Google searches but hard combination to search on.

ghadi17:05:30

are you running a jlink'ed jvm @the2bears

the2bears17:05:07

That's a good question... what exactly is that? 😛

ghadi17:05:07

how are you launching this process that is erroring

the2bears17:05:16

I'll try to give my best "guess", as I'm looking into the error. "Offshore" has been testing compatibility with OpenJDK11. We build a Storm topology using Lein uberjar. This includes a topology submitter that submits to the Storm cluster. All run with 'java -jar...'

the2bears17:05:57

The test run was "local" so not one of our fancier machines in a managed environment.

the2bears17:05:01

Admittedly I'm just starting my investigation, and wondered if there was a similar type of thing seen.

the2bears18:05:28

I'm able to reproduce this locally, just installed OpenJDK 11.0.3 on a Mac, "lein run ..."

the2bears18:05:47

Maybe an issue with how they load modules?

seancorfield18:05:41

Which version of lein @the2bears?

seancorfield18:05:34

(this sounds very familiar as a Leiningen issue to do with bootclassloader... I'll see if I can find it via Zulip's search)

seancorfield19:05:15

Yeah, it was discussed April 30th, in the #leiningen channel.

seancorfield19:05:17

I don't see a resolution there, but that channel will be your best bet for a fix @the2bears

robertfw19:05:33

I'm running into some scenarios where clojure.java.jdbc/get-connection hangs. is there any way to give it a timeout? or advice on digging into where it is hanging? I had a look through the source and couldn't spot anything obvious

robertfw19:05:36

This is happening during some rather abusive reconnect testing

robertfw19:05:07

(We use get-connection directly as we're doing some things with postgresql event listeners that require hanging onto a specific connection handle)

seancorfield19:05:14

@robertfrederickwarner If you're not already using a connection pool, I'd strongly suggest you do so.

seancorfield19:05:22

get-connection on a regular db-spec is a pretty heavy operation. I would never use it in a production setting. I would create a pooled datasource and use that {:datasource pooled-ds} -- and call get-connection on that.

seancorfield19:05:00

And if you're already doing that, then I'd say your problem is in how your pooled datasource is configured (that can take a bit of tuning / debugging, as we found with c3p0 in production).

robertfw20:05:52

we're using c3p0 for our regular db stuff - this is a special case, we're using pgjdbc-next to use listen / notify, which requires us to open a connection, issue LISTEN {topic name} on that connection, and give the connection to the driver. it then gets notifications on that specific conn

robertfw20:05:47

we can register a callback with the driver to execute when the connection is closed, which we use to handle reconnecting

robertfw21:05:20

we have one listener for the app as a whole, and in typical operation that conn gets setup once at application startup so it being heavy isn't really an issue

robertfw21:05:32

however during testing, i can get it into a state where we get that hang when calling get-connection

seancorfield21:05:46

Part of me is not surprised by that -- but I'm pretty sure you'll find it's hanging in the PG driver since clojure.java.jdbc is a pretty thin wrapper around JDBC.

seancorfield21:05:34

But it is possible that it is hanging on either Clojure's class loading code as get-connection tries to check for the availability of the JDBC driver class... I could certainly move that out to a cache since it's really only needed on the first get-connection call.

seancorfield21:05:23

If you can verify the hang is in that Clojure class loading stuff, I'll go ahead and move that. I'll probably do that in next.jdbc anyway, now that I've thought about it.

seancorfield21:05:53

It turns out next.jdbc doesn't do this if you follow the best practice of calling get-datasource on the db-spec and then calling get-connection on that (or passing the datasource to other calls). It does call DriverManager/getLoginTimeout every time you try to get a connection, which could be optimized, but the class loading stuff is in get-datasource, not get-connection now.

robertfw21:05:45

I narrowed it down to something hanging within the pgjdbc-ng driver. I've worked around it for now by using their PGDataSource directly, which lets me set a login timeout so instead of hanging indefinitely we'll drop back into our normal retry behaviour, which seems to have done the trick

seancorfield21:05:30

You could probably put :loginTimeout NNN in the db-spec and have it take effect the same way (driver-dependent but often works).

seancorfield21:05:43

Glad you were able to isolate it to the driver. I need to encourage people more actively to start switching to next.jdbc -- but that will probably take at least a Beta release and maybe even an RC or gold release 🙂

robertfw21:05:54

Thanks for your input! Invaluable as always.

the2bears21:05:16

@seancorfield lein 2.7.1 so do for an upgrade. Thanks for the help, you've got me on the right track.

seancorfield21:05:28

@the2bears Glad to hear it. The Java 9+ and nREPL changes have played havoc with lein (and boot) users... I'm glad we switched to clj 🙂

Aleksander09:05:56

bit off-topic - but is java 9+ still an issue for leiningen?

seancorfield16:05:59

It's all fixed now in the latest version but the upgrade paths for both tools have been pretty bumpy with bugs, namespace changes, and the module stuff.

the2bears21:05:01

Wish we could too 🙂

seancorfield21:05:53

It turns out next.jdbc doesn't do this if you follow the best practice of calling get-datasource on the db-spec and then calling get-connection on that (or passing the datasource to other calls). It does call DriverManager/getLoginTimeout every time you try to get a connection, which could be optimized, but the class loading stuff is in get-datasource, not get-connection now.

seancorfield21:05:39

(it could still be cached tho' for the cases where folks try to call get-connection directly on db-spec maps)