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

Very good to know, Steve. We're on 9.0 right now but I will investigate as
all the work is for unattended automatic processes which are continuously
streaming data from multiple resources and need to resolve these collisions
by themselves.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steven Schlansker
Sent: April 30, 2013 7:10 PM
To: Carlo Stonebanks
Cc: 'Tom Lane'; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

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