Re: transaction error handling

Hi Kevin,
Thank you, that is very helpful.
I am not worried about the implicit commits. The "no implicit savepoint" was more of an issue, since it created a necessity to create and destroy savepoints per each sql statement to capture any statement level error without losing a transaction, that approach has prohibitive performance repercussions.
I will check out the ON_ERROR_ROLLBACK feature.
Thank you,
Sincerely,
Kasia

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Tuesday, November 29, 2011 10:55 AM
To: Kasia Tuszynska; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] transaction error handling

Kasia Tuszynska <ktuszynska(at)esri(dot)com> wrote:

> Oracle:
> Begin transaction
> Insert - no error
> Implicit savepoint
> Insert - error raised
> Implicit rollback to the savepoint, no transaction loss, error
> raised on the insert statement that errored out.
> End transaction, implicit commit, with the single error free
> insert.
>
> Postgres:
> Begin transaction
> Insert - no error
> Insert - error raised
> Transaction loss = no implicit rollback to the single error free
> insert.
>
> Is this a correct interpretation of the Postgres transaction error
> handling?

Well, in psql you can set ON_ERROR_ROLLBACK so that each statement
will be automatically preceded by a SAVEPOINT which will be
automatically rolled back if the statement has an error. There are
various constructs for accomplishing this in supported PLs,
depending on the language.

I'm not aware of any "explicitly start a transaction but guess at
whether a commit is intended" feature in PostgreSQL. An explicit
transaction is committed if and when you say so.

-Kevin