Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid

On Tue, Jan 04, 2011 at 07:02:54PM +0200, Marko Tiikkaja wrote:
> On 2011-01-04 6:27 PM, David Fetter wrote:
> >On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
> >>Heikki Linnakangas wrote:
> >>>You can of course LOCK TABLE as a work-around, if that's what you want.
> >>
> >>Presuming the code quality issues and other little quirks I've
> >>documented (and new ones yet to be discovered) can get resolved
> >>here, and that's a sizeable open question, I could see shipping this
> >>with the automatic heavy LOCK TABLE in there.  Then simple UPSERT
> >>could work out of the box via a straightforward MERGE.
> >
> >How about implementing an UPSERT command as "take the lock, do the
> >merge?"  That way, we'd have both the simplicity for the simpler cases
> >and a way to relax consistency guarantees for those who would like to
> >do so.
> 
> That, unfortunately, won't work so well in REPEATABLE READ :-(

There are caveats all over READ COMMITTED/REPEATABLE READ/SNAPSHOT.
The only really intuitively obvious behavior is SERIALIZABLE, which
we'll have available in 9.1. :)

> But I, too, am starting to think that we should have a separate,
> optimized command to do UPSERT/INSERT .. IGNORE efficiently and
> correctly while making MERGE's correctness the user's
> responsibility.  Preferably with huge warning signs on the
> documentation page.

+1 for the HUGE WARNING SIGNS :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate