Clojurians Log v2

@nikolavojicic Hard to be sure without a bit more detail in the form of a stacktrace -- but it looks like PG is trying to interpret (pr-str (Throwable->map some-exception)) as some sort of record literal rather than plain varchar?

user> (pst)
PSQLException ERROR: malformed record literal: "{:via [{:type java.lang.ArithmeticException, :message "Divide by zero", :at [clojure.lang.Numbers divide "Numbers.java" 188]}], :trace [[clojure.lang.Numbers divide "Numbers.java" 188] [clojure.lang.Numbers divide "Numbers.java" 3901] [rent.db_test$eval19756$fn__19757 invoke "form-init6477224877073100566.clj" 33] [rent.db_test$eval19756 invokeStatic "form-init6477224877073100566.clj" 33] [rent.db_test$eval19756 invoke "form-init6477224877073100566.clj" 32] [clojure.lang.Compiler eval "Compiler.java" 7177] [clojure.lang.Compiler eval "Compiler.java" 7132] [clojure.core$eval invokeStatic "core.clj" 3214] [clojure.core$eval invoke "core.clj" 3210] [clojure.main$repl$read_eval_print__9086$fn__9089 invoke "main.clj" 437] [clojure.main$repl$read_eval_print__9086 invoke "main.clj" 437] [clojure.main$repl$fn__9095 invoke "main.clj" 458] [clojure.main$repl invokeStatic "main.clj" 458] [clojure.main$repl doInvoke "main.clj" 368] [clojure.lang.RestFn invoke "RestFn.java" 1523] [nrepl.middleware.interruptible_eval$evaluate invokeStatic "interruptible_eval.clj" 79] [nrepl.middleware.interruptible_eval$evaluate invoke "interruptible_eval.clj" 55] [nrepl.middleware.interruptible_eval$interruptible_eval$fn__917$fn__921 invoke "interruptible_eval.clj" 142] [clojure.lang.AFn run "AFn.java" 22] [nrepl.middleware.session$session_exec$main_loop__1018$fn__1022 invoke "session.clj" 171] [nrepl.middleware.session$session_exec$main_loop__1018 invoke "session.clj" 170] [clojure.lang.AFn run "AFn.java" 22] [java.lang.Thread run "Thread.java" 834]], :cause "Divide by zero"}"
  Detail: Missing left parenthesis.
	org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2553)
	org.postgresql.core.v3.QueryExecutorImpl.processResults (QueryExecutorImpl.java:2285)
	org.postgresql.core.v3.QueryExecutorImpl.execute (QueryExecutorImpl.java:323)
	org.postgresql.jdbc.PgStatement.executeInternal (PgStatement.java:473)
	org.postgresql.jdbc.PgStatement.execute (PgStatement.java:393)
	org.postgresql.jdbc.PgPreparedStatement.executeWithFlags (PgPreparedStatement.java:164)
	org.postgresql.jdbc.PgPreparedStatement.execute (PgPreparedStatement.java:153)
	next.jdbc.result-set/stmt->result-set (result_set.clj:631)
	next.jdbc.result-set/stmt->result-set (result_set.clj:626)
	next.jdbc.result-set/eval7860/fn--7865 (result_set.clj:867)
	next.jdbc.protocols/eval6768/fn--6769/G--6757--6778 (protocols.clj:33)
	next.jdbc.result-set/eval7899/fn--7902 (result_set.clj:961)
nil

What is PG's default size for a VARCHAR?

It's not limited if the size isn't provided

Just checking. I wondered if there could have been a truncation error. If you insert that into a regular varchar field rather than your edn type, it works?

Hmm, you have db.edn as the type you define, but EDN as the column type -- could that be the problem?

It works with VARCHAR.

Or maybe PG does special processing on custom types?

Nope, same error

Will check CREATE TYPE functionality in detail later & will write here if I find something.

When you tested that you could insert into a varchar, was that still with a table called err?

My searches suggest, based on StackOverflow answers, that you get that error if you have some sort of conflict between table names and type names and PG resolves things incorrectly...

Although it seems PG throws that exception for a variety of things... 👀

Yes

@nikolavojicic One final thing you might try is to require next.jdbc.types and try wrapping the value you are inserting with (as-other ...)

That seems to be something PG requires for ENUM columns, but it may also change the behavior here...

:throwable (as-other (try ...))

See https://cljdoc.org/d/seancorfield/next.jdbc/1.1.547/api/next.jdbc.types

That throws:

Execution error (PSQLException) at org.postgresql.jdbc.PgPreparedStatement/setMap (PgPreparedStatement.java:499).
No hstore extension installed.

Which is the same error that is thrown if I pass a map to insert without an extension for IPersistentMap.

