Why not just always drop the record to be inserted and insert it. It is very fast..and if the delete fails..it doesn't abort the transaction..
drop from table x insert into table x commit catch all rollback print error message Only type of errors you get here truly are fatal ones...(i.e Postgres Crashed in the middle...or closed the dbhandle for some strange reason..(i.e. doing a rollback from another abandoned process..)) These are the type of errors that can be trapped so that one can take a health measurement, reconnect and pick up where you left off. And is also low level enough that it should work for any db driver..it's also about twice as fast as checking to see whether a record is an insert or an update..if your master database says it's an update, it's a drop and insert, if your master database says it's an insert, it ought to be an insert..no questions...Of course if all you have are peer databases just assume everything is an update and do a drop/insert all the time....(i can't imagine writing code to keep track of which one is right though..I wouldn't want to..) OF course if you have peer databases..you should limit the places updates can come from anyways, and apply updates across the board simulataneously and keep a log of failed updates... I think this addresses both issues..if autocommit is off and each drop/insert combo is a transaction then the programmer can determine whether to retry the operation, or fail and log the error. *********** REPLY SEPARATOR *********** On 12/3/01 at 1:43 PM Joern Reder wrote: >"Peter Haworth" wrote: > >> You can do it with one check: >> >> try >> if( ! update table x ) >> insert into table x >> commit >> catch all >> rollback >> print error messge > >That's correct for the actual example. The problem is: we have many, >many constructions like this in our application, and want to prevent >their adaption for PostgreSQL. Further on we use this in lower level >libraries, which can't make assupmtions about the transactional state or >commit/rollback the actual transaction. > >> Surely your application ought to know whether it should be doing an >insert >> or an update anyway? > >This can easy be done by setting flags, with or without usage of >exception handling. A common case for my example is an import program, >which transparently inserts new or updates existing rows. > >Besides this it is much faster to assume, that the row can be inserted, >and only seldomly do special things if this fails, as always execute a >SQL command for checking on existence, which normally always will fail, >thus I have to regularly execute two statements instead of one. > >It works with every database I know, but not with PostgreSQL. Whose >transaction model is right? > >Joern > >-- >Joern Reder -- Software Development, dimedis GmbH, 50672 Koeln > http://www.dimedis.de/ http://spirit.dimedis.de/ >supporting: http://www.zyn.de/ http://www.netcologne.de/~nc-joernre/ >CPAN: http://www.perl.com/CPAN/modules/by-module/CIPP/JRED/ ********************** 3K ******************************
