Clojurians Log v2

How do I set a Postgres type when inserting? I have some field that uses a type, e.g., CREATE TYPE language AS ENUM ('en', 'fr', 'es'); … the field is defined as lang language DEFAULT 'en', it asked me to cast the expression when using insert! and insert-multi!: > ERROR: column “lang” is of type language but expression is of type character varying > Hint: You will need to rewrite or cast the expression.

@somedude314 No idea what PG requires. In MySQL enum columns can have strings inserted into them and the JDBC driver handles the conversion automatically.

If you use execute! for the insert, you will be able to specify cast expressions which may be your best bet with PostgreSQL...?

☝️ Can confirm cast expressions work with postgres. That's what I use.

I am not willing to give up the friendly functions, they are very clean and convenient, I’d rather turn the fields into varchar… I had it working with hugsql though by appending ::lang to the placeholder. How does it look in MySQL when the column name is a keyword?

@somedude314 Not sure what you mean re: MySQL? If I have some_col ENUM('blue','red','green') in MySQL, I can insert {:some_col "red"} and it just works. If you try to insert an invalid string, you get a value too big for column exception (something like that).

If you use HoneySQL to generate the insert SQL (and then use execute! to run it), I think you can get casts into the inserted values reasonably cleanly.

I see. I thought I could pass ::uuid or ::language after the value somehow. I will drop the enum usage for now.

Bear in mind that ::uuid might look like a keyword in Clojure but it's PostgreSQL-specific syntax in SQL (and ::uuid would auto-resolve to :current.namespace/uuid anyway).

I think in standard SQL, you'd say CAST( 'fr' AS language ) ?

@somedude314 it looks like PostgreSQL would be happy with .setString() to assign a string version of the enum to the column. You can make that happen by providing a metadata implementation of SettableParameter as a wrapper around your string value.

I believe you could handle that with a function like this:

(defn enum [s] (with-meta [s] {`next.jdbc.prepare/set-parameter (fn [[v] ^java.sql.PreparedStatement s ^long i] (.setString s i v))}))

I believe so, SELECT CAST( 'student' AS user_role ) from "user" works for me

(I'm not at my main machine so I can't verify that -- I will try tomorrow)

Only wrapping with (enum) gives me: > Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2440). > ERROR: column “lang” is of type language but expression is of type character varying > Hint: You will need to rewrite or cast the expression. > Position: 93

Ah, that sucks.

The JDBC PG docs seemed to suggesting that would work. I'll try to find some time tomorrow to do some more research -- it's my day off so I won't be working.

Strange that it's the same error as you got originally tho'...

I appreciate it. If it won't be doable with insert! I will be converting the fields and managing the types on the application level. I won't be leaving the insert! I'd die on that hill lol. The little functions cleaned my code so much

I wanted to save a couple of million rows to a JSON, in the end I just made a lazy seq that did batched queries to the db.

So basically pagination with offsets and limits.

Hmm, so streaming rows into a JSON file? You could do that with reducing over plan by appending the JSON of each row to a file, with trailing , added I guess? (and a leading [ and trailing ])

@somedude314 Figured it out! This works

(defn- enum [s]
  (with-meta [s]
    {`prep/set-parameter
     (fn [[v] ^java.sql.PreparedStatement s ^long i]
       (.setObject s i v java.sql.Types/OTHER))}))

