Read with me. PostgreSQL Source (16)--executor (--control node of the query execution module (bottom))

5.ModifyTable node

Let's look at an example of a modifytable node:

postgres=# explain update test_01 set id = 5 where name = 'xxx';                          QUERY PLAN--------------------------------------------------------------- Update on test_01  (cost=0.00..23.75 rows=6 width=48)   ->  Seq Scan on test_01  (cost=0.00..23.75 rows=6 width=48)         Filter: ((name)::text = 'xxx'::text)(3 rows)

You may wonder why there is no "modifytable" in the query plan above, and here is a paragraph in the EXPLAIN.C file:

case T_ModifyTable:            sname = "ModifyTable";            switch (((ModifyTable *) plan)->operation)            {                case CMD_INSERT:                    pname = operation = "Insert";                    break;                case CMD_UPDATE:                    pname = operation = "Update";                    break;                case CMD_DELETE:                    pname = operation = "Delete";                    break;                default:                    pname = "???";                    break;            }            break;

Thus we can see that for the Modifytable node, explain will determine whether or not to remove the change in order to make a judgment. So when you see INSERT, UPDATE, and delete in explain, we know that this is the Modifytable node.

So here we have to explain the modifytable node again? Explain it:

 *      Apply rows produced by subplan(s) to result table(s), *      by inserting, updating, or deleting.

That is, I get rows from the lower Subplan, and then choose whether to insert, update or Delete, depending on the command type. So we can see that this is a top-level node, and below it is the query node (that is, select). This is in line with what we have always said, all the additions and deletions are actually select!

typedef struct modifytable{Plan plan;      Cmdtype operation;      /* INSERT, UPDATE, or DELETE */bool Cansettag; /* Do we set the command tag/es_processed?    */Index nominalrelation;    /* Parent RT Index for use of EXPLAIN */List *resultrelations; /* Integer list of RT indexes */int resultrelindex;          /* Index of First resultrel in plan ' s list */list *plans;   /* Plan (s) producing source data */List *withcheckoptionlists; /* per-target-table WCO Lists */List *returninglists;   /* Per-target-table Returning tlists */List *fdwprivlists;       /* per-target-table FDW Private Data lists */List *rowmarks;       /* Planrowmarks (non-locking only) */int epqparam;  /* ID of Param for evalplanqual re-eval */onconflictaction onconflictaction; /* on CONFLICT action */List *arbiterindexes; /* List of on CONFLICT Arbiter index OIDs */list *oncoNflictset;    /* SET for INSERT on CONFLICT do UPDATE */Node *onconflictwhere;     /* WHERE for on CONFLICT UPDATE */Index Exclrelrti;   /* RTI of the excluded pseudo relation */List *exclreltlist; /* Tlist of the excluded pseudo relation */} modifytable;

Because the modifytable node involves more operations, here is a little explanation of some of the fields in Modifytable.

withcheckoptionlists Field

This is related to the view, and we know that creating a view has this usage:

CREATE VIEW xxx_view AS query WITH CHECK OPTION

In Postgres, when you create a view with CHECK option in a statement, you must also see the result of the operation in the view by doing the operation through the view (add and remove).

Other words:

For insert, the added record must be visible after the view query.

For update, the modified results must also be visible through this view.

For delete, only the records displayed in the view are deleted.

So for this kind of operation, when we manipulate the table/view, we add the conditions in the WITH option to the (insert/update/delete) where condition.

returninglists Field

This is simple, because the syntax of Postgres is similar to the following usage:

DELETE FROM xxx_table WHERE condition RETURNING xxx;UPDATE xxx_table SET a = '123'   WHERE condition RETURNING xxx;INSERT INTO xxx_table VALUES (somevalues) RETURNING xxx;

Yes, Postgres's returning clause can return modified rows, so for queries that contain returning clauses, there are additional rows to be returned in addition to insert/update/delete the data in the table. That is, additional output is also needed.

fdwprivlists Field

Postgres supports access to external databases, so this field provides support for the processing of FDW.

Rowmarks Field

This is related to the lock clause for select:

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

See details here: http://www.postgres.cn/docs/9.5/sql-select.html

Onconflictaction, Arbiterindexes, arbiterindexes, and Onconflictwhere fields

Yes, for insert operations, we have the following syntax (to support conflicts that occur in insert):

INSERT INTO table_name VALUES (somevalues) ON CONFLICT [ conflict_target ] conflict_action并且 conflict_action 是以下之一：    DO NOTHING    DO UPDATE SET { column_name = { expression | DEFAULT } |                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |                    ( column_name [, ...] ) = ( sub-SELECT )                  } [, ...]              [ WHERE condition ]

Such a look, it is easy to get on.

Exclrelrti, exclreltlist Fields

