On Fri, Nov 18, 2005 at 05:30:34PM +0100, Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > I have update the TODO item to reflect this:
> >
> > * Add MERGE command that does UPDATE/DELETE, or on failure, INSERT
> > (rules, triggers?)
> >
> > To implement this cleanly requires that the table have a unique
> > index so duplicate checking can be easily performed.
>
> We're still trying to work out the semantic relationship between MERGE
> and REPLACE and what-we-actually-want. This entry doesn't seem to take
> that into account.
Right. From my reading of the spec (which someone posted here
somewhere) MERGE has no special rules regarding visibility. So it's
just as susceptable to "duplicate keys" and "lost updates" as any
current method. That doesn't dimish its usefulness, it's just not what
some people thought it was.
My current position is that since REPLACE seems to violate normal
transaction semantics (must not fail no matter what other backends are
doing) that any implementation will probably have to play fancy
footwork with locking and savepoints within a single statement. And
that's not MERGE.
I'd say implement SQL MERGE which doesn't have any really unusual
features. And seperately implement some kind of INSERT OR UPDATE which
works only for a table with a primary key.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.