Re: Current transaction is aborted, commands ignored until end of transaction block

On Fri, Dec 30, 2011 at 4:03 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Fri, Dec 30, 2011 at 4:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Jan Bakuwel <jan(dot)bakuwel(at)greenpeace(dot)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.
>
> Having used PostgreSQL a LOT, I find that being able to throw an
> 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.