Jan Janak wrote: > Maxim Sobolev wrote: >> 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. > > Yes, this is how REPLACE works. It guarantees that the new data will be > written into the database by removing all rows that stand in its way. > There seems to be a new construct called MERGE in one of the newer > revisions of the SQL standard but PostgreSQL does not support it yet.
Thanks for the explanation. My point is that such construct is dangerous in the presence of programmatic errors and/or unexpected data in the database. For example here we use some SER tables that have few extra fields and few extra indexes/constrains to enforce data integrity. Instead of generating SQL error in the case when SER for some reason (i.e. coding error, unexpected input data, etc) is trying to insert records that break that integrity, the database server would silently remove possibly unrelated data. IMHO the SER's code should explicitly specify which fields should be matched to locate rows that need to be replaced - relying on index for this is a bad thing. 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
