Re: MERGE Specification

Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
>> Perhaps a better option would be to implement Merge per spec, and then
>> implement a "replace into" command for the oltp scenario. This way you keep
>> the spec behavior for the spec syntax, and have a clearly non-spec command
>> for non-spec behavior.
>
> In that case, it's a fair question to ask just who will use the "spec"
> syntax. As far as I can tell from years of watching the mailing lists,
> there is plenty of demand for a concurrent-safe insert-or-update
> behavior, and *exactly zero* demand for the other. I challenge you to
> find even one request for the "spec" behavior in the mailing list
> archives. (Simon doesn't count.)

While I agree that there is zero demand for anything else then "UPSERT"
version of MERGE INTO, I don't think that doing the *exact opposite* of
what SQL standard says without any change of syntax to clearly
differentiate the behavior is best thing to do.

Another thing is, the table on which we do SELECT (the one in USING) can
be different from target table and we can use columns from that table in
INSERT/UPDATE statement (probably one the reasons why spec says the
"SELECT" query has to be executed before any changes). How you want to
use the "INSERT first" implementation in this scenario ? IMHO you still
need to have both implementations in the end. So we probably need to
implement the standard one first and then implement our version and put
some restrictions of what can be in USING or INSERT part when using it.

Regards
Petr Jelinek
--
Regards
Petr Jelinek (PJMODOS)