On 11/11/2005 2:20 PM, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> Tom Lane wrote:
>>> Surely they require a unique constraint --- else the behavior isn't
>>> even well defined, is it?
>
>> They require that the merge condition does not match for more than one
>> row, but since the merge condition can do just about anything, there is
>> no guarantee that a unique constraint encompasses it.
>
> ISTM to be a reasonable implementation restriction that there be a
> constraint by which the system can prove that there is at most one
> matching row. Per other comments in this thread, we'd not be the only
> implementation making such a restriction.
>
> (Certainly, if I were a DBA and were told that the performance of MERGE
> would go to hell in a handbasket if I had no such constraint, I'd make
> sure there was one. I don't think there is very much of a use-case for
> the general scenario.)
Such restriction does look reasonable. Especially because ...
The largest problem I see with MERGE is the question of BEFORE triggers.
Consider a BEFORE INSERT trigger that modifies a third table, after
which the constraint or whatever post-heap_insert-attempt we might use
detects a conflict. How do we undo the actions of the BEFORE trigger?
The only way to do that is to plan the query as a nestloop, with the
USING part as the outer loop. If the (updating) scan of the INTO
relation did not hit any tuple, then do the INSERT. We can only undo the
side effects of any BEFORE trigger by wrapping each and evey nested INTO
relation insert attempt into its own subtransaction.
Sure, we "could" of course do the insert and then rescan the whole thing
with read-committed to see if our row is now the only one ... needless
to say that in the case of a sequential scan inside the loop, that
nestloop will suck big times even without that second scan. But ... hmmm
... we could get away with that and if we don't find a constraint that
will ensure uniqueness, then we do a rescan to check for it. But I would
vote for a "please_no_notice_about_stupid_usage_of_merge" runtime option
that suppresses the corresponding NOTICE.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #