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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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
