2025-09-02 sql | Clojure Slack Archive

sql

jussi 2025-09-02T12:16:47.162159Z

Is it possible to get next-jdbc to return a Postgres BYTEA type as a string when I'm 100% certain it contains a string?

p-himik 2025-09-02T12:19:41.669989Z

Just to confirm - you do not want to do it at the query level via an explicit cast?

jussi 2025-09-02T12:21:47.625369Z

whatever works, I'm just thinking if there is something closer to the source since I get the contents nicely to clojure (it prints like #object[[B 0x7320137b [B@7320137b]) and wondering if my existing tooling can handle it so that I don't have to 😅

jussi 2025-09-02T12:23:03.345659Z

if I read that type correctly, it seems to be an array of byte arrays?

jussi 2025-09-02T12:26:36.052339Z

Ah. forces himself to look at the mirror, since PEBKAC The contents of that particular BYTEA is encrypted, the decrypting cannot happen on query level.

jussi 2025-09-02T12:26:46.653609Z

I have to deal with that object in clojure

jussi 2025-09-02T12:27:05.732519Z

Thank you for being my rubber duck...

p-himik 2025-09-02T12:27:30.864029Z

Ah, well, anyway - since I've already typed all that. :D There are three solutions I can think of: • At the DB level, via a view • At the next.jdbc level via a result builder (or whatever it's called, I forgot) • At the query level The first is as close to the source as it gets, and it remains with the source - it will work the same even if you use something other than your app to work with the data. If it's tolerable, you could maybe even change the column type and be done with it. But a no-go if you can't alter the schema. The second is just as implicit and a bit more removed from the source. Better than the previous one only if you can't change the schema. The third one is the most explicit solution, no chance of screwing something up because of some assumptions you yourself or someone else might have later on when working with the same code and not realizing that String in Clojure suddenly becomes BYTEA in the DB.

p-himik 2025-09-02T12:28:36.947359Z

Technically, the decryption could happen at the next.jdbc level if you provide a custom result builder at that particular call site.

🙏🏻 1

jussi 2025-09-02T12:30:41.870849Z

Thank you! Anyway 😅

2025-09-02T16:44:31.634479Z

I'm getting a transaction error and I'm unsure whether it looks like a next.jdbc bug, a redshift driver bug, or user error:

(require '[next.jdbc :as njdbc])
  (def ds
    {:dbtype "redshift"
     ;; These values come from the app's config:
     :dbname database
     :user user
     :password password
     :host host
     :port port})
  (njdbc/with-transaction [conn ds]
    (njdbc/execute! conn ["select 1"])
    (njdbc/execute! conn ["select 2"]))

This succeeds 1 out of every 10 times or so. Typically I get an exception:

1. Unhandled com.amazon.redshift.util.RedshiftException
   ERROR: current transaction is aborted, commands ignored until end
   of transaction block
    QueryExecutorImpl.java: 2689  com.amazon.redshift.core.v3.QueryExecutorImpl/receiveErrorResponse
    QueryExecutorImpl.java: 2302  com.amazon.redshift.core.v3.QueryExecutorImpl/processResultsOnThread
    QueryExecutorImpl.java: 1885  com.amazon.redshift.core.v3.QueryExecutorImpl/processResults
    QueryExecutorImpl.java: 1877  com.amazon.redshift.core.v3.QueryExecutorImpl/processResults
    QueryExecutorImpl.java:  374  com.amazon.redshift.core.v3.QueryExecutorImpl/execute
RedshiftStatementImpl.java:  521  com.amazon.redshift.jdbc.RedshiftStatementImpl/executeInternal
RedshiftStatementImpl.java:  442  com.amazon.redshift.jdbc.RedshiftStatementImpl/execute
RedshiftPreparedStatement.java:  202  com.amazon.redshift.jdbc.RedshiftPreparedStatement/executeWithFlags
RedshiftPreparedStatement.java:  186  com.amazon.redshift.jdbc.RedshiftPreparedStatement/execute
            result_set.clj:  674  next.jdbc.result-set/stmt->result-set
            result_set.clj:  669  next.jdbc.result-set/stmt->result-set
            result_set.clj:  902  next.jdbc.result-set/eval34284/fn
             protocols.clj:   34  next.jdbc.protocols/eval30533/fn/G
                  jdbc.clj:  268  next.jdbc/execute!
                  jdbc.clj:  254  next.jdbc/execute!
                      REPL: 1183  kc.event-consumer.goals.common/eval41618/fn
           transaction.clj:   72  next.jdbc.transaction/transact*
           transaction.clj:   51  next.jdbc.transaction/transact*
           transaction.clj:  152  next.jdbc.transaction/eval34481/fn
             protocols.clj:   58  next.jdbc.protocols/eval30623/fn/G
           transaction.clj:  155  next.jdbc.transaction/eval34485/fn
             protocols.clj:   58  next.jdbc.protocols/eval30623/fn/G
                  jdbc.clj:  433  next.jdbc/transact
                  jdbc.clj:  425  next.jdbc/transact
                      REPL: 1181  kc.event-consumer.goals.common/eval41618

Commenting out either of the queries makes the other succeed. Apparently the transaction is getting aborted after the first query but idk why. Any suggestions?

seancorfield 2025-09-02T16:52:15.734839Z

Can you test it against any other DBs? I strongly suspect it's a peculiarity of the RedShift driver -- but I don't have a RS DB to test against, and as far as I know that code should work fine.

2025-09-02T17:58:46.275369Z

Will do

2025-09-02T18:23:06.081499Z

Confirmed next.jdbc is blameless. No issue w/ postgresql, but I get the same exception w/ funcool.jdbc and Redshift.

👍🏻 1

seancorfield 2025-09-02T19:04:44.196919Z

Appreciated, @tomc!

Clojurians Log v2

sql