Re: MERGE vs REPLACE
От | Rick Gigger |
---|---|
Тема | Re: MERGE vs REPLACE |
Дата | |
Msg-id | A65FDC57-19B6-47E4-823A-77E639971DF7@alpinenetworking.com обсуждение исходный текст |
Ответ на | Re: MERGE vs REPLACE (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: MERGE vs REPLACE
(Jaime Casanova <systemguards@gmail.com>)
Re: MERGE vs REPLACE (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-hackers |
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 indexmerge without table locking elseif needed table lock already existsmerge elsethrow 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 >
В списке pgsql-hackers по дате отправления: