Thread: UNIQUE constraint violations and transactions

Hi!

I've got a UNIQUE constraint on a field, and obviously, when I try to
insert a duplicate value, I get a WARNING via psql (and an Exception via
the JDBC drivers) saying I tried to violate the constraint.  No biggie.
This is what I expect.

The tricky part comes in when I violate the constraint inside a
transaction.  It seems once a WARNING happens inside a transaction you
can't do anything else in the transaction.  You can only COMMIT or
ROLLBACK.  In my situation, it's not fatal that I tried to insert a
duplicate value...  I just want to continue on in the transaction.

I realize this is by design, but I'm wondering if there's a
configuration option (compile-time or otherwise) that will let me
continue to do stuff inside the transaction, even after a WARNING.

Another way to ask this might be:  Is it more efficient to blindly
INSERT the value, and let postgres throw the Exception, or to SELECT for
it first, then INSERT only if the SELECT returns zero rows?  ie:

   try
     INSERT INTO words (word) VALUES ('foo');
   catch (Constraint violation)
      COMMIT   // this bugs me because I don't want
      BEGIN    // to commit the transaction yet
   end
                v/s

   SELECT word_id FROM words WHERE word='foo';
   if (resultset size == 0)
       INSERT INTO words (word) VALUES ('foo');
   end


eric

On Fri, 26 Oct 2001, Eric Ridge wrote:

> Hi!
>
> I've got a UNIQUE constraint on a field, and obviously, when I try to
> insert a duplicate value, I get a WARNING via psql (and an Exception via
> the JDBC drivers) saying I tried to violate the constraint.  No biggie.
> This is what I expect.
>
> The tricky part comes in when I violate the constraint inside a
> transaction.  It seems once a WARNING happens inside a transaction you
> can't do anything else in the transaction.  You can only COMMIT or
> ROLLBACK.  In my situation, it's not fatal that I tried to insert a
> duplicate value...  I just want to continue on in the transaction.

It depends on what you plan to do after the insert failed. In my case I
often want to insert a value if the primary key does not exist, or
update the tuple if it already exists. In this case, the simplest way to
do it is to UPDATE it and if the number of affected tuples is 0,
INSERT. I believe it's also more efficient, since it's done in one
transaction (you don't have to rollback and open a new one).

Another way to do it could be SELECT count WHERE ... and if it's zero,
INSERT, else choose a new number.

I think in both cases you may need "transaction isolation level
serializable" if you want to be very strict. You may end up with
rollbacked transaction anyway...

--
Alvaro Herrera (<alvherre[@]atentus.com>)
"La conclusion que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusion de ellos" (Tannenbaum)

> It depends on what you plan to do after the insert failed. In
> my case I
> often want to insert a value if the primary key does not exist, or
> update the tuple if it already exists. In this case, the
> simplest way to
> do it is to UPDATE it and if the number of affected tuples is 0,
> INSERT. I believe it's also more efficient, since it's done in one
> transaction (you don't have to rollback and open a new one).

I see what you mean.  In my case, if the INSERT fails I just want to
keep going.  I don't really care if I can't INSERT again (but I do care
if I can't insert the first time!).

> Another way to do it could be SELECT count WHERE ... and if it's zero,
> INSERT, else choose a new number.

That was an option I was exploring...

>
> I think in both cases you may need "transaction isolation level
> serializable" if you want to be very strict. You may end up with
> rollbacked transaction anyway...

... but I ended up restructing my transaction into 2 parts.  1
transaction for the data, and a bunch of small atomic INSERTS against
the table with the UNIQUE constraint.  Overall, it's probably slower
this way, but my code is a little easier to follow.

thanks for your time!

eric