Re: isn't "insert into where not exists" atomic?

Mage <mage(at)mage(dot)hu> writes:
> On 02/03/2011 08:23 PM, Tom Lane wrote:
>> No, it isn't: it *will* fail in the presence of other transactions doing
>> the same thing, because the EXISTS test will only see rows that
>> committed before the command started. You might care to read the
>> manual's chapter about concurrency:
>> http://www.postgresql.org/docs/9.0/static/mvcc.html
> Thank you, Tom. I will read that.

> However I googled a bit before written this trigger and I would like to
> ask you: what is the best practice for doing "insert or update"-like
> thing, especially in this case, in trigger? I would use lock table from
> now. Is it the recommended way?

> (I just don't like the "insert -> on exception -> update" method).

AFAIR the basic alternatives are insert -> exception -> update or
taking a lock at the table level. The latter is simpler and cleaner
but distinctly worse for concurrent-insert performance, especially if
you can't keep the transactions very short. Pick your poison ...

regards, tom lane