(deftest enum-pg
  (when (postgres?)
    (let [r (sql/insert! (ds) :lang_test {:lang (enum "fr")})]
      (println 'enum-pg r))))

So I'm probably going to add some helpers, based on java.sql.Types, that allow easy translation on a per-object basis.

That's pretty fantastic!

Hmmm, from that SO answer I thought that reduce is eager: https://stackoverflow.com/questions/58068904/how-to-adapt-the-ireduceinit-from-next-jdbc-to-stream-json-using-cheshire-to-a-h

Hmm, it’s not working for me. I get this error on insert! and insert-multi!: Error: Execution error (PSQLException) at org.postgresql.jdbc.PgPreparedStatement/setObject (PgPreparedStatement.java:949). Can't infer the SQL type to use for an instance of clojure.lang.PersistentVector. Use setObject() with an explicit Types value to specify the type to use. -- (enum "fr") => ["fr"] (type (enum "fr")) => clojure.lang.PersistentVector postgres --version postgres (PostgreSQL) 12.3 Am I supposed to be passing some special options?

@somedude314 make sure you exactly copy what I pasted -- in particular the first arg to the anonymous fn is a destructured [v]

The above code comes from the test suite, so it definitely works 😁

Reduce is eager. The "lazy" part refers to how the rows are read from the database

So the bottleneck for me is in ram, I didn't want to have the whole result set in memory.

[~/Desktop] ⤑ lein repl nREPL server started on port 51669 on host 127.0.0.1 - <nrepl://127.0.0.1:51669> REPL-y 0.4.4, nREPL 0.6.0 Clojure 1.10.0 Java HotSpot(TM) 64-Bit Server VM 11.0.7+8-LTS Docs: (doc function-name-here) (find-doc "part-of-name-here") Source: (source function-name-here) Javadoc: (javadoc java-object-or-class-here) Exit: Control+D or (exit) or (quit) Results: Stored in vars *1, *2, *3, an exception in *e user=&gt; (defn- enum [s] #_=&gt; (with-meta [s] #_=&gt; {prep/set-parameter` #_=> (fn [[v] ^java.sql.PreparedStatement s ^long i] #_=> (.setObject s i v java.sql.Types/OTHER))})) #'user/enum user=> (enum "fr") ["fr"] user=> (type (enum "fr")) clojure.lang.PersistentVector

It’s outputting a plain vector

It's a vector with metadata. That's correct.

You haven't required next.jdbc.prepare :as prep -- required for the `prep/set-parameter symbol to resolve correctly.

or you could use 'next.jdbc.prepare/set-parameter as the metadata key.

BTW, you can use triple backticks around blocks of code to make them more readable.

like this
block

Right, so you must "lazily stream" the result sets from the database, but you can eagerly reduce them.

Getting the driver to lazily stream results is database-dependent -- some suggestions are given in the next.jdbc docs, on the Tips & Tricks page as I recall.

Ah, that's great, I'll take a look, thank you!

Got it working now with that! Thanks.

Cool. In the next version of next.jdbc (probably later today), there will be several built-in helpers to make this a lot easier.

Just released 1.0.478 with next.jdbc.types/as-other so you no longer need to define that enum function!

See usage in the test for inserting a string into a PostgreSQL enum https://github.com/seancorfield/next-jdbc/blob/develop/test/next/jdbc/sql_test.clj#L162-L165 @somedude314

(these as-xxx functions are auto-generated from java.sql.Types via Java Reflection when the next.jdbc.types namespace is required so it's opt-in)

(also, develop has been merged into nested-tx and multi-rs in case anyone is testing against those features and wants the latest released features too)

awesome sauce!

Multiple result set support will probably get merged to develop and released later today. I just have to write up all the documentation around it.

Belay that... I still haven't figured out how multiple result sets should work with plan...

It all works for execute! (but not for execute-one! since that is specifically designed to return one row of one result set).

Amazing, thank you. Working like a charm. I am still defining enum in my simple db wrapper to avoid importing the types all over the place :)

(def enum types/as-other)

I'm going back and forth on how multiple result sets should work with plan but I'm thinking the most intuitive approach is to reduce over each result set in sequence and still conj the results into a vector.

But some databases return an update count as well as actual result sets (at least, based on the stored procs I've created for testing), and some don't (and HSQLDB and MySQL return the update count in different places, just to make this more fun).

Anyone reducing over plan with multiple result sets has got to know what they're doing. Is it common to have different types of result sets coming back, so a single reducing function could not be used?

(perhaps this is too niche of a concern to even warrant spending cycles on it at all? How many people actually use multiple result sets in the first place?)

If you're using stored procs already, I suspect you'd lean toward having the proc compute "everything" and just get fairly simple result sets back -- so maybe execute! is sufficient? But what about large result sets or other in-memory computation you might need to account for?

I guess you could just have a multi-method as the reducing function? Or a function that did stuff conditionally depending on what columns the row has in it? Because of the MySQL/HSQLDB results, you'd already need to look for :next.jdbc/update-count to spot an update count "result set" instead of a "real" one...

Not a really a bug but a funny behaviour in next.jdbc: (sql/query datasource [(str "SELECT * FROM \"user\"")]) gets me the users from my user table (sql/query datasource ["SELECT * FROM user"]) gets me Postgres users from its user table

same behavior from psql

kevin=# select * from user;
 user  
-------
 kevin
(1 row)

kevin=# select * from "user";
--
(0 rows)

kevin=# 

user isn't actually postgres's table of users

user is, maybe a session variable? that always contains the current user name

kevin=# select 'kevin' = user;
 ?column? 
----------
 t
(1 row)

kevin=# 

Ah I see. Maybe I should have used plural forms for my table names. Singular names seem more correct but not sure it the troubles are worth it

my postgres won't let you create a table named user unless you quote it too

but if you used the ddl stuff to generate your create table commands it might just quote everything