Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Gregory Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] AUTOCOMMIT currently doesn't handle non-transactional commands very well
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly