Re: Statement-level rollback

From: Craig Ringer [mailto:craig(at)2ndquadrant(dot)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