Re: problem with new autocommit config parameter and jdbc

On Tue, 2002-09-10 at 21:44, Curt Sampson wrote:
> But there were some issues with rolling back and SET commands,
> weren't there? I remember a long discussion about this that I'm
> not sure I want to go back to. :-)

So.. Unless explicitly requested, a SET command should have immediate
effect?

The other constrictive value I can think of is search_path.

-- Must be transaction safe
BEGIN;
CREATE SCHEMA <newschema>;
SET search_path = <newschema>;
ROLLBACK;
CREATE TABLE...

-- This should be ok
BEGIN;
SET autocommit = on;
INSERT ...
COMMIT;
-- SET takes place on commit, as it was an explicit transaction

-- This is requested behavior
SET autocommit = off;
SET autocommit = on;
INSERT... -- immediate effect, since autocommit is on

-- This gets interesting be ok as the schema must exist
SET autocommit = off;
CREATE SCHEMA <newschema>;
SET search_path = <newschema>; -- implicit commit here?
ROLLBACK;
CREATE TABLE ...
-- search_path must roll back or schema must have been created

-- Similar to the above
SET autocommit = off;
CREATE TABLE ...
SET autocommit = on; -- implicit commit here?
ROLLBACK;
-- Does this rollback anything?
-- Was CREATE TABLE committed with the second SET statement?

> Well, I'm not going to go chase it down right now, but ISTR that
> DECLAREing a cursor just allocates a variable name or the storage for it
> or something like that; it doesn't actually create an active cursor.

Indeed, this is how the cursor is able to cross transactions. It is
closed at transaction commit, and re-created in next use.

4.29:

For every <declare cursor> in an SQL-client module, a cursor is
effectively created when an SQLtransaction (see Subclause 4.32,
SQL-transactions ) referencing the SQL-client module is initiated.

--
Rod Taylor