Re: Simple SQL INSERT to avoid duplication failed: why?

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> writes:
> The only way I can see this happening is that an
> acache_mdx_logic_address_validation sneaks in before the insert and after
> the NOT EXISTS... SELECT. And for that to occur, the client must be mistaken
> and something else MUST be running and inserting into
> acache_mdx_logic_address_validation.

That seems like the way to bet to me. Note that the window can actually
be wider than it might seem because of MVCC considerations --- for
example, if the conflicting insert has been done by an uncommitted
transaction, the EXISTS will fly right past the uncommitted row, but
then the index insertion will wait patiently to see if the conflicting
row commits, whereupon it will throw the error.

regards, tom lane