Oh, wait... setMap... OK, well, I suppose that is actually consistent. And I just realized that call as-other is going to undo your own set-parameter logic. Sorry.

At this point, I have no idea. I don't use PostgreSQL at all. Maybe someone else here who actually uses PG can point you at something helpful.

Thank you for your help.

I've found what the error is... Create EDN type:

CREATE SCHEMA XX;
CREATE TYPE XX.EDN AS (X VARCHAR);
CREATE TABLE XX.FOO(BAR XX.EDN);

INSERT INTO XX.FOO (BAR) VALUES ('123');
----------------------------------------
ERROR:  malformed record literal: "123"
LINE 1: INSERT INTO XX.FOO (BAR) VALUES ('123');
                                         ^
DETAIL:  Missing left parenthesis.
SQL state: 22P02
Character: 34

INSERT INTO XX.FOO (BAR) VALUES (ROW('123'));

goodmorning did anyone work with redshift + clojure.java.jdbc? I am seeing ERROR:1023 DETAIL: Serializable isolation violation on table in Redshift for select query (als there are insert/update staff happened in another transaction) does select made in a transaction (by default)? can it conflict with insert/update? please advice how to deal with it? (it is recommended to lock the table, but seems a bit too much) thanks! ---- UPD: maybe autocommit triggers transactional behaviour for select?

Using clojure.java.jdbc, is there any mechanism for namespacing keys automatically? We have a manual solution currently, but I suspected this might be built in (I know it is in next.jdbc, but we're not ready for that right now)

it sees to work with honeysql, thus I assume it is possible

This is for the return, rather than the query.

ah, right

Are the maps you get back namespaced?

it is possible to have them namespaced

defn query
  "Given a database connection and a vector containing SQL and optional parameters,
  perform a simple database query. The options specify how to construct the result
  set (and are also passed to prepare-statement as needed):
    :as-arrays? - return the results as a set of arrays, default false.
    :identifiers - applied to each column name in the result set, default lower-case
    :keywordize? - defaults to true, can be false to opt-out of converting
        identifiers to keywords
    :qualifier - optionally provides the namespace qualifier for identifiers
    :result-set-fn - applied to the entire result set, default doall / vec
        if :as-arrays? true, :result-set-fn will default to vec
        if :as-arrays? false, :result-set-fn will default to doall
    :row-fn - applied to each row as the result set is constructed, default identity
  The second argument is a vector containing a SQL string or PreparedStatement, followed
  by any parameters it needs.
  See also prepare-statement for additional options."

:qualifier - optionally provides the namespace qualifier for identifiers

So it does, even for inserts. Wonderful. I didn't look at query, oops!

i'm using clojure/java.jdbc and unable to get "saved" values from my "insert" queries using MYSQL. both insert! and execute! with {:return-keys ["id"]} return nil. i'd like to insert auto_incremented rows and get the evaluated id's back. i'm familiar with mysql last_query_id query (can query all new id's after my existing id) but maybe there's something else?

this clojure.jdbc? https://github.com/funcool/clojure.jdbc

nopes. https://github.com/clojure/java.jdbc *

OK - I had to ask because that lib (last touched three years ago) chose a confusing name :/

so what you think 😄

I'll have to leave that for the jdbc experts, sorry, but I suspect they will lead you to next.jdbc(?)

Hello. I am trying to figure out how to insert enum values in PostgreSQL with next.jdbc and honeysql. Everything works fine when I use plain next.jdbc with calling as-other as described here: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md#working-with-enumerated-types But I am not able to insert enum with honeysql

(-> (h/insert-into :table)
    (h/values (map #(update :enum-field (comp jdbc-types/as-other name)) records))
    (hsql/format))

this code throws exception java.lang.AssertionError Assert failed: Alias should have two parts

yea, in mew projects I do use next, but this one is with the old java jdbc

Thanks for reporting back. Interesting.

In clojure.java.jdbc most operations happen in an automatically added transaction unless you explicitly opt out of that behavior. In next.jdbc I no longer do that (because it could cause problems for some DBs).

If you want to ask that in #honeysql I'll take a look. The error is not coming from next.jdbc.

^ @michal.kurtak

sorry i’ve not been aware of #honeysql channel. I’ve copied my question there. thank you

thanks! how i can opt out transaction when doing just query(eg select)? setting autocommit false is not enough?

Looking at the source of clojure.java.jdbc, a query should not even try to use a transaction -- it just calls .executeQuery. I'd have to see a bit more of your code to help any further.

NP. It's mostly about volume. #sql is higher traffic and I'm just less likely to see Qs about HoneySQL here so I try to encourage folks over to #honeysql so it's more likely HoneySQL users can help.

thanks a lot! sorry, about to go to bed now - will post tomorrow