Re: gSoC - ADD MERGE COMMAND - code patch submission

Dear Hackers

I considered my situation. And I found that I didn't communicate well with
you, as makes you have little confidence on my project. Most of the time I
just work by myself and not report to you frequently. I always want to
finish a solid stage progress before do a submission. This may be a bad
habit in the remote project.

In fact, I have a detailed design on how to implement the command and I am
working hard these days to catch the schedule.

In my design,
1. the merge command is firstly transformed to a "MergeStmt" node in
parser. And analyzer will generate a left out join query as the top query
(or main query). This query is similar to a SELECT command query, but I set
target relation in it. The top query will drive the scanning and joining
over target and source tables.

The merge actions are transformed into lower level queries. I create a Query
node for each of them and append them in a newly create List field
mergeActQry. The action queries have different command type and specific
target list and qual list, according to their declaration by user. But they
all share the same range table. This is because we don't need the action
queries to be planned latter. The joining strategy is decided by the top
query. We are only interest in their specific action qualifications. In
other words, these action queries are only containers for their target list
and qualifications.

2. When the query is ready, it will be send to rewriter. In this part, we
can call RewriteQuery() to handle the action queries. The UPDATE action will
trigger rules on UPDATE, and so on. What need to be noticed are: 1. the
actions of the same type should not be rewritten repeatedly. If there are
two UPDATE actions in merge command, we should not trigger the ON UPDATE
rules twice. 2. if an action type is fully replaced by rules, we should
remove all actions of this type from the action list.
Rewriter will also do some process on the target list of each action.

The first submission has finished the above part.

3. In planner, the top level query is handled in a normal way. Since it has
almost the same structure as a SELECT query, the planner() function can work
on it straight forward. However, we need a small change here. The merge
command has a target relation, which need a ctid junk attribute in the
target list. The ctid is required by the UPDATE and DELETE actions.

Besides, for each of the action queries, we also need to create a Plan node.
We don't need to do a full plan on the action queries. The crucial point is
to preprocess the target list and qualification of each action. (Explanation
for this point. The execution of a merge action is composed by two parts.
The top plan will be executed in the main loop, and return the joined tuples
one by one. And a action will apply its qualification on the returned
tuples. If succeed, it will take the action and do corresponding
modification on the target table. Thus, even we have a Plan node created for
each action, we don't want to throw it directly into Planner() function.
That will generate a new plan over the tables in Range Table, which is very
probably different with the top-level plan. If we run the action plans
directly, they will be confilict with each other).

I create a function merge_action_planner() to do this job. This part is
added at the end of standard_planner(). After that, all the plans of merge
actions are linked into a new List filed in PlannedStmt result of the top
plan.

4. When planner is finished, the plan will be send to executor through
PortalRun(). As a new command, merge will chose the PORTAL_MULTI_QUERY
strategy, and be sent to ProcessQuery() function.

5. As in the ExecutorStart() part, we need to set junkfilter for merge
command, since we have a ctid junk attr in target list. And, the merge
action plans should also be initialized and transformed into PlanState
nodes. However, the initialization over action plan is only focus on the
target list and quals. We don't need other part of traditional plan
initialization, since these action plans are not for scanning or joining
(this is the job of top plan). We only want to transform the action
information into standard format that can be used by qualification evaluator
in executor.
I HAVE DONE ALL THE ABOVE IN A SECOND SUBMISSION.

6. In ExecutorRun() part, the top plan will be passed into ExecutePlan().
The action planstates can be found in the
estate->es_plannedstmt field.
The top plan can return tuples of the left out join on source table and
target table. (I can see the tuple be returned in my codes). Thus, the
design is correct. At least the top plan can do its work well. In the
junkfilter, if we can find a non-null ctid, it is a matched tuple, or else,
it is a NOT MATCHED tuple. Then we need to evaluate the additional quals of
the actions one by one. If the evaluations of one action succeed, we will
take this action and skip the remaining ones.

Since the target list and qual expressions are all processed by rewriter,
planner and InitPlan(), I think they will be accepted by the ExecQual()
function without many problems.

This is the last step, and I am still working on it.

PS: Heikki asked me about what the "EXPLAIN MERGE ..." command will do.
Well, I have not test it, but it may through an error or just explain the
top plan, since I put the action plans in a new field, which cannot be
recognized by old functions.

Thanks!

Yours Boxuan.