Server-prepared statements fail to handle some rollback cases

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Server-prepared statements fail to handle some rollback cases
Date: 2003-12-02 06:42:15
Message-ID: 20031202064215.GI19205@opencloud.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 transaction block
[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 Content-Type Size
serverprepare_rollback_tests.txt text/plain 2.3 KB

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2003-12-02 07:31:54 Re: autocommit trouble with jdbc on Postgres 7.4
Previous Message pginfo 2003-12-02 06:29:52 Re: autocommit trouble with jdbc on Postgres 7.4