Re: [GENERAL] Avoid transaction abot if/when constraint violated
Gauthier, Dave dave.gauth...@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 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? 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Avoid transaction abot if/when constraint violated
Ya, I don't mind that it eventually fails (why have constraints otherwise), but I'd like to see all the constraint violations for the set of records. I actually have something working. I'm coding in perl/DBI, and I just rollback after each constraint violation and keep going. Nothing from the entire stream is committed until/unless they're all clean. Thanks -Original Message- From: Joshua D. Drake [mailto:j...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Avoid transaction abot if/when constraint violated
On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave dave.gauth...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Avoid transaction abot if/when constraint violated
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
