`datafy` can call getHoldability (exception on PostgresSQL)

[owen-audience-republic]

I'm trying to put together a function to report back on table schema in PostgreSQL. It is throwing an exception which looked reportworthy. I swear this exact function has also worked without error, so there is some sort of configuration problem at play - I'm not sure what though.

This isn't a huge problem for me, I'll rewrite the function to do the same thing in a different way. But I thought documenting the problem somewhere would be helpful.

(defn simple-table-schema
  "If table-name exists, return its schema as a sequence ({:name \"oid\" :type \"bigserial\"})"
  [tx table-name]
  (let [schema (-> tx
                   .getMetaData
                   (.getColumns nil "public" table-name nil)
                   clojure.datafy/datafy
                   :rows)]
    (map #(hash-map :name (:COLUMN_NAME %) :type (:TYPE_NAME %)) schema)))

tx is a com.zaxxer.hikari.pool.HikariProxyConnection wrapping an org.postgresql.jdbc.PgConnection. Running that on a table causes the following exception (edited out the top of the stack trace):

HikariPool-2 - Connection org.postgresql.jdbc.PgConnection@e4b7b23 marked as broken because of SQLSTATE(0A000), ErrorCode(0)
  debug/simple-table-schema                  copy.clj:   69
  clojure.datafy/datafy                datafy.clj:   23
  clojure.core.protocols/fn/G             protocols.clj:  182
  next.jdbc.datafy/fn                datafy.clj:  162
  next.jdbc.datafy/safe-bean                datafy.clj:   94
  ...                                
  clojure.java.data/fn                  data.clj:  365
  clojure.core/into                  core.clj: 6958
  clojure.core/reduce                  core.clj: 6886
  clojure.core.protocols/fn/G             protocols.clj:   13
  clojure.core.protocols/fn             protocols.clj:   75
  clojure.core.protocols/seq-reduce             protocols.clj:   31
  clojure.core.protocols/fn/G             protocols.clj:   19
  clojure.core.protocols/fn             protocols.clj:  169
  clojure.core/next                  core.clj:   64
                                                       ...                                
  clojure.java.data/fn/iter/fn                  data.clj:  350
  clojure.java.data/fn/iter/fn/fn                  data.clj:  351
  clojure.java.data/make-shallow-getter-fn/fn                  data.clj:   98
                                                       ...                                
  com.zaxxer.hikari.pool.HikariProxyResultSet.getHoldability  HikariProxyResultSet.java     
  org.postgresql.jdbc.PgResultSet.getHoldability          PgResultSet.java: 3663
   org.postgresql.Driver.notImplemented               Driver.java:  699
java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgResultSet.getHoldability() is not yet implemented.
     SQLState: "0A000"
    errorCode: 0

This was on postgres 9.6.3, running on linux, Clojure 1.11.1, jdbc.next 1.2.796 & [org.postgresql/postgresql "42.2.16"].

[seancorfield]

Since you want just the :rows, you would do better calling next.jdbc.result-set/datafiable-result-set (on the result of .getColumns, passing tx as the connectable and an options hash map), then calling datafy on that (and removing the :rows selector).

The default datafication of a ResultSet -- after you've required next.jdbc.datafy -- is going to attempt to call getters on the ResultSet object and some database drivers, unfortunately, do strange things on what should be purely readonly operations on object state. Looking at the stack trace above, it's hard to tell whether that's bad behavior by the PG driver or by HikariCP -- but the net result is that calling the (unimplemented feature) getHoldability marked the Connection as bad.

Since metadata doesn't change (unless you're actively applying DDL changes while your app is running), you might do better to precalculate the metadata once at startup and cache it -- and use a plain PG Connection that you get from next.jdbc, independent of the HikariCP pool.

I'll leave this open until you've verified my suggestion about calling datafiable-result-set, but there's not much next.jdbc can do about bad driver/pooling behavior in cases like this.

[owen-audience-republic]

This does work:

(defn simple-table-schema-ii
  "If table-name exists, return its schema as a sequence ({:name \"oid\" :type \"bigserial\"})"
  [tx table-name]
  (let [schema (-> tx
                   .getMetaData
                   (.getColumns nil "public" table-name nil)
                   rs/datafiable-result-set
                   clojure.datafy/datafy)]
    (map #(hash-map :name (:COLUMN_NAME %) :type (:TYPE_NAME %)) schema)))

Thankyou very much.