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

Hi Tom,

>> There's nothing obviously wrong with that, which means the issue is in
something you didn't show us. Care to assemble a self-contained example?
<<

Unfortunately, it happens erratically and very, very rarely so I can't give
you something that will fail. I expected an occasional failure and there is
a try-catch to handle it, I was just surprised when the client told me there
was no other apps running against this table. I just wanted to make sure the
logic was correct and that I wasn't doing something stupid or there is some
known SQL or PG behaviour that would explain this.

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.

Would you agree, or is there anything else to consider?

INSERT INTO
mdx_lib.acache_mdx_logic_address_validation
(
address,
postal_code,
address_id
)
SELECT
'306 station 22 1 2 st' AS address,
'29482' AS postal_code,
100165016 AS address_id
WHERE
NOT EXISTS
( SELECT
1
FROM
mdx_lib.acache_mdx_logic_address_validation
WHERE
address = '306 station 22 1 2 st'
AND postal_code = '29482'
)

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: April 25, 2013 5:06 PM
To: Carlo Stonebanks
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Simple SQL INSERT to avoid duplication failed: why?

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> writes:
> Ok, I tried to be clever and I wrote code to avoid inserting duplicate
data.
> The calling function has a try-catch to recover from this, but I am
> curious as to why it failed:

There's nothing obviously wrong with that, which means the issue is in
something you didn't show us. Care to assemble a self-contained example?

regards, tom lane