Jan Janak wrote: > Maxim Sobolev wrote: >> Jan Janak wrote: >>> * No support for INSERT OR UPDATE (or REPLACE in MySQL). PostgreSQL does >>> not seem to support "insert or update" type of queries, so I will have >>> to re-implement it using transactions somehow. I am not yet sure >>> exactly how, so suggestions/patches/code are welcome. >> Well, I think you can simply run DELETE followed by INSERT within >> transaction block to emulate that. The outside world will not see any >> changes until transaction completes, which should provide the same >> functionality as "INSERT OR UPDATE", i.e.: >> >> BEGIN; >> DELETE FROM location WHERE ... LIMIT 1; >> INSERT INTO location ...; >> END; >> >> If INSERT fails there will be no changes to the database. > > Unfortunately it is more complex than that. REPLACE only deletes an > old row if it stands in the way of the new row. In other words an old > row will only be deleted if the new insert generates a primary key or > unique index violation. > > The code above would delete the old row even if it does not stand in > the way of the new insert. This could happen in acc or missed_calls > tables, for example, which has been designed without any such > constrains to make sure that inserts never fail.
Well I think the whole idea "delete an old row if it stands in the way based on index" seems to be very dangerous. What if the new row "stands in the way" of several rows (tables can have multiple indexes and/or indexes that involve multiple fields)? Should all of them be deleted? IMHO the code should explicitly specify which fields should match in order to warrant replacing old record with the new one, in which case the DELETE/INSERT bundle should work just fine. Regards, -- Maksym Sobolyev Sippy Software, Inc. Internet Telephony (VoIP) Experts T/F: +1-646-651-1110 Web: http://www.sippysoft.com _______________________________________________ Serdev mailing list [email protected] http://lists.iptel.org/mailman/listinfo/serdev
