Thread: AUTOCOMMIT currently doesn't handle non-transactional commands very well

The psql manual says this:

AUTOCOMMIT     ... The autocommit-off mode works by issuing an implicit BEGIN for you,     just before any command that
isnot already in a transaction block and     is not itself a BEGIN or other transaction-control command, nor a
commandthat cannot be executed inside a transaction block (such as     VACUUM).
 

Unfortunately that's not precisely true. In fact psql cannot know whether the
command can be executed within a transaction block since some commands, like
CLUSTER and now CREATE INDEX, sometimes can and sometimes can't.

The basic problem is that really we want to be able to run these commands even
if a transaction has been started as long as nothing else has been done in
that transaction (including the savepoint that psql also does automatically).
Would it work to just check whether the serializable snapshot has been set?
That would be simpler than the current logic.

One possible criticism is that a user that manually does BEGIN; CLUSTER
DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable. It
seems to me that perhaps we want to somehow distinguish between manually
invoked BEGIN where we would want to notify the user if they're trying to run
something that will be committed automatically and implicit BEGIN which starts
a new transaction but only the next time a transactional command is run.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Gregory Stark <gsstark@mit.edu> writes:
> One possible criticism is that a user that manually does BEGIN; CLUSTER
> DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable.

s/possible criticism/deal-breaker/ ... you can't possibly think that the
above would be acceptable.  It'd be worse than "won't be undoable"; it'd
probably corrupt your database.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <gsstark@mit.edu> writes:
> > One possible criticism is that a user that manually does BEGIN; CLUSTER
> > DATABASE; ROLLBACK; won't be warned that the cluster will not be undoable.
> 
> s/possible criticism/deal-breaker/ ... you can't possibly think that the
> above would be acceptable.  It'd be worse than "won't be undoable"; it'd
> probably corrupt your database.

I'm not sure I understand why. Or are you just referring to the snapshot bugs
in cluster?

I'm imagining what would happen is that cluster would take the liberty of
committing the transaction begun by the BEGIN since it hadn't been used yet
anyways. Then it would leave you with a fresh transaction when it was done so
the rollback would be a noop as it just rolled back that empty transaction.

I do have an alternative idea: Instead of having psql parse the SQL commands
to try to guess which commands are non-transactional, have psql simply try the
command, and check the error code. If a command fails immediately after the
BEGIN implicitly inserted when autocommit=false and it fails with a specific
error code set aside for this purpose, then abort the transaction and
reattempt it outside a transaction.

If that error comes back during a user-initiated transaction or with
autocommit=true then psql wouldn't do anything special.

I'm still a bit bothered by all this since I think it would still make it hard
to use non-transactional commands from other clients. Clients like DBI and
JDBC generally assume you're *always* in a transaction so one imagines they do
something similar to psql with inserting implicit BEGINs everywhere.

The "real" solution is probably to go back to autocommit=false semantics on
the server and have psql implement autocommit mode simply by inserting
"commit" all the time. But I have a feeling people are so burned by the last
change in this area that bringing it up again isn't going to win me any
friends :)

-- 
greg

Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> s/possible criticism/deal-breaker/ ... you can't possibly think that the
>> above would be acceptable.  It'd be worse than "won't be undoable"; it'd
>> probably corrupt your database.

> I'm not sure I understand why. Or are you just referring to the snapshot bugs
> in cluster?

The ROLLBACK would undo the catalog updates made by the command, but not
its non-transactional changes.  Possibly in some cases there would be
no resulting inconsistency, but in general it would leave inconsistent
state.  In most of our usages of PreventTransactionChain, the point is
that a rollback occuring after the command thinks it's completed would
be unsafe.

> I do have an alternative idea: Instead of having psql parse the SQL commands
> to try to guess which commands are non-transactional, have psql simply try the
> command, and check the error code.

I don't think it is really all that hard to have psql parse the commands
far enough to determine if they're transactional or not.  If you can't
tell by easy visual inspection which kind of command it is, then we've
misdesigned the command language and should change it: it'll be
confusing for people as well as programs.

In the case of the online-index-creation command, this may require
putting the critical keyword up front rather than burying it somewhere
in the command string, but I don't see a problem with that.
        regards, tom lane