On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave <dave.gauthier@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
