Re: Automatic rollback on error, duplicate key?

On Monday 11 November 2002 08:04 pm, [email protected] wrote:
<snip>
> Can someone point me in the right direction here? I basically want to
> do:
>
>             open a new transaction
>
>             insert row into table 1
>
>             insert multiple rows into table 2
>                these inserts may be duplicates
>
>             insert multiple rows into table 3, each of which is tied
>             back to table 1 by an ID.
>
>             commit the transaction
>
> For all statements except the inserts into table 2, if I get an error
> I would like to rollback the transaction.
<snip>

Well, I think that PostgreSQL is behaving exactly as expected by an RDBMS 
performing a transaction. I use a similar technique in a Java app, but only 
for the part involving the inserts into row 2. However, the key difference is 
that I'm able to make each insert a separate transaction.

Perhaps you could use a rule to achieve what you want. You could create a 
clone of table 2 augmented with a rule that checks to see if the row you just 
inserted into the clone exists already in table 2. If so, it just drops the 
new row. If not, it inserts the row into table 2. Basically, the clone table 
just consumes each row inserted into it. You replace the "insert multiple 
rows into table 2" part with "insert multiple rows into table 2's clone." I'm 
not sure this approach will work, but it seems to be worth a try.

Regards,

.... Bob


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf