Re: functions, transactions, key violations

On Jun 4, 2008, at 6:00 PM, Jeff Davis wrote:

>> Some other concurrent transaction could still
> insert something after the UPDATE but before the INSERT, so the unique
> constraint violation can still occur.

Yes, I saw the comment. I'm guessing I'm missing something wrt
transaction isolation level or locking. Would I need to use
SERIALIZABLE or some kind of locking? Is the function in the example
any different than the following explicit transaction (with the UPDATE
not affecting any rows)?

BEGIN;
UPDATE db SET b = data WHERE a = key;
INSERT INTO db(a,b) VALUES (key, data);
COMMIT;

I'm obviously on the cusp of learning something new, or understanding
concurrency more deeply, so I'm looking forward to your responses.

Cheers,

Michael Glaesemann
grzm seespotcode net