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

On Apr 30, 2013, at 4:00 PM, "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> wrote:

> 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'
> )
>

I know you said that it was the only active application at the time, but I
figured I'd share my experience anyway…

We do a somewhat more involved version of this to provide fake UPSERT functionality,
and this failure mode happened more often than we'd guessed it would (due to concurrent updates).

However, new in 9.1 is SSI -- http://wiki.postgresql.org/wiki/Serializable
which means that if you run this transaction at isolation level SERIALIZABLE you will get
serialization failures instead of duplicate key exceptions, which makes it easy to retry until success.

So now we run any code that looks like this at SERIALIZABLE and are very happy with it.

Not sure if that'll help, but hopefully!

Best,
Steven

>
> -----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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general