Re: I am interested in the MERGE command implementation as my gSoC project - Mailing list pgsql-hackers

On Tue, Mar 30, 2010 at 3:26 AM, Zhai Boxuan <bxzhai@gmail.com> wrote:
> To whom may concern,
> projects provided by postgres in google Summer Code of this year. I think
> the MERGE command in TO DO list is a suitable topic for me.
> I have read some infor about the MERGE command, which has not been
> implemented yet in Postgres 8. I considered the problem and have a brief
> plan for the jobs.
>
> 1 We need to update the backend/parser/gram.y for adding the SQL style MERGE
> command in the parser. I can do this, since it is similar to what I was
> doing in China. One new “MergeStmt” structure should be designed to hold the
> transformed command information.
> The structure definitely need: one SelectStmt to hold the subquery of the
> source table, a list of expressions for the MATCH condition. Yet some other
> expression lists are needed for specifying the additional match and/or not
> match conditions.
> It is relatively easy to implement since we can reuse many components of the
> SELECT command.
>
> 2. In the Analyze.c file we need to add a function to transform this
> MergeStmt into a Query node.
> It is necessary to add a new command type for MERGE, which is a plannable
> command.
> We need to check the semantics correctness of the statement. What I am
> thinking about is to combine the target table and the source table as a
> whole SELECT query.
> If there is no NOT MATCH option, we can generate a normal query node of
> something like “SELECT * FROM target, source WHERE match-condition;” or , We
> have to do a cross join if we want to handle some NOT MATCH actions, which
> will do a query like “SELECT * FROM target, source;”
> The benefit is that we can almost fully reuse the rewriter and planner to
> transform this generated query as an executor-accepting structure.
>
> 3. A plan is need for the query. The planner should accept this new
> plannable command. However, as motioned above, the real work will be: do a
> traditional query plan on the formatted select query based on the target and
> source table. Then pack this plan with a outer planner node, which is
> designed for MERGE command specifically.
>
> 4. How to execute the query? I am still not very clear. The basic idea is
> for each returned tuple of the select query we generated above (the tuple
> contains all the attributes in both source and target table) we can test it
> with MATCH and/or NOT MATCH conditions, and do corresponding actions base
> the testing result.
>
> I believe there are some problems will encounter especially for the
> transaction things. And I am also not sure about whether the UPDATE, INSERT
> and DELETE operations for previous output tuple will affect the remaining
> join processing.
> Hope you can help me on improving this rough idea. Or, if you are not
> convenient, please kindly forward this letter to who may concern it.

This is a big project.  If you're not already familiar with the
internals of the planner and executor, you're not going to be able to
finish this in one summer, or even come close.  I would expect a patch
like this to take a year or more to get merged, even with someone very
knowledgeable on it.  You might want to set your sights on something a
little less ambitious.

See also:

http://archives.postgresql.org/pgsql-hackers/2010-03/msg01034.php
http://archives.postgresql.org/pgsql-hackers/2010-03/msg01116.php

...Robert