Re: autocommit and stored procedures

On Wed, 2007-08-15 at 21:21 +1000, roehm(at)it(dot)usyd(dot)edu(dot)au wrote:
> Hi,
>
> 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?
> 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?
>
> Example:
>
> Stored Procedure
> ----------------
> CREATE PROCEDURE Test (n varying character,val REAL)
> DECLARE
> cid INTEGER;
> BEGIN
> SELECT custid INTO cid
> FROM account
> WHERE name=n;
>
> UPDATE checking
> SET balance=balance-val
> WHERE custid=cid;
> END;
>
> JDBC Code
> ---------
> Connection conn;
> CallableStatement cstmt;
> conn.setAutoCommit(true);
> cstmt = conn.prepareCall("{call Test(?,?)}");
> cstmt.setString(1, name);
> cstmt.setString(2, value);
> cstmt.execute();
>
>
> Does PostgreSQL execute one commit after the execution of Test(),
> i.e. do select and update run in one transaction?
> Or will PostgreSQL commit after the select and then again after the
> update
> inside the Test() procedure?
>
> Does anyone know what the specified behaviour is for JDBC AutoCommit?
>
> Many thanks
>
> Uwe

The JDBC driver manages the autocommit flag by not beginning a new
transaction at all, since in PostgreSQL all statements execute in their
own private transactions unless an explicit transaction is started with
the BEGIN statement.

Therefore, you would expect each individual statement (as recognized by
the PG back-end, not by your Java code) to execute and commit
individually.

I do not know whether or not JDBC specifies a behavior for this case,
but given the intentional vagueness of the spec in several areas, I
would be very surprised if the the spec weren't either silent or
answered, "Executing multiple SQL commands in a single JDBC command is
unsupported and may result in unspecified, driver-specific behavior".

-- Mark