Thread: Avoid transaction abot if/when constraint violated
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”.
Is there a way around this?
Thanks in Advance!
On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave 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â. > > > > Is there a way around this? Only if it is a foreign key issue in which case you can defer the check. If it is a single transaction, and you insert a bad record the whole transaction fails. Joshua D. Drake > > > > Thanks in Advance! > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
Ya, I don't mind that it eventually fails (why have constraints otherwise), but I'd like to see all the constraint violationsfor the set of records. I actually have something working. I'm coding in perl/DBI, and I just "rollback" after each constraint violation and keepgoing. Nothing from the entire stream is committed until/unless they're all clean. Thanks -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Thursday, January 14, 2010 6:35 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Avoid transaction abot if/when constraint violated On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave 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". > > > > Is there a way around this? Only if it is a foreign key issue in which case you can defer the check. If it is a single transaction, and you insert a bad record the whole transaction fails. Joshua D. Drake > > > > Thanks in Advance! > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave <dave.gauthier@intel.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.
Gauthier, Dave 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”. > > Is there a way around this? > use savepoints inside the transaction for each insert. your app will have to figure out how to track the errors if it wants to postpone any rollback/commit decision til the end.
On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave 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”. > > > > Is there a way around this? Only if it is a foreign key issue in which case you can defer the check. If it is a single transaction, and you insert a bad record the whole transaction fails. Joshua D. Drake > > > > Thanks in Advance! > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
"Gauthier, Dave" <dave.gauthier@intel.com> writes: > 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 violationsbefore rolling back (or opting to commit). > Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports “ERROR: currenttransaction is aborted, commands ignored until end of > transaction block”. > > Is there a way around this? Either load to another table with no constraint then use it as a source for loading the constrained one, excluding the problematic rows: INSERT INTO ... SELECT * FROM import.table LEFT JOIN ... ; Or try using pgloader once the input format is CSV like rather than INSERT. Regards, -- dim