Fork me on GitHub
#sql
<
2020-09-08
>
seancorfield00:09:12

@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?

nikolavojicic00:09:56

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

seancorfield00:09:20

What is PG's default size for a VARCHAR?

nikolavojicic00:09:20

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

seancorfield00:09:06

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?

nikolavojicic00:09:24

It works with VARCHAR.

seancorfield00:09:49

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

nikolavojicic00:09:31

Nope, same error

seancorfield00:09:51

Or maybe PG does special processing on custom types?

nikolavojicic00:09:05

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

seancorfield00:09:42

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

seancorfield00:09:40

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...

seancorfield00:09:34

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

seancorfield01:09:25

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

nikolavojicic01:09:14

That throws:

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

nikolavojicic01:09:48

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

seancorfield01:09:18

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.

seancorfield01:09:55

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

seancorfield01:09:16

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

seancorfield01:09:12

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.

nikolavojicic01:09:28

Thank you for your help.

nikolavojicic09:09:59

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);
This fails:
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
Works when wrapped with ROW(...):
INSERT INTO XX.FOO (BAR) VALUES (ROW('123'));

seancorfield17:09:11

Thanks for reporting back. Interesting.

kirill.salykin09:09:57

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?

seancorfield17:09:39

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).

kirill.salykin17:09:13

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

seancorfield18:09:49

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.

kirill.salykin18:09:01

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

kirill.salykin09:09:07

It looks like this:

(defn query
  "Runs a query."
  ([db sql-params]
   (jdbc/with-db-connection [conn db]
     (.setAutoCommit (:connection conn) false)
     (jdbc/query conn sql-params {:identifiers format-column-name}))))

seancorfield16:09:55

Hmm, that shouldn't cause a transaction either. Are you calling this from inside other code that does set up a transaction?

kirill.salykin16:09:27

nope, no explicit transaction started

seancorfield16:09:36

Then I've really no idea, sorry.

kirill.salykin16:09:53

no problem, thanks!

dominicm10:09:17

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)

kirill.salykin10:09:00

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

dominicm10:09:33

This is for the return, rather than the query.

dominicm10:09:47

Are the maps you get back namespaced?

kirill.salykin10:09:06

it is possible to have them namespaced

kirill.salykin10:09:38

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."

kirill.salykin10:09:46

:qualifier - optionally provides the namespace qualifier for identifiers

dominicm10:09:36

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

Aviv Kotek12:09:33

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?

seancorfield17:09:32

Try :return-keys true -- that definitely works, because that's what we use at work -- and then you'll get back a map with :generated_key (for next.jdbc, that's :GENERATED_KEY for MySQL by default). If that still doesn't work, maybe it's an issue with your JDBC driver/version and/or the database version itself?

Aviv Kotek18:09:21

it seems that if you insert multiple rows via execute! - only the first id is returned. while insert-multi! will return a sequence of returned-keys (what I wanted). although looking https://github.com/clojure/java.jdbc/blob/master/src/test/clojure/clojure/java/jdbc_test.clj#L958 it should work well, i'm looking to do an insert-only-if-not-exists and grab the returned id's. so insert-multi! will force me to pre-look on every new inserted item

seancorfield19:09:16

Per that test, you need to specify :multi? true to get back multiple generated keys -- and structure your parameter values slightly differently (because you need to specify groups of values, not just a sequence of values).

Aviv Kotek15:09:32

yep - works well. 🙏

3
noisesmith12:09:10

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

Aviv Kotek13:09:52

so what you think 😄

noisesmith13:09:43

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

Aviv Kotek14:09:43

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

mkurtak13:09:42

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

seancorfield17:09:48

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

mkurtak17:09:04

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

seancorfield18:09:52

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.

mkurtak13:09:51

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

mkurtak13:09:43

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

seancorfield17:09:32

Try :return-keys true -- that definitely works, because that's what we use at work -- and then you'll get back a map with :generated_key (for next.jdbc, that's :GENERATED_KEY for MySQL by default). If that still doesn't work, maybe it's an issue with your JDBC driver/version and/or the database version itself?