Re: [HACKERS] Statement-level rollback - Mailing list pgsql-hackers

From Tsunakawa, Takayuki
Subject Re: [HACKERS] Statement-level rollback
Date
Msg-id 0A3221C70F24FB45833433255569204D1F80F999@G01JPEXMBYT05
Whole thread Raw
In response to Re: [HACKERS] Statement-level rollback  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
From: Craig Ringer [mailto:craig@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
whenthey 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
theburdon 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

pgsql-hackers by date:

Previous
From: "Tsunakawa, Takayuki"
Date:
Subject: Re: [HACKERS] Statement-level rollback
Next
From: Catalin Iacob
Date:
Subject: Re: [HACKERS] Patch: add --if-exists to pg_recvlogical