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

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