No longer able to write a UUID to Postgres with ClojureQL

[Don Jackson]

For those of you following along at home, my spate of bad luck continues….

A week or so ago, I was happily writing and reading UUIDs into Postgres with ClojureQL.

Then, all of a sudden, it stopped working, and I can't figure out why.

Shame on me for not being more careful about documenting when it worked, and what changed.  

And a lot of stuff changed in the interim…

So I wrote small example project to demonstrate the problem:

I have verified that I can write UUIDs into Postgres with java.jdbc itself, so that is evidence that the issue is not with java.jdbc, the Postgres JDBC library, nor my Postgres server.

(def u1 (. UUID (randomUUID)))

(def u2 (. UUID (randomUUID)))

(defn write-uuid-jdbc

  [uid name]

  (sql/with-connection postgres-db

    (sql/insert-values

     :testuuid

     [:uid :name]

     [uid name])))

(defn read-table-clojureql

  []

  @(table postgres-db :testuuid))

(defn write-uuid-clojureql

  [uid name]

  (conj! (table postgres-db :testuuid)

             {:uid uid :name name}))

This illustrates the problem:

(write-uuid-jdbc u1 "jdbc") 

works.

(write-uuid-clojureql u2 "clojureql") 

throws the following exception:

Bad value for type int : d812274a-a1ff-4ce5-962e-005f3c893459

  [Thrown class org.postgresql.util.PSQLException]

Restarts:

 0: [QUIT] Quit to the SLIME top level

Backtrace:

  0: org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)

  1: org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)

Now write u2 with jdbc:

(write-uuid-jdbc u2 "jdbc")

And read the entire table back out using ClojureQL:

(read-table-clojureql)

({:name "jdbc", :uid #<UUID 2896de2b-2c48-40ab-83a6-cee6c2be16cd>} 

 {:name "jdbc", :uid #<UUID d812274a-a1ff-4ce5-962e-005f3c893459>})

So ClojureQL can certainly read the table, and return the UUIDs, but can't write them…

Here is the relevant part of project.clj:

  :dependencies [[org.clojure/clojure "1.2.1"]
  		 [postgresql/postgresql "9.1-901.jdbc4"]
	         [org.clojure/java.jdbc "0.1.1"]
		 [clojureql "1.1.0-SNAPSHOT"]
		 ])

I woud definitely appreciate/welcome any suggestions about why this is happening, or how it might be fixed….

I've pushed this example test case/project up to GitHub in case anyone wants to poke around:

dcj/postgres-uuid-test 

Don

[Sean Corfield]

If it helps someone debug this for Don (since he and I discussed this off-list):

The stack trace from ClojureQL originates in its function to return
generated keys and it's calling .getInt on the (generated) key which
fails because it's a UUID.

So the question is probably: why is ClojureQL assuming all generated
keys are integers?

As for what might have changed in Don's setup, I wondered if the :uid
column definition had been changed to indicate (to ClojureQL) that it
is a potentially generated key?

(is there a ClojureQL mailing list?)

Sean

[Herwig Hochleitner]

2011/12/6 Sean Corfield <seanco...@gmail.com>:

> If it helps someone debug this for Don (since he and I discussed this off-list):
>
> The stack trace from ClojureQL originates in its function to return
> generated keys and it's calling .getInt on the (generated) key which
> fails because it's a UUID.
>
> So the question is probably: why is ClojureQL assuming all generated
> keys are integers?

It shouldn't, and doesn't now:
https://github.com/LauJensen/clojureql/commit/f7ffe88b166e6f60eccb3b4f46b7db5d69dbcc64
Thanks for tracing this and taking the time to post it.

> As for what might have changed in Don's setup, I wondered if the :uid
> column definition had been changed to indicate (to ClojureQL) that it
> is a potentially generated key?

CQL doesn't distinguish generated and non-generated. It just exposes
the notion, JDBC provides of that.

> (is there a ClojureQL mailing list?)

Not that I'm aware of, currently.

kind regards
--
__________________________________________________________________
Herwig Hochleitner

[Sean Corfield]

On Tue, Dec 6, 2011 at 6:12 AM, Herwig Hochleitner
<hhochl...@gmail.com> wrote:
> 2011/12/6 Sean Corfield <seanco...@gmail.com>:

>> So the question is probably: why is ClojureQL assuming all generated
>> keys are integers?
> It shouldn't, and doesn't now:
> https://github.com/LauJensen/clojureql/commit/f7ffe88b166e6f60eccb3b4f46b7db5d69dbcc64
> Thanks for tracing this and taking the time to post it.

That looks like the fix Don needs! Thanx for the swift response.
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Don Jackson]

On Dec 6, 2011, at 11:23 AM, Sean Corfield wrote:

> On Tue, Dec 6, 2011 at 6:12 AM, Herwig Hochleitner
> <hhochl...@gmail.com> wrote:
>> 2011/12/6 Sean Corfield <seanco...@gmail.com>:
>>> So the question is probably: why is ClojureQL assuming all generated
>>> keys are integers?
>> It shouldn't, and doesn't now:
>> https://github.com/LauJensen/clojureql/commit/f7ffe88b166e6f60eccb3b4f46b7db5d69dbcc64
>> Thanks for tracing this and taking the time to post it.

I have confirmed that this change/commit fixes my/the problem!

Thank you Herwig for the fix, and thank you Sean for narrowing the problem down!