Re: autocommit and stored procedures

["Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>]

roehm(at)it(dot)usyd(dot)edu(dot)au wrote:
> In particular, does an JDBC autocommit around the call to a stored
> procedure commit each statement within that stored procedure, or does 
> it commit the procedure as a whole?

A function in PostgreSQL is always executed within one single
transaction, so you cannot commit only part of the SQL-statements
within a function.

The JDBC 4.0 specification says:

 The Connection attribute auto-commit specifies when to end transactions. Enabling
 auto-commit causes a transaction commit after each individual SQL statement as
 soon as that statement is complete. The point at which a statement is considered to
 be "complete" depends on the type of SQL statement as well as what the application
 does after executing it:

 ■ For Data Manipulation Language (DML) statements such as Insert, Update,
 Delete, and DDL statements, the statement is complete as soon as it has finished
 executing.
 ■ For Select statements, the statement is complete when the associated result set
 is closed.
 ■ For CallableStatement objects or for statements that return multiple results,
 the statement is complete when all of the associated result sets have been closed,
 and all update counts and output parameters have been retrieved.

To me that seems to mean that if I do

executeUpdate("SELECT myfunction()")

the autocommit should occur after the select statement is finished, i.e.
after the complete function has been executed.

But I'll admit that this is disputable...

Yours,
Laurenz Albe