Re: Internal design of MERGE, with Rules

On Wed, 2008-04-30 at 16:58 +0100, Simon Riggs wrote:
> The main query will then look like this
>
> select target.ctid
> ,case when-not-matched (as above)
> ,case when-matched (as above)
> ,(all other columns required for side queries)
> from <source-query> left outer join <target> on <join-condition>
> where (<when-matched-condition-0>
> or <when-matched-condition-1>
> ...
> or <when-matched-condition-N>)
> or (<when-not-matched-condition-0>
> or <when-not-matched-condition-1>
> ...
> or <when-not-matched-condition-N>)
>
> The WHERE clause is likely required in case we get queries like this
>
> MERGE target t
> USING (select * from source) s
> ON (s.pkey = t.pkey)
> WHEN MATCHED AND s.pkey = $1
> UPDATE SET col = $2;
>
> which would be perfectly valid, even if we might hope that they had
> coded like this
>
> MERGE target
> USING (select * from source WHERE index-column = $1)
> ON (join-condition)
> WHEN MATCHED
> UPDATE SET col = $2;

Peter has just jogged my memory about double evaluation of volatile
functions, so the above transformation isn't correct.

We would not be able to fully optimise a MERGE statement like this

MERGE target t
USING (select * from source) s
ON (s.pkey = t.pkey)
WHEN MATCHED AND s.key = $1
UPDATE SET col = $2;

since we won't be able to pass the clause "s.pkey = $1" down into the s
query so it would use an index. The following statement will be faster,
but will in all cases give an identical result:

MERGE target t
USING (select * from source WHERE key = $1) s
ON (s.pkey = t.pkey)
WHEN MATCHED
UPDATE SET col = $2;

I don't think its too important, since the latter is the way people
would have used MERGE in SQL:2003 anyway.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com