Re: MERGE vs REPLACE

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