Re: confused about transactions and connection pools

[Kris Jurka <books(at)ejurka(dot)com>]

On Wed, 1 Nov 2006, Oliver Jowett wrote:

> Tom Lane wrote:
> > I'm a bit
> > dubious that the prepared-statement machinery actually results in a win
> > compared to just issuing simple Query messages.
>
> The driver has no infrastructure for issuing simple Query messages in v3 
> mode, and anyway we want to batch the BEGIN up with the query that follows 
> it.

We could easily use QueryExecutor.QUERY_ONESHOT for these, but I'm not 
sure what the point would be.  The 8.2 release can log this correctly so 
I'm not sure why bother changing it for the 8.2 driver.  Some quick 
testing shows that using ONESHOT results in a 4-5% hit using the attached 
test case of a BEGIN; SELECT 1; COMMIT loop.

Kris Jurka

import java.sql.*;

public class Commit {

	public static void main(String args[]) throws Exception {
		Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5820/jurka", "jurka", "");

		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();

		for (int j=0; j<10; j++) {
		long t1 = System.currentTimeMillis();

		for (int i=0; i<10000; i++) {
			ResultSet rs = stmt.executeQuery("SELECT 1");
			rs.close();
			conn.commit();
		}

		long t2 = System.currentTimeMillis();

		System.out.println(t2 - t1);
		}

	}
}