Error when using RETURNING from PostgreSQL

[rcanepa]

Hey,

I am having troubles to define an insert query which should return the new record id. I defined the query this way:

-- :name insert-contact! :!
-- :doc Insert a contact record.
INSERT INTO contacts
(:i*:fields)
VALUES
(:v*:values)
RETURNING id;

When I execute the query through HugSQL I get a BatchUpdateException, however, If I execute the query on psql it pass without problems (returning the record's id). This is the error stacktrace:

user=> ERROR Batch entry 0 INSERT INTO contacts
(address, email, first_name, organization_name, phone, comments, identifier, last_name)
VALUES
('Apoquindo 6371','contacto@moptte.cl','Arturo','Moptte','+56 2 7781 2831','4 oficinas 12 meses.','13.121.981-K','Clode')
RETURNING id was aborted.  Call getNextException to see the cause.
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO contacts
(address, email, first_name, organization_name, phone, comments, identifier, last_name)
VALUES
('Apoquindo 6371','contacto@moptte.cl','Arturo','Moptte','+56 2 7781 2831','4 oficinas 12 meses.','13.121.981-K','Clode')
RETURNING id was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2717)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2666)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImpl.java:373)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1828)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2898)
    at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:404)
    at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:397)
    at clojure.java.jdbc$db_do_execute_prepared_statement$fn__10737.invoke(jdbc.clj:764)
    at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:595)
    at clojure.java.jdbc$db_transaction_STAR_.doInvoke(jdbc.clj:568)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:763)
    at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:748)
    at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:786)
    at clojure.java.jdbc$db_do_prepared.doInvoke(jdbc.clj:770)
    at clojure.lang.RestFn.invoke(RestFn.java:464)
    at clojure.java.jdbc$execute_BANG_$execute_helper__10764.invoke(jdbc.clj:891)
    at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:896)
    at clojure.java.jdbc$execute_BANG_.doInvoke(jdbc.clj:875)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.RestFn.applyTo(RestFn.java:132)
    at clojure.core$apply.invokeStatic(core.clj:649)
    at clojure.core$apply.invoke(core.clj:640)
    at hugsql.adapter.clojure_java_jdbc.HugsqlAdapterClojureJavaJdbc.execute(clojure_java_jdbc.clj:11)

Any thoughts on what could I be missing?

[robinheghan]

I can confirm this bug. I noticed it yesterday. My exception was "Received result when none was expected"

Using the latest version of hugsql, and funcool/clojure.jdbc adapter.

[csummers]

This happens because statements executed with execute (:! in HugSQL) are not expected to return result sets. You can get around this issue by using :? for your insert w/ returning statements.

However, I'm going to keep this open. The HugSQL docs should indicate this, I should add a test to show this, and I'm thinking about adding a command type that would indicate this. Also, this is tangentially related to issue #8.

[rcanepa]

@csummers great!. I can confirm that it solves the problem. All I had to do was change my query to something like this:

-- :name insert-contact! :? :1
-- :doc Insert a contact record.
INSERT INTO contacts
(:i*:fields)
VALUES
(:v*:values)
RETURNING id;

Thanks!

[csummers]

As of the 0.4.0, there is a :command type of :returning-execute, or :<! for short. This still executes as a query like :?, but provides a bit more semantic value. See Insert for an example.

[rcanepa]

Excellent!... I will update my code and let you know if I something doesn't work!... Keep the good work!!

[sthomp]

Just came across this issue and wanted to suggest adding an example to the Insert examples of how to insert with dynamic columns (ie: the (:i*:fields) notation used above). All the current examples use fixed columns.

[csummers]

Thanks for the suggestion. I could add a couple of examples to this effect. I think a likely scenario instead of the separate fields and values vectors would use a hashmap of {"field1" "value1"...}. This is hinted at with the generic update example in the Clojure Expressions portion of the docs.

[sthomp]

That would be really nice. The {field value .. syntax is probably the best feature about Korma that other libraries are missing

[rcanepa]

Hi @sthomp, I use that pattern (:i*:fields) in combination with something like this:

(s/defn create-office :- {s/Keyword s/Uuid}
  [db :- ConnectionMap
   office :- schemas/Office]
  (let [office (select-keys office schemas/office-db-fields) ;; remove read only fields
        fields (map name (keys office)) ;; get fields as strings
        values (vals office) ;; get values
        sql-args {:fields fields
                  :values values}]
    (execute! db :insert-office sql-args)))

[sthomp]

Thanks @rcanepa I'm also doing something similar

[Ramblurr]

@csummers Is this feature supposed to work for UPDATE statements too? Postgres also supports this.

[byrongibby]

Works with upsert using INSERT ON CONFLICT statement as well, if there was any doubt.