Pg-jdbc Appends RETURNING * on Prepared Batch wtih Existing RETURNING Clause

Question

I am executing a prepared batch insert into a Postgres table using pg-jdbc/JDBI in order to obtain a list of generated id values using an explicit RETURNING clause:

PreparedBatch b = getHandle().prepareBatch("INSERT INTO death_star(id,exhaust_port) VALUES ( :id, :exhaust_port) RETURNING id;");

for (RebelPilot p : rebelPilots) {
    b.add().bind("id",p.getId()).bind("exhaust_port",p.getProtonTorpedo());
}

ResultSetMapper<Long> mapper = new IdMapper()
GeneratedKeys<Long> gk = b.executeAndGenerateKeys(mapper);
return gk.list()

When the statement is prepared, pg-jdbc will mindlessly append an extra RETURNING * after the existing RETURNING clause resulting in the following malformed abomination:

INSERT INTO death_star(id,exhaust_port) VALUES ( ?, ?) RETURNING id RETURNING *;

If I remove the explicit RETURNING clause, the statement works fine; however, it causes ALL fields from the target table to be returned, which is highly undesirable in many situations where large amounts of data have been inserted.

Is there any method to stop the pg-jdbc from engaging in this behavior?

Test Dependencies:

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.4.1207.jre7</version>
</dependency>
<dependency>
  <groupId>org.jdbi</groupId>
  <artifactId>jdbi</artifactId>
  <version>2.71</version>
</dependency>

When using plain JDBC `Connection.prepareStatement(String)` will **not** append the `returning` clause. So I guess that `prepareBatch()` method calls e.g. [`Connection.prepareStatement(String, int)`](http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#prepareStatement-java.lang.String-int-). Can you teach that method to do a "plain" prepare? I don't know what `executeAndGenerateKeys()` does. But with plain JDBC you would simply call `execute()` and than obtain the generated keys using `getResultSet()` from the `PreparedStatement` — , Jan 01 '16 at 23:15
I don't understand why blanket appending `RETURNING *` would be the default behavior without a global option to change it, especially when a `RETURNING` clause is already present in the statement Perhaps I need to file an issue on pg-jdbc and JDBI. There doesn't seem to be a way to work around this behavior with a JDBI prepared batch. According to [this](https://github.com/2ndQuadrant/pgjdbc/commit/1471bd93fce5401c8bc80f686f8beace9572ec79) pg-jdbc doesn't actually do batching if target tables contain arrays or unbounded text columns, so I'm not sure if there is a point to begin with, — THX1138, Jan 02 '16 at 00:16
This was discussed on the JDBC mailing list. When using `prepareStatement(String, int)` the driver would need to parse the statement to find out which columns to return. But you *do* have an option to change this (in JDBC). If you use `prepareStatement(String, String[])` the driver will only return the specified columns (I don't know if your obfuscation layer will let you do that though) — , Jan 02 '16 at 08:49

Mark Rotteveel · Accepted Answer · 2016-01-02T08:39:32.870

You are using executeAndGenerateKeys which will likely use Connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS). The generated keys implementation of PostgreSQL JDBC will append RETURNING * to the query in that case (it does not check if it already exists in the query text). You need to remove RETURNING id from your own query to make it work.

If you want to use RETURNING id as some sort of optimization, then you need to find out if JDBI has an API to access Connection.preparedStatement(String query, String[] columnNames) (which as far as I can tell it doesn't have).

To only other option is to file an improvement request for pgjdbc.

Filed bugs on JDBI [here](https://github.com/jdbi/jdbi/issues/253) and pgjdbc [here](https://github.com/pgjdbc/pgjdbc/issues/488) — THX1138, Jan 02 '16 at 15:46

score 0 · Answer 2 · answered Jul 24 '16 at 10:06

0

Use connection.prepareStatement(sql) instead of connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)

answered Jul 24 '16 at 10:06

Thirumal

8,280
11
53
103

Pg-jdbc Appends RETURNING * on Prepared Batch wtih Existing RETURNING Clause

2 Answers2