Re: gSoC - ADD MERGE COMMAND - code patch submission

On 16/07/10 12:26, Boxuan Zhai wrote:
> For the EXPLAIN MERGE command, I expect it to return a result similar to
> that of a SELECT command.
>
> I think the EXPLAIN command is to show how the tables in a query is scaned
> and joined. In my design, the merge command will generate a top-level query
> (and plan) as the main query. It is in fact a left join select query over
> the source and target tables. This main query (plan) decides how the tables
> are scanned. The merge actions will not effect this process. So when we
> explain the merge command, a similar result will be returned.
>
> For example the command
> EXPLAIN
> MERGE INTO Stock USING Sale ON Stock.stock_id = Sale.sale_id
> WHEN MATCHED THEN UPDATE SET balance = balance + sale.vol;
> WHEN ....
> .....
>
> Will return a result just like that of the following command:
>
> EXPLAIN
> SELECT * FROM Sale LEFT JOIN Stock ON stock_id = sale_id;

You really need to look at the changes in 9.0 in this area, you now have
a Update/Delete/Insert node (implemented in
src/backend/executor/nodeModifyTable.c) at the top of the plan for
update/insert/delete commands:

postgres=# explain UPDATE foo SET id = 456 WHERE id = 123;
QUERY PLAN
-----------------------------------------------------------
Update (cost=0.00..40.00 rows=12 width=6)
-> Seq Scan on foo (cost=0.00..40.00 rows=12 width=6)
Filter: (id = 123)
(3 rows)

I would expect there to be a Merge node similar to that, with
Update/Insert/Delete subnodes for each action.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com