Re: autocommit and stored procedures

[Oliver Jowett <oliver(at)opencloud(dot)com>]

roehm(at)it(dot)usyd(dot)edu(dot)au wrote:

> Which command granularity does the JDBC driver's autocommit have?
> Does it commit after each client-side JDBC statement, or does it commit
> each individual SQL statement on the server-side?

It effectively commits after each client-side JDBC statement execution, 
even if you provide multiple semicolon-separated queries in a single 
statement. At the protocol level the driver converts a single JDBC 
statement into one or more groups of Parse/Bind/Execute messages, 
followed by a single Sync. You can see this if you look at the debugging 
produced with loglevel=2. Also see 
http://www.postgresql.org/docs/8.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Regardless of that, there's no way I know of to get "autocommit" within 
a stored procedure as you seem to be wanting, regardless of what client 
driver you're using, because the server will always set up a transaction 
(one way or another) before executing the top-level query that runs your 
stored procedure so by the time your stored procedure is running it's 
too late to think about transaction demarcation.

> Does anyone know what the specified behaviour is for JDBC AutoCommit?

See the javadoc for Connection.setAutoCommit():

> The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed. In advanced cases, a single statement may return multiple results as well as output parameter values. In these cases, the commit occurs when all results and output parameter values have been retrieved.

-O