Thread

1. Re: Abort transaction on duplicate key error

   Christof Petig <christof@petig-baender.de> — 2001-09-27T10:47:58Z

   Haller Christoph wrote:
   
   > 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".
   
   Simply select by the key you want to enter. If you get 100 an insert is ok,
   otherwise do an update. Oracle has a feature called 'insert or update' which
   follows this strategy. There also was some talk on this list about
   implementing this, but I don't remember the conclusion.
   
   BTW: I strongly recommend staying away from autocommit. You cannot
   control/know whether/when you started a new transaction.
   
   Christof
   
   PS: I would love to have nested transactions, too. But no time to spare ...
   Perhaps somebody does this for 7.3?
   
   
   
   

2. Abort transaction on duplicate key error

   Christoph Haller <ch@rodos.fzk.de> — 2001-09-27T11:27:09Z

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

3. Re: Abort transaction on duplicate key error

   Christoph Haller <ch@rodos.fzk.de> — 2001-09-27T12:59:47Z

   Thanks a lot. Now that I've read your message, 
   I wonder why I was asking something trivial. 
   Christoph
   
   > > 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".
   > 
   > Simply select by the key you want to enter. If you get 100 an insert is ok,
   > otherwise do an update. Oracle has a feature called 'insert or update' which
   > follows this strategy. There also was some talk on this list about
   > implementing this, but I don't remember the conclusion.
   > 
   > BTW: I strongly recommend staying away from autocommit. You cannot
   > control/know whether/when you started a new transaction.
   > 
   > Christof
   > 
   > PS: I would love to have nested transactions, too. But no time to spare ...
   > Perhaps somebody does this for 7.3?
   > 
   
   
   

4. Re: Abort transaction on duplicate key error

   Barry Lind <barry@xythos.com> — 2001-09-27T17:04:07Z

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