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
