Re: [HACKERS] I am interested in the MERGE command implementation as my gSoC project
On Tue, Mar 30, 2010 at 3:26 AM, Zhai Boxuan bxz...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] I am interested in the MERGE command implementation as my gSoC project
To whom may concern, My name is Zhai Boxuan, a student from China. I am now a Master Student of National University of Singapore. And, before I came to Singapore, I have got another master degree in Wuhan University. In that period, I focus mainly on implementing a novel Object-oriented database model on the postgresql 7.4. I am have great interest on the 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. Thank you very much! Yours Boxuan -- Best Wishes Yours Boxuan -- Best Wishes Yours Boxuan
Re: [HACKERS] I am interested in the MERGE command implementation as my gSoC project
On 30 March 2010 08:26, Zhai Boxuan bxz...@gmail.com wrote: 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. You may find these to be some help if you haven't already read them: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01890.php http://archives.postgresql.org/pgsql-hackers/2008-04/msg01157.php Regards Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