The following clauses are available for the Build Table statement:

EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ]

The EXCLUDE clause specifies an exclusion constraint that guarantees that if any two rows are compared using the specified operator on the specified column or expression, not all comparisons will return true. See here for details:

Therefore, you can think of this field as a constraint field that needs to be judged when doing the update operation.

typedef struct modifytablestate{planstate PS;      /* Its first field is Nodetag */cmdtype operation;      /* INSERT, UPDATE, or DELETE */bool Cansettag; /* Do we set the command tag/es_processed?        */bool Mt_done; /* is we done?       */Planstate **mt_plans;      /* Subplans (one per target rel) */int mt_nplans;   /* Number of plans in the array */int mt_whichplan;       /* Which one is being executed (0..n-1) */Resultrelinfo *resultrelinfo;   /* Per-subplan Target relations */List **mt_arowmarks;    /* Per-subplan Execauxrowmark Lists */Epqstate mt_epqstate; /* for evaluating evalplanqual rechecks */bool firebstriggers; /* Do we need to fire stmt triggers?     */Onconflictaction Mt_onconflict;      /* on CONFLICT type */List *mt_arbiterindexes; /* Unique index OIDs to arbitrate * taking alt path */Tupletableslot *MT_existing;       /* slot to store existing target tuple in */List *mt_excludedtlist;       /* The excluded pseudo relation ' s * tlist */Tupletableslot *mt_conflproj; /* CONFLICT ... SET ... projection * target * *} modifytablestate;

So for the modifytablestate of some fields, we refer to the Modifytable node interpretation, but also can understand the same, there is not much to say.

Below to get to the chase:

the initialization of the Modifytable node is done by the execinitmodifytable function. This function, in addition to some basic initialization operations, has been set up and initialized for the fields I mentioned above. To say the finer things is:

• (1) Call the Execinitnode function to initialize the Subplans node in the plans list in the Modifytable node and save its results to the Modifytablestate field of the Mt_plans structure. In this step, you also do this by the way: Verify that the target relations that the query is involved in is legal, and open the index on these target relations, because for Update/insert operation, We also operate on the corresponding index (the delete operation does not delete the index, and the index left behind by the delete leaves the vacuum to clean up).

• (2) Initialize the above mentioned with CHECK OPTION (if present) according to the Withcheckoptionlists field in the Modifytable node. The member variables ri_withcheckoptions and Ri_withcheckoptionexprs in the Resultrelinfo field of the modifytablestate structure are saved after initialization.

• (3) Initializes the returning clause mentioned above (if present) according to the Returninglists field in the Modifytable node, and returns the type of the result set based on this construct. If the Returninglists field is empty, the description does not have a returning clause. Then the type of the returned result set is set to NULL.

• (4) If an on CONFLICT does update field is present, the target list, the projection condition Resultrelinfo, and the filter condition qual are initialized for him, and the result is saved in the corresponding field in the modifytablestate structure.

• (5) The Rowmark field in the Modifytable node is processed, and the results are saved in the Mt_arowmarks field of the modifytablestate structure.

• (6) Initialize the junk filter. The origin of this junk filter is because in the plan phase, we produce some "intermediate information" that is placed in a tuple for excutor use. Ctid, for example, locates where the tuple is placed in a disk file. But when we write tuples to disk, we don't need to save this information. So this information is equivalent to redundancy, we need to use this junkfilter to filter and delete it.

• (7) We know that we may be involved in insert/update/delete when the trigger is set up, where trigger related slots are provided for subsequent function calls.

• (8) If the Modifytable node is not the top-level modifytable node (the upper layer also has the modifytable node), set the global State structure Estate->es_auxmodifytables attributes, mark.

Execution of the modifytable node is performed by the execmodifytable function. In particular:

• (1) First we have to remember that there may be before STATEMENT triggers this thing, as the name implies, is to execute this trigger before STATEMENT execution. If so, we'll call the Firebstriggers function to handle it before we get to the formal process.

• (2) Next is a large for loop. Inside this for loop, the program calls the Execprocnode function to iterate through the tuples from the underlying node. It is important to note that this loop is similar to the Append node, and after reading the tuple in the first Subplans node, the tuples in the subsequent subplan are read sequentially until all reads are complete. we said before. Postgres is a tuple that is read one at a time and processed by a tuple. This is no exception, and each tuple is read by invoking the Execinsert/execupdate/execdelete function, respectively, according to the type of operation.

• (3) Beginning and ends, since there may be before STATEMENT triggers, then there may be after STATEMENT triggers, where the fireastriggers function is called to handle it.

Then we should be interested in the Execinsert/execupdate/execdelete function. Let's start talking about them.

1.ExecInsert

For the Execinsert function, there are two main things: inserting a tuple into the target table, the relation, and inserting the corresponding index entry into the relevant index table, index relations (there may be multiple indexes to process).

• (1) First, the tuple that needs to be inserted is removed from the slot and localized. Why Because this slot may not be safe in the subsequent operation of the Heap_insert function, it is taken out in advance. This work is done by the Execmaterializeslot function.

• (2) Estate->es_result_relation_info obtain information from the global state and determine if result relation requires an OID. If necessary, set the OID field in the tuple to 0 first.

• (3) handle before ROW INSERT Triggers. Here we should note that this trigger is of row level, and before STATEMENT triggers is statement level, they are not the same.

• (4) processing instead of ROW INSERT Triggers. If present, call the Execirinserttriggers function to process and return directly without an insert operation.

• (5) Processing foreign table, for which the ri_fdwroutine is initialized. Call the API of the foreign server to process the insertion of this tuple and get the returned slot

• (6) Handle the condition (execwithcheckoptions function) and uniqueness constraint (execconstraints function) on OnConflict option in with CHECK option.

• (7) If there is an on onconflict option condition, the speculative insertion lock is obtained first, and the Heap_insert function is called to insert the tuple into the heap table. If the insert succeeds, the lock is released normally if no conflict occurs. Otherwise force the lock to be released and perform an on onconflict do UPDATE, if any.

• (8) There is no condition in (7), we normally call the Heap_insert function to insert tuples into the heap table. Also call the Execinsertindextuples function to insert the appropriate index tuple.

• (9) Call the Execarinserttriggers function to process the after ROW INSERT Triggers. Similar to (3) processing.

• (10) Remember the WITH CHECK option in the CREATE view mentioned above? Here call execwithcheckoptions function to do processing, do not meet the error exit.

• (11) If there is a returning clause, we call the execprocessreturning function to handle it.

2.ExecDelete

The Execdelete function is relatively simple, he only needs to delete the tuple, do not need to do any work on the index.

• (1) Estate->es_result_relation_info get information from the global state.

• (2) processing before ROW DELETE Triggers. Here we should note that this trigger is of row level, and before STATEMENT triggers is statement level, they are not the same.

• (3) processing instead of ROW DELETE Triggers. If present, call the Execirdeletetriggers function to process and return directly without an insert operation.

• (4) Processing foreign table, for which the ri_fdwroutine is initialized. Call the foreign server's API to handle the deletion of the tuple and get the returned slot.

• (5) We normally call the Heap_delete function to perform a delete operation. If the return value is not heaptuplemaybeupdated, the operation fails and the appropriate processing is performed based on the error code that failed.

• (6) Call the Execardeletetriggers function to process after ROW DELETE Triggers. Similar to (2) processing.

• (7) If there is a returning clause, we call the execprocessreturning function to handle it.

3.ExecUpdate

The

Execupdate function actually performs an "INSERT" operation. Because Postgres internal is the MVCC mechanism, multi-version concurrency control. The old tuples are not actually deleted, just no longer referenced. At the same time, the update operation within the database is also in the "transaction", otherwise Postgres will continue to be added to the updated tuple as the need to update the tuple, loop down.

• (1) Determine whether the current is in the bootstrapprocessing mode, in which all transaction IDs are set to 1. This is the time to ensure that updates are not recycled.

• (2) First, the tuple that needs to be inserted is removed from the slot and localized. Why Because this slot may not be safe in the subsequent operation of the Heap_update function, it is taken out in advance. This work is done by the Execmaterializeslot function.

• (3) processing before ROW UPDATE Triggers.

• (4) processing instead of ROW UPDATE Triggers. If present, call the Execirupdatetriggers function to process and return directly without an insert operation.

• (5) Processing foreign table, for which the ri_fdwroutine is initialized. Call the foreign server's API to process the update for that tuple and get the returned slot.

• (6) Handle the condition (execwithcheckoptions function) and uniqueness constraint (execconstraints function) on OnConflict option in with CHECK option.

• (7) We normally call the Heap_update function to perform update, operation. If the return value is not heaptuplemaybeupdated, the operation fails and the appropriate processing is performed based on the error code that failed. If successful, the Execinsertindextuples function is called to insert an index tuple into the index.

• (8) Call the Execarupdatetriggers function to process the after ROW UPDATE Triggers.

• (9) Execute with CHECK OPTION again for the upper view of the table.

• (10) If there is a returning clause, we call the execprocessreturning function to handle it.

modifytable node cleanup is simpler (execendmodifytable function). In addition to the regular cleanup work, cleanup may exist for FDW structures, cleaning up those subplans nodes that are initialized in the extra initialization.

The control node ends here.

Read with me. PostgreSQL Source (16)--executor (--control node of the query execution module (bottom))