Re: Avoid transaction abot if/when constraint violated

On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
> Hello !
>
>
>
> I have a long list of records I want to insert into a table in such a way as
> I can trap and report any/all constraint violations before rolling back (or
> opting to commit).  Unfortunately, after I hit the first constraint
> violation, it aborts the transaction, and then reports “ERROR: current
> transaction is aborted, commands ignored until end of transaction block”.

You're probably thinking in terms of how other databases work. For
many dbs, an aborted transaction can have a very high cost (usually in
terms of rollback) so there was a lot of work put into allowing you to
work around these errors and so on. In Pgsql an aborted transaction
has a fairly low cost. Insert 10,000 rows, change your mind, roll is
back, and you're done, no waiting for the rollback to happen, it's
immediate.

Because of this, the transactional semantics in pgsql are very simple.
Begin a transaction, get an error, abort. They've been made a bit
more robust with the introduction of save points, which allow you to
roll a transaction back to the latest savepoint and start again from
there without losing all the work from before that savepoint.

However, savepoints aren't free, or even necessarily cheap. Setting
one and releasing it before each statement makes your overall
transaction quite slow.

If you're trying to massage data to get it into a format that will
insert into a table, a preferred method for me is to put it into a
load table, then check to see if the rows there pass, and if they
don't delete or change them to fit.