Re: MERGE vs REPLACE - Mailing list pgsql-hackers

Interesting approach.  Actually, we could tell the user they have to use
BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
already have a table lock.

---------------------------------------------------------------------------

Rick Gigger wrote:
> I agree.  I would never ever ever want it to silently start doing  
> table locks.  I would simply avoid using merge at all if that was a  
> possibility.
> 
> However it seems like the idea is to eventually flesh out full  
> fledged merge.  And to do that it sounds like you would need to do  
> one of the following:
> 
> 1) implement predicate locking beyond the simple "match on unique  
> index" case that we have here
> 2) do full table locks.
> 
> It sounds like #1 isn't going to happen for a while.  So in order to  
> do more complicated merges you will need to do #2.  If you are going  
> to implement more complicated merge functionality I certainly  
> wouldn't want it throwing a warning telling me about a table lock if  
> I had already knew it would get the table lock and decided I wanted  
> to go ahead with using merge anyway.
> 
> Could you let the user create the lock himself to handle this  
> situation?  For instance:
> 
> analyze the merge
> if merge condition matches unique index
>     merge without  table locking
> elseif needed table lock already exists
>     merge
> else
>     throw an error
> 
> You could also just add something to the merge syntax like ALLOW  
> TABLE LOCK or something.  The idea is just that the user can  
> explicitly allow the table lock and thus the more complicated merge.
> 
> I don't really know anything about the implementation details but  
> that is the behavior that I would prefer.  That way I could always do  
> a complicated merge if I wanted to but there is no way it would ever  
> do an implicit table lock on me.  And it would never throw an error/ 
> warning unless I actually did something questionable.
> 
> Does that make sense.
> 
> Rick Gigger
> 
> On Nov 16, 2005, at 7:49 AM, Tom Lane wrote:
> 
> > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >>> We should probably throw a notice or warning if we go to a table  
> >>> lock,
> >>> too.
> >
> >> That's not very useful, because you can only do somethign about it  
> >> AFTER
> >> the 1 hour exclusive lock merge has already run :)
> >
> > We shouldn't do anything remotely like that.  A statement whose  
> > locking
> > effects can't be predicted on sight is horrid both from the user's
> > viewpoint and from the implementation viewpoint.  In particular, if we
> > have to do planning before we can determine whether the table needs  
> > just
> > a SELECT lock or something stronger, then we have to take a weak  
> > lock to
> > do the planning and then we are faced with upgrading to the stronger
> > lock at runtime.  Can you say "deadlock risk"?
> >
> > I think we should do REPLACE-like functionality that simply fails  
> > if the
> > match condition isn't equality on a primary key.  If we can use SQL- 
> > spec
> > MERGE syntax for this, that's fine, but let's not think in terms of
> > silently changing to a stronger table lock and a much slower
> > implementation when the condition isn't a primary key.  That's a whole
> > lot of work that isn't solving any real-world problems, and *is*
> > creating a foot-gun for people to cause themselves performance and
> > deadlock problems anytime they spell the WHERE condition slightly  
> > wrong.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of  
> > broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that  
> > your
> >        message can get through to the mailing list cleanly
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073