Обсуждение: 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.

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