Re: Current transaction is aborted, commands ignored until end of transaction block - Mailing list pgsql-sql
| From | Samuel Gendler |
|---|---|
| Subject | Re: Current transaction is aborted, commands ignored until end of transaction block |
| Date | |
| Msg-id | CAEV0TzA+9=JsHbC__HZd2ztzQPEaxXDCx1TgE4N2Xye2JK8tEg@mail.gmail.com Whole thread Raw |
| In response to | Re: Current transaction is aborted, commands ignored until end of transaction block (Scott Marlowe <scott.marlowe@gmail.com>) |
| Responses |
Re: Current transaction is aborted, commands ignored until
end of transaction block
Re: Current transaction is aborted, commands ignored until end of transaction block |
| List | pgsql-sql |
On Fri, Dec 30, 2011 at 4:03 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Dec 30, 2011 at 4:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Having used PostgreSQL a LOT, I find that being able to throw an
> Jan Bakuwel <jan.bakuwel@greenpeace.org> writes:
>> Why-o-why have the PostgreSQL developers decided to do it this way...?
>
> Because starting and cleaning up a subtransaction is an expensive thing.
> If we had auto-rollback at the statement level, you would be paying that
> overhead for every statement in every transaction, whether you need it
> or not (since obviously there's no way to forecast in advance whether a
> statement will fail). Making it depend on explicit savepoints allows
> the user/application to control whether that overhead is expended or
> not.
>
> If you want to pay that price all the time, there are client-side
> frameworks that will do it for you, or you can roll your own easily
> enough. So we do not see it as a big deal that the database server
> itself doesn't act that way.
entire update at the db and having it fail / be rolled back / CTRL-C
out of and fix the problem is actually much less work than the
frameworks for other databases. Once you've chased down bad data in a
load file a few times, it's really pretty easy to spot and fix these
issues and just run the whole transaction again. Since PostgreSQL
doesn't have a very big penalty for rolling back a whole transaction
it's not that bad. Some dbs, like MySQL with innodb table handler
have a 10:1 or greater penalty for rollbacks. Insert a million rows
in innodb then issue a rollback and go get a sandwich. In PostgreSQL
a rollback is generally instantaneous, with the only real cost being
bloat in the tables or indexes.
More to the point - if a statement is truly independent of all the other statements in a transaction, it would seem that the transaction itself is poorly defined. The whole point of a transaction is to define an atomic unit of work. If you don't care about atomicity, enable auto commit and just catch the constraint violation exception and continue on your merry way. Yes, on occasion, working around the way postgresql functions causes extra work for a developer (I don't think anyone is suggesting that it should change the end user experience, as was sort-of implied by one response on this thread), but so too can code which is not atomic cause extra work for a developer - and transactions are intended to be atomic, so it makes far more sense to me to implement it the postgres way and incur the modicum of extra developer overhead in the few cases where I may want to deal with acceptable constraint violations rather than in the many cases where I want a transaction to be atomic.
In the example of users adding a new value to an enumerated list in the same unit of work as other rows are inserted in, it is likely not too much work to use a trigger to check the insert prior to executing it - assuming that list is in another table with just a foreign key going into the table the majority of your inserts are going to. Alternatively, if you aren't doing a bulk insert via a copy, it probably isn't too much work to construct the set of inserts needed for the joined table separately and issue those in separate transactions before doing the main transaction.