Re: Abort transaction on duplicate key error - Mailing list pgsql-hackers

From Barry Lind
Subject Re: Abort transaction on duplicate key error
Date
Msg-id 3BB35C07.7000008@xythos.com
Whole thread Raw
In response to Abort transaction on duplicate key error  (Haller Christoph <ch@rodos.fzk.de>)
List pgsql-hackers
Haller,

The way I have handled this in the past is to attempt the following 
insert, followed by an update if the insert doesn't insert any rows:

insert into foo (fooPK, foo2)
select 'valuePK', 'value2'
where not exists  (select 'x' from foo   where fooPK = 'valuePK')

if number of rows inserted = 0, then the row already exists so do an update

update foo set foo2 = 'value2'
where fooPK = 'valuePK'

Since I don't know what client interface you are using (java, perl, C), 
I can't give you exact code for this, but the above should be easily 
implemented in any language.

thanks,
--Barry



Haller Christoph wrote:

> Hi all, 
> Sorry for bothering you with my stuff for the second time 
> but I haven't got any answer within two days and the problem 
> appears fundamental, at least to me. 
> I have a C application running to deal with meteorological data 
> like temperature, precipitation, wind speed, wind direction, ... 
> And I mean loads of data like several thousand sets within every 
> ten minutes. 
>>From time to time it happens the transmitters have delivered wrong data, 
> so they send the sets again to be taken as correction. 
> The idea is to create a unique index on the timestamp, the location id 
> and the measurement id, then when receiving a duplicate key error 
> move on to an update command on that specific row. 
> But, within PostgreSQL this strategy does not work any longer within 
> a chained transaction, because the duplicate key error leads to 
> 'abort the whole transaction'. 
> What I can do is change from chained transaction to unchained transaction, 
> but what I have read in the mailing list so far, the commit operation 
> requires loads of cpu time, and I do not have time for this when 
> processing thousands of sets. 
> I am wondering now whether there is a fundamental design error in 
> my strategy. 
> Any ideas, suggestions highly appreciated and thanks for reading so far. 
> Regards, Christoph 
> 
> My first message:
> In a C application I want to run several 
> insert commands within a chained transaction 
> (for faster execution). 
>>From time to time there will be an insert command 
> causing an 
> ERROR:  Cannot insert a duplicate key into a unique index
> 
> As a result, the whole transaction is aborted and all 
> the previous inserts are lost. 
> Is there any way to preserve the data 
> except working with "autocommit" ? 
> What I have in mind particularly is something like 
> "Do not abort on duplicate key error".
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 
> 




pgsql-hackers by date:

Previous
From: Martín Marqués
Date:
Subject: Re: [PHP] [BUGS] PostgreSQL / PHP Overrun Error
Next
From: Neil Padgett
Date:
Subject: Re: Spinlock performance improvement proposal