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

On Mon, May 6, 2013 at 11:19 AM, Carlo Stonebanks
<stonec(dot)register(at)sympatico(dot)ca> wrote:
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jasen Betts
> Sent: May 4, 2013 7:44 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?
>
> On 2013-05-01, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> wrote:
>> There are no client poolers (unless pgtcl has one I don't know about)
>> so this is unlikely.
>>
>> The trigger is an interesting idea to try if it happens again - I
>> can't keep it for long as it is for a massive cache (used to deflect
>> calls to a web
>> service) and will bloat the logs pretty quickly.
>>
>> I have to ask myself, is it more likely that I have discovered some PG
>> anomaly in 9.0 that no one has ever noticed, or that the client has
>> accidentally launched the process twice and doesn't know it?
>
>>> If it's running on a web server and two clients send the same request simultaneously could that cause it to try the >> same task twice simultaneously?
>
> It would, but it's not running on a web server. It's a kind of dedicated data warehouse with very limited access. No programs have the right to write to the tables other than the one in question.

If this is more of OLAP type machine and adjusting logs is not
practical, I would consider staging the records to be input into a
pre-processing table. Then something periodically scoops the records
up and moves them to the 'real' table while purging duplicates. the
preproc table can record the client ip and backend pid -- that way we
can catch the culprit (which is almost certainly not what the client
thinks it is).

Another less invasive way to do that is to simply have the 'before'
trigger log the operation to a (i'd make it unlogged) 'log' table that
records the insert operation and who did it (backend pid, client ip,
etc) with the time.

merlin