Hi everyone,
The following test code…
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.concurrent.CountDownLatch;
/** @author Christian Schlichtherle */
public class Ticket251IT {
private static final String CONNECTION_STRING = "jdbc:postgresql:postgres"; private static final int NUM_THREADS = 2;
@Test public void foo() throws SQLException, InterruptedException { try (Connection c = DriverManager.getConnection(CONNECTION_STRING)) { c.setAutoCommit(false); final Runnable task = new Runnable() { final CountDownLatch startSignal = new CountDownLatch(NUM_THREADS);
@Override public void run() { try { startSignal.countDown(); startSignal.await(); // FIXME: This idiom doesn't work on a shared connection! Savepoint sp = c.setSavepoint(); try { // Insert transaction script here... } finally { c.releaseSavepoint(sp); } } catch (SQLException | InterruptedException e) { e.printStackTrace(); } } }; final Thread[] threads = new Thread[NUM_THREADS]; for (int i = 0; i < threads.length; i++) (threads[i] = new Thread(task)).start(); for (Thread thread : threads) thread.join(); }, }
}
…frequently produces the following output…
org.postgresql.util.PSQLException: ERROR: no such savepointat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:376)at org.postgresql.jdbc3.AbstractJdbc3Connection.releaseSavepoint(AbstractJdbc3Connection.java:192)at cpssd.db.Ticket251IT$1.run(Ticket251IT.java:32)at java.lang.Thread.run(Thread.java:745)
The obvious workaround is to put all calls on the connection into a synchronized (whatever) { … } block. However, although the workaround works fine when applied to this isolated test case, it fails in my production code. I have yet to find out why that is. Nevertheless, this seems to be a bug in the JDBC driver.
The real use case behind this simplified test case is to distribute work to multiple threads. A parent thread creates the connection and starts a transaction, then spawns a number of child threads. Each child thread creates a save point, does its work and releases or rolls back the save point again. If all child threads succeed, the parent thread then commits the entire transaction. I figure from
this document that this is indeed „fair use“ of a Connection. Thus, it would be superb if PostgreSQL could support it.
Regards,
Christian Schlichtherle