Server-prepared statements fail to handle some rollback cases - Mailing list pgsql-jdbc

From Oliver Jowett
Subject Server-prepared statements fail to handle some rollback cases
Date
Msg-id 20031202064215.GI19205@opencloud.com
Whole thread Raw
List pgsql-jdbc
The current driver fails to handle some cases involving server-side prepared
statements and rollbacks. The attached testcases demonstrate some of the
problems. I get:

    [junit] Testcase: testPrepareRollbackExecute(org.postgresql.test.jdbc2.ServerPreparedStmtTest):     Caused an ERROR
    [junit] ERROR: prepared statement "jdbc_statement_11" does not exist
    [junit] org.postgresql.util.PSQLException: ERROR: prepared statement "jdbc_statement_11" does not exist
    [junit]     at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:153)
    [junit]     at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:157)
    [junit]     at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:109)
    [junit]     at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
    [junit]     at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:520)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:55)
    [junit]     at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:236)
    [junit]     at
org.postgresql.test.jdbc2.ServerPreparedStmtTest.testPrepareRollbackExecute(ServerPreparedStmtTest.java:289)
    [junit]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [junit]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [junit]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)


    [junit] Testcase: testPrepareRollbackDeallocate(org.postgresql.test.jdbc2.ServerPreparedStmtTest):  Caused an ERROR
    [junit] ERROR: current transaction is aborted, commands ignored until end of transaction block
    [junit] org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of
transactionblock 
    [junit]     at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:153)
    [junit]     at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:157)
    [junit]     at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:109)
    [junit]     at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
    [junit]     at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:520)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:55)
    [junit]     at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:236)
    [junit]     at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:224)
    [junit]     at
org.postgresql.test.jdbc2.ServerPreparedStmtTest.testPrepareRollbackDeallocate(ServerPreparedStmtTest.java:314)
    [junit]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [junit]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [junit]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

I don't have a patch for these problems at the moment as it's quite involved
to get right; the driver needs a mechanism to get feedback on the execution
of individual statements within a multi-statement query it's synthesized.
The underlying problem is that a server-prepared statement looks like:

  PREPARE jdbc_statement_N(...) AS query; EXECUTE jdbc_statement_N(...)

and we need to distinguish failure to PREPARE from failure to EXECUTE (as
the PREPARE is not a transactional action) to work out whether we can
subsequently EXECUTE or not, and similarly, whether we should DEALLOCATE or
not -- we can't just ignore errors on DEALLOCATE as it might result in the
txn being rolled back, as happens in the second failure.

Suggestions on a simple fix are welcome :)

-O

Attachment

pgsql-jdbc by date:

Previous
From: Oliver Jowett
Date:
Subject: patch: improve exception propagation in testcases
Next
From: pginfo
Date:
Subject: Re: autocommit trouble with jdbc on Postgres 7.4