Обсуждение: Problem with prepareStatement and Statement.RETURN_GENERATED_KEYS in PostgreSQL JDBC driver 8.4

When using 8.4 JDBC drivers, calling prepareStatement(String sql, int
autoGeneratedKeys) with Statement.RETURN_GENERATED_KEYS seems to add
"RETURNING *" to the end of the SQL even with select statements.
According to Javadoc for prepareStatement(String sql, int
autoGeneratedKeys) in java.sql.Connection:

"The given constant tells the driver whether it should make
auto-generated keys available for retrieval. This parameter is ignored
if the SQL statement is not an INSERT statement, or an SQL statement
able to return auto-generated keys (the list of such statements is
vendor-specific). "

Here's a simple test class to demonstrate this problem:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class PostgrePrepareStatementTest {

    public static void main(String[] args) throws
InstantiationException, IllegalAccessException, ClassNotFoundException,
SQLException {
        Class.forName("org.postgresql.Driver").newInstance();
        String url = "jdbc:postgresql://localhost:5432/testdb";
        Connection conn = DriverManager.getConnection(url, "user",
"password");
        PreparedStatement pStmt = conn.prepareStatement("select * from
test_table", Statement.RETURN_GENERATED_KEYS);
        System.out.println(pStmt.toString());
        pStmt.execute();
    }
}


When run this prints "select * from test_table RETURNING *" -  which is
invalid - and an exception is thrown:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
syntax error at or near "RETURNING"
  Position: 26
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360)
    at
com.smilehouse.PostgrePrepareStatementTest.main(PostgrePrepareStatementTest.java:17)

I have tested this with driver version 8.4-701 (both JDBC3 and JDBC4).
The PostgreSQL version was 8.4.

-Heikki Hiltunen

Heikki Hiltunen wrote:
> When using 8.4 JDBC drivers, calling prepareStatement(String sql, int
> autoGeneratedKeys) with Statement.RETURN_GENERATED_KEYS seems to add
> "RETURNING *" to the end of the SQL even with select statements.
> According to Javadoc for prepareStatement(String sql, int
> autoGeneratedKeys) in java.sql.Connection:
>
> "The given constant tells the driver whether it should make
> auto-generated keys available for retrieval. This parameter is ignored
> if the SQL statement is not an INSERT statement, or an SQL statement
> able to return auto-generated keys (the list of such statements is
> vendor-specific). "

Yeah, the driver just blindly tacks a " RETURNING *" to the end of the
SQL string if you specify RETURN_GENERATED_KEYS. I'm tempted to do
something like this:

*** AbstractJdbc3Connection.java    23 Dec 2009 10:28:40 +0200    1.21
--- AbstractJdbc3Connection.java    23 Apr 2010 18:49:44 +0300
***************
*** 359,364 ****
--- 359,381 ----
      throws SQLException
      {
          checkClosed();
+
+     /*
+      * We implement fetching auto-generated keys by tacking a
+      * " RETURNING *" to the end of the string. Don't try to do that
+      * with other statements than INSERT/UPDATE/DELETE.
+      *
+      * XXX this gets fooled by comments at the beginning of the SQL string
+      */
+         if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
+         {
+         String trimmedSql = sql.trim();
+         if (!trimmedSql.regionMatches(true, 0, "INSERT", 0, 6) &&
+         !trimmedSql.regionMatches(true, 0, "UPDATE", 0, 6) &&
+         !trimmedSql.regionMatches(true, 0, "DELETE", 0, 6))
+         autoGeneratedKeys = Statement.NO_GENERATED_KEYS;
+     }
+
          if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
              sql = AbstractJdbc3Statement.addReturning(this, sql, new
String[]{"*"}, false);

But that's not very bullet-proof, and will fail to detect the statement
as an INSERT if it e.g begins with a comment. We could add a mini-parser
to detect comments too, but it's not a very robust approach.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas wrote:

> But that's not very bullet-proof, and will fail to detect the statement
> as an INSERT if it e.g begins with a comment. We could add a mini-parser
> to detect comments too, but it's not a very robust approach.

It will also fail to work with WITH clauses,

alvherre=# create table r (f int);
CREATE TABLE
alvherre=# insert into r values (42), (142857);
INSERT 0 2
alvherre=# with t as (select 1) update r set f = f + t."?column?" from t returning r.*;
   f
--------
     43
 142858
(2 filas)

UPDATE 2

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera-9 wrote
> Heikki Linnakangas wrote:
>
>> But that's not very bullet-proof, and will fail to detect the statement
>> as an INSERT if it e.g begins with a comment. We could add a mini-parser
>> to detect comments too, but it's not a very robust approach.
>
> It will also fail to work with WITH clauses,
>
> alvherre=# create table r (f int);
> CREATE TABLE
> alvherre=# insert into r values (42), (142857);
> INSERT 0 2
> alvherre=# with t as (select 1) update r set f = f + t."?column?" from t
> returning r.*;
>    f
> --------
>      43
>  142858
> (2 filas)
>
> UPDATE 2

Check for insert/update/delete (IUD) at query start (ignoring whitespace) or
immediately following a close parenthesis, and for added measure check for
into/set/from appropriately subsequent to (i.e, need to account for relation
name in the set case)?

Can anchor off of */ for block comment detection and ignoring anything
following a -- should be straight forward.

Nested block comments will be a problem though...does the parser even
effectively allow them?

Given the end result of a bad guess is a parser error getting it 98%
accurate is acceptable.  Even if it is somewhat expensive I'm less inclined
to worry about punishing those who blindly set the option.  I imagine a
typical IUD statement should be able to quickly match any regex we use so
the typical use case should still be performant.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Problem-with-prepareStatement-and-Statement-RETURN-GENERATED-KEYS-in-PostgreSQL-JDBC-driver-8-4-tp2172722p5807018.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.