Re: MERGE vs REPLACE

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
>