Re: problem with new autocommit config parameter and jdbc

Curt Sampson <cjs(at)cynic(dot)net> writes:
> From Date's _A Guide to the SQL Standard_ (Fourth Edition):
> ...
> The following SQL statements are _not_ transaction-initiating:

> CONNECT
> SET CONNECTION
> DISCONNECT
> SET SESSION AUTHORIZATION
> SET CATALOG
> SET SCHEMA
> SET NAMES
> SET TIME ZONE
> SET TRANSACTION
> SET CONSTRAINTS
> COMMIT
> ROLLBACK
> GET DIAGNOSTICS

Hm. This brings up a thought I've been turning over for the past
couple days. As of CVS tip, SET commands *do* initiate transactions
if you have autocommit off. By your reading of Date, this is not
spec compliant for certain SET variables: a SET not already within
a transaction should not start a transaction block, at least for the
variables mentioned above. It occurs to me that it'd be reasonable
to make it act that way for all SET variables.

An example of how this would simplify life: consider the problem of
a client that wants to ensure autocommit is on. A simple
SET autocommit TO on;
doesn't work at the moment: if autocommit is off, then you'll need
to issue a COMMIT as well to get out of the implicitly started
transaction. But you don't want to just issue a COMMIT, because
you'll get a nasty ugly WARNING message on stderr if indeed autocommit
was on already. The only warning-free way to issue a SET right now
if you are uncertain about autocommit status is
BEGIN; SET .... ; COMMIT;
Blech. But if SET doesn't start a transaction then you can still
just do SET. This avoids some changes we'll otherwise have to make
in libpq startup, among other places.

Does anyone see any cases where it's important for SET to start
a transaction? (Of course, if you are already *in* a transaction,
the SET will be part of that transaction. The question is whether
we want SET to trigger an implicit BEGIN or not.)

> Nor, of course, are the nonexecutable statements DECLARE CURSOR,
> DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
> SECTIONS, and WHENEVER.

Hmm. I think the spec's notion of DECLARE must be different from ours.
Our implementation of DECLARE CURSOR both declares and opens the cursor,
and as such it *must* be transaction-initiating; else it's useless.

regards, tom lane