Re: MERGE vs REPLACE

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(at)familyhealth(dot)com(dot)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(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073