Re: autocommit and stored procedures

On 16-Aug-07, at 5:46 AM, Albe Laurenz wrote:

> 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.

Isn't that interesting I wonder who's database has the above select
and CallableStatement semantics.

Dave
>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match