From: Craig Ringer [mailto:cr...@2ndquadrant.com] > The example often cited is some variant of > > BEGIN; > CREATTE TABLE t2 AS SELECT * FROM t1; > DROP TABLE t1; > ALTER TABLE t2 RENAME TO t1; > COMMIT; > > Right now, we do the right thing here. With default statement level rollback, > you just dropped t1 and all your data. oops.
That's a horrible example. So I think the default behavior should be what it is now for existing PostgreSQL users. > On a related note, psql's -v ON_ERROR_STOP=1 is horrible and hard to discover > UI, and one of the top FAQs on Stack Overflow is some variant of "I'm getting > random and incomprehensible errors restoring a dump, wtf?". So I'd really > love to make it the default, but we'd face similar issues where a SQL script > that's currently correct instead produces dangerously wrong results with > ON_ERROR_STOP=1 . Yes. And although unrelated, psql's FETCH_SIZE is also often invisible to users. They report out-of-memory trouble when they do SELECT on a large table with psql. > What about if we add protocol-level savepoint support? Two new messages: > > BeginUnnamedSavepoint > > and > > EndUnnamedSavepoint > > where the latter does a rollback-to-last-unnamed-savepoint if the txn state > is bad, or a release-last-unnamed-savepoint if the txn state is ok. That > means the driver doesn't have to wait for the result of the statement. It > knows the conn state and query outcome from our prior messages, and knows > that as a result of this message any failed state has been rolled back. > > This would, with appropriate libpq support, give people who want statement > level error handling pretty much what they want. And we could expose it > in psql too. No GUCs needed, no fun surprises for apps. psqlODBC could adopt > it to replace its current slow and super-log-spammy statement rollback > model. > > Downside is that it needs support in each client driver. Yes, I believe we should avoid the downside. It's tough to develop and maintain a client driver, so we should minimize the burdon with server-side support. Regards Takayuki Tsunakawa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers