Re: 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:jd(at)commandprompt(dot)com]
Sent: Thursday, January 14, 2010 6:35 PM
To: Gauthier, Dave
Cc: pgsql-general(at)postgresql(dot)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.