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

On 02/03/2011 08:23 PM, Tom Lane wrote:
> Mage<mage(at)mage(dot)hu> writes:
>> The main question is that isn't "insert into ... select ... where not
>> exists" atomic?
> 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).

Mage