Re: autocommit and stored procedures

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

> roehm@it.usyd.edu.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