Re: Problem with aborting entire transactions on error

"Kevin Grittner" <kgrittn(at)mail(dot)com> writes:
> Zbigniew wrote:
>> No idea, is it simple or very complicated. But if it is simple -
>> why not?

> When I first started using PostgreSQL I was used to a database
> product which would roll back an entire statement on failure, but
> leave the transaction open for another try. This is handy for
> interactive work, which is why psql (an interactive tool) gives you
> an easy way to do it, using subtransactions behind the scenes.

FWIW, we've generally concluded that this sort of thing should be done
on the client side, ie, exactly like psql does it. Fooling with
transactional behavior on the server side poses far too much risk of
breaking clients that aren't expecting the new behavior. We learned an
object lesson on that point back in 7.3, when we attempted to implement
autocommit on/off behavior on server side, using a GUC setting to control
it. (Autocommit isn't exactly what the OP is asking for, but it's close
enough that the lesson holds.) Basically, turning the setting to the
non-default state broke just about every nontrivial client program.
The push-back was fierce enough that the setting went away again in 7.4,
and it won't be coming back as long as anybody who remembers that fiasco
is still around the project. So now we have AUTOCOMMIT (and ON_ERROR_STOP
too) as psql behaviors, where they're available for interactive use but
won't break non-interactive program logic.

The complexity and performance costs that people have mentioned are other
good reasons not to change it; but even if the change were free on the
server side, history says it's not something we ought to mess with.

regards, tom lane