Method for updating multiple tables of PostgreSQL database and electronic equipment
By handling multi-table updates in the PostgreSQL database, traversing the index value linked list and updating multi-table records, the problem that PostgreSQL does not support multi-table updates is solved, and the multi-table update function is realized, which improves work efficiency and data consistency.
Patent Information
- Application Number
- CN202411537758.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-31
- Publication Date
- 2025-02-11
AI Technical Summary
The PostgreSQL database does not support updating rows of multiple specified tables, resulting in the multi-table update syntax that cannot be used after migrating from MySQL to PostgreSQL. The application business logic needs to be manually modified to reduce work efficiency and increase migration adaptation costs.
By syntactically and semantically processing multiple tables, traversing the index value of the relationship's index value, making its column index value consistent with the relationship index value, and updating multi-table records during execution, realizing the multi-table update function of PostgreSQL database.
It realizes the support for multi-table updates by PostgreSQL database, reduces migration difficulty, improves work efficiency, simplifies code and maintenance, and improves data consistency and transaction control.
Smart Images
Figure CN119415534A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of databases, and in particular to a method and electronic device for updating multiple tables of a PostgreSQL database. Background Art
[0002] The UPDATE syntax in the current PostgreSQL database supports updating rows in a specified table, but does not support updating multiple specified tables. As a result, many applications cannot be used after being migrated from MySQL to PostgreSQL. The application business logic needs to be manually modified to implement multi-table updates, which greatly reduces work efficiency and increases migration adaptation costs. Summary of the invention
[0003] The technical problem to be solved by the present invention is how to implement multi-table update of PostgreSQL; in view of this, the present invention provides a method and device for updating multiple tables of a PostgreSQL database.
[0004] The technical solution adopted by the present invention is a method for updating multiple tables of a PostgreSQL database, comprising:
[0005] Step S1, syntactically and semantically processing multiple tables to obtain target columns;
[0006] Step S2, traversing the index value linked list of the update relationship, processing the target column so that its column index value is consistent with the relationship index value;
[0007] Step S3, updating multiple table records during execution.
[0008] In one embodiment, step S1 includes:
[0009] Add a node in PostgreSQL to save the specified table in the form of a linked list;
[0010] Parse the generated parse tree, processing the specified multiple tables and target columns.
[0011] In one embodiment, the parse tree generated by the parsing processes the specified multiple tables and target columns, including:
[0012] In MySQL compatible mode, check whether the specified table is a multi-table. If it is a multi-table, proceed to the next step;
[0013] Construct a range table, obtain the updated index value of the range table, and record it in the current linked list;
[0014] The target column is parsed, relationships that do not need to be updated are deleted, and the attribution relationship is identified through the target column and the range table.
[0015] In one embodiment, step S2 includes:
[0016] Traversing the index value linked list of the update relation, and obtaining the index value according to the range table;
[0017] Get the corresponding range table and table information. The column index value of the processing target column must be consistent with the relationship index value.
[0018] In one embodiment, the step S2 further includes:
[0019] Set default values, construct new column information, and record it in the linked list;
[0020] According to the column information, if it is a hidden column, it is recorded in the linked list and the attribute value is adjusted at last.
[0021] In one embodiment, step S3 specifically includes:
[0022] Construct a ProjectionInfo node according to the update column;
[0023] Based on the ProjectionInfo node, multiple table updates are performed.
[0024] In one implementation, constructing a ProjectionInfo node according to the update column includes:
[0025] Traverse the current target column linked list to obtain the target column of each table to be updated; construct slot and tuple descriptors, and associate the slot and tuple descriptors;
[0026] Construct the ProjectionInfo node information and save it.
[0027] In one embodiment, performing multi-table update based on the ProjectionInfo node includes:
[0028] Calculate the number of relations in MySQL mode, use them as the specified relations corresponding to the conditions, and update the columns of the specified table in sequence according to the specified relations;
[0029] Get the corresponding planSlot and tuple ctid to determine whether it is valid. If it is valid, execute the next step; if it is invalid, skip this update;
[0030] Initialize the projection data of the update tuple according to the target column corresponding to the table to be updated, and perform the update.
[0031] In one embodiment, step 3 further includes: completing the update and clearing the slot information:
[0032] When the update is performed for the first time, the slot is obtained and recorded, and the subsequent ctid is obtained from the slot;
[0033] Determine whether the current ctid is valid. If not, set the flag; if valid, proceed to the next step;
[0034] Determine the visibility in the snapshot and whether it can be updated. If yes, set the flag; if not, report an error.
[0035] According to the projectioninfo corresponding to the table, get the corresponding slot and fill the corresponding ctid value into the tts_values structure;
[0036] Call the database built-in function to clean up the current slot.
[0037] Another aspect of the present invention provides an electronic device, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of the method for updating multiple tables of a PostgreSQL database as described in any one of the above items.
[0038] Compared with the prior art, the present invention has at least the following advantages:
[0039] The technical solution of the present invention aims at the technical problem that the current PostgreSQL database does not support updating rows of multiple specified tables, especially the syntax of updating multiple tables from MySQL application migration, which the database cannot support. By processing multiple tables in syntax and semantics, traversing the index value chain table of the update relationship, making its column index value consistent with the relationship index value, and updating multiple table records during execution, PostgreSQL has the syntax for updating multiple tables, and provides support for MySQL application migration and updating multiple tables. It improves work efficiency and increases database usability. BRIEF DESCRIPTION OF THE DRAWINGS
[0040] Figure 1 A schematic flow chart of a method for updating multiple tables in a PostgreSQL database according to an embodiment of the present invention;
[0041] Figure 2 A schematic diagram of a process flow for processing multiple tables in terms of syntax and semantics according to an embodiment of the present invention;
[0042] Figure 3 A schematic diagram of a flow chart of processing specified multiple tables and target columns for a parse tree generated by semantic analysis according to an embodiment of the present invention;
[0043] Figure 4 is a schematic diagram of a target column processing flow according to an embodiment of the present invention;
[0044] Figure 5 A schematic diagram of a process of updating multi-table records during execution according to an embodiment of the present invention;
[0045] Figure 6 A schematic diagram of a process of constructing a ProjectionInfo node according to an embodiment of the present invention;
[0046] Figure 7 A schematic diagram of a multi-table update process according to an embodiment of the present invention;
[0047] Figure 8 A schematic diagram of a process for clearing slot information according to an embodiment of the present invention;
[0048] Fig. 9 The figure is a schematic diagram of the composition of an electronic device according to an embodiment of the present invention. DETAILED DESCRIPTION
[0049] In order to further explain the technical means and effects adopted by the present invention to achieve the predetermined purpose, the present invention is described in detail below in conjunction with the accompanying drawings and preferred embodiments.
[0050] Unless otherwise defined, all terms (including technical terms and scientific terms) used in this article have the same meaning as commonly understood by ordinary technicians in the field to which this application belongs. It should also be understood that terms (such as terms defined in commonly used dictionaries) should be interpreted as having the same meaning as their meaning in the context of the relevant technology, and will not be interpreted in an idealized or overly formal sense unless explicitly defined in this article.
[0051] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.
[0052] For ease of understanding, the technical terms mentioned in this application are explained below.
[0053] 1) SQL: SQL is a structured query language, which is closer to natural language. When using SQL, you only need to say what to do, not how to do it. It consists of data definition language, data manipulation language, and data control language. It is not process-oriented, that is, the previous statement is independent of the next statement. SQL is a standard language.
[0054] 2) UPDATE is a data manipulation language in SQL, and its function is to update the rows of a table in the database.
[0055] 3) UPDATE updates the rows from the specified table that satisfy the WHERE clause. If the WHERE clause is not present, the effect is to update all specified rows in the table. The result is the number of updated rows returned. There are two ways to update the rows of a table using information contained in other tables in the database: using a subselect or specifying additional tables in the from clause.
[0056] 4) Currently, PostgreSQL only supports updating one specified table, not multiple specified tables.
[0057] The first embodiment of the present invention is a method for updating multiple tables in a PostgreSQL database, such as Figure 1 As shown, including:
[0058] Step S1, syntactically and semantically processing multiple tables to obtain target columns;
[0059] Step S2, traversing the index value linked list of the update relationship, processing the target column so that its column index value is consistent with the relationship index value;
[0060] Step S3, updating multiple table records during execution.
[0061] refer to Figure 1 , the method provided in this embodiment will be described in detail step by step below.
[0062] Step S1, syntactically and semantically processing multiple tables to obtain target columns.
[0063] refer to Figure 2 In the specific implementation of this embodiment, it is necessary to add a node for parsing multiple tables in the syntax parsing stage, and save the specified table through a linked list.
[0064] Furthermore, the parse tree generated by semantic analysis is processed to specify multiple tables and target columns.
[0065] Specifically, refer to Figure 3 , the parse tree generated by semantic analysis and parsing, processing the specified multiple tables and target columns can be in MySQL compatible mode, check whether the specified table is a multiple table; if not, process it as a single table update; if it is a multiple table, execute the next step;
[0066] Further, a range table is constructed, an index value of the range table is obtained, and the index value is recorded in a linked list;
[0067] Parse the target column and identify the relationship according to the identification information of the target column, which specifically includes parsing the target column, deleting the relationship (table) that does not need to be updated, and identifying the belonging relationship through the target column and the range table.
[0068] Step S2, traverse the index value linked list of the update relationship, process the target column, and make its column index value consistent with the relationship index value.
[0069] refer to Figure 4 In a specific implementation of this embodiment, the following processing is specifically included:
[0070] Traverse the index value linked list of the update relationship and obtain the index value according to the constructed range table;
[0071] Get the corresponding range table; get the table information, the column index value of the processing target column must be consistent with the relationship index value;
[0072] Process the generated columns, that is, set default values, construct new column information, and record them in the linked list;
[0073] Process hidden columns, that is, judge according to the column information, if it is a hidden column, record it in the linked list, and finally adjust the attribute value;
[0074] Update multiple table records during execution.
[0075] Step S3, updating multiple table records during execution.
[0076] Specifically, the ProjectionInfo node is constructed according to the update column through initialization processing. The flowchart is as follows Figure 6 :
[0077] First, traverse the target column linked list to obtain the target column of each table to be updated; construct slot and tuple descriptors, and associate the slot and tuple descriptors;
[0078] Furthermore, the ProjectionInfo node information is constructed and saved.
[0079] The specific processing flow chart for executing multi-table updates is as follows Figure 7 :
[0080] In MySQL mode, the number of relationships is calculated and used as a condition to specify the relationship, so as to update the columns of the specified table in sequence; according to the number of ProjectionInfo nodes constructed (the number of nodes constructed will correspond to the number of tables that need to be updated), the number of update relationships is calculated and used as a loop condition to loop and update the columns corresponding to each table.
[0081] Get the corresponding planSlot and tuple ctid, and determine whether ctid is valid (whether it is 0). If so, proceed to the next step; if not, skip this update.
[0082] Initialize the projection data of the update tuple according to the target column corresponding to the table to be updated; execute the update.
[0083] In one possible implementation, reference Figure 8 This embodiment may further include subsequent cleaning operations, specifically including cleaning slot information:
[0084] The first time the update is executed, the slot is obtained and recorded, and the subsequent ctid is obtained from the slot.
[0085] Determine whether ctid is valid. If not, set the flag; if valid, proceed to the next step.
[0086] Determine the visibility in the snapshot and whether it can be updated (specifically, whether the tuple can be found in the buffer or on disk, and the visibility is determined according to the visibility judgment rules (xmin, xmax, clog, snapshot)). If yes, set the flag; if not, report an error.
[0087] According to the projectioninfo corresponding to the table, get the slot and fill the real ctid value into tts_values.
[0088] Call the database built-in function to clean up the slot.
[0089] Compared with the prior art, this embodiment has at least the following effects:
[0090] 1. Reduce the difficulty of migration: The current PostgreSQL database supports updating rows in a specified table. This solution implements the syntax of PostgreSQL to update multiple tables, which reduces the difficulty of migration, improves work efficiency, and enhances database performance.
[0091] 2. Reduce transaction complexity: Directly updating multiple tables in a single transaction can reduce transaction complexity. All necessary updates can be completed in one operation, rather than writing multiple separate UPDATE statements and handling dependencies between them.
[0092] 3. Improve data consistency: By updating multiple tables in one operation, it is easier to maintain consistency between data. Using transactions can ensure that either all updates are successful or all are rolled back when an error occurs, thus avoiding data inconsistency problems caused by partial updates.
[0093] 4. Simplify code and maintenance: In the application code, encapsulating the update logic of multiple tables in one database operation can make the code more concise and easier to maintain. In addition, reducing the number of SQL statements in the code can also reduce potential sources of errors.
[0094] 5. Better transaction control: Direct multi-table updates allow for finer control over transaction boundaries, ensuring that all related tables have been updated as expected before committing a transaction, thus avoiding data inconsistencies that are discovered after the transaction is committed.
[0095] 6. Support complex business logic: In many business scenarios, data updates may involve complex relationships between multiple tables. Direct multi-table updates can more easily implement these complex business logics without writing complex intermediate logic or additional database objects (such as triggers).
[0096] 7. Improve developer efficiency: For database administrators and developers, being able to update multiple tables in one operation can save time and improve efficiency. This reduces the need to write, test, and debug multiple separate update statements.
[0097] A second embodiment of the present invention is an electronic device, such as Fig. 9 As shown, it can be understood as a physical device, including a processor and a memory storing instructions executable by the processor. When the instructions are executed by the processor, the following operations are performed:
[0098] Step S1, syntactically and semantically processing multiple tables to obtain target columns;
[0099] Step S2, traversing the index value linked list of the update relationship, processing the target column so that its column index value is consistent with the relationship index value;
[0100] Step S3, updating multiple table records during execution.
[0101] The third embodiment of the present invention, the process of the method for updating multiple tables of the PostgreSQL database of this embodiment is the same as that of the first and second embodiments, the difference is that, in terms of engineering implementation, this embodiment can be implemented by means of software plus a necessary general hardware platform, of course, it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the method of the present invention can be embodied in the form of a computer software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), including several instructions for enabling a device to execute the method described in the embodiment of the present invention.
[0102] Through the description of the specific implementation methods, a deeper and more specific understanding of the technical means and effects adopted by the present invention to achieve the predetermined purpose should be obtained. However, the accompanying drawings are only for reference and illustration purposes and are not intended to limit the present invention.
Claims
1. A method for updating multiple tables in a PostgreSQL database, characterized in that: include: Step S1, syntactically and semantically processing multiple tables to obtain target columns; Step S2, traversing the index value linked list of the update relationship, processing the target column so that its column index value is consistent with the relationship index value; Step S3, updating multiple table records during execution.
2. The method for updating multiple tables in a PostgreSQL database according to claim 1, characterized in that: The step S1 comprises: Add a node in PostgreSQL to save the specified table in the form of a linked list; Parse the generated parse tree, processing the specified multiple tables and target columns.
3. The method for updating multiple tables in a PostgreSQL database according to claim 2, characterized in that: The parse tree generated by the parsing processes the specified multiple tables and target columns, including: In MySQL compatible mode, check whether the specified table is a multi-table. If it is a multi-table, proceed to the next step; Construct a range table, obtain the updated index value of the range table, and record it in the current linked list; The target column is parsed, relationships that do not need to be updated are deleted, and the attribution relationship is identified through the target column and the range table.
4. The method for updating multiple tables in a PostgreSQL database according to claim 3, characterized in that: The step S2 comprises: Traversing the index value linked list of the update relation, and obtaining the index value according to the range table; Get the corresponding range table and table information. The column index value of the processing target column must be consistent with the relationship index value.
5. The method for updating multiple tables in a PostgreSQL database according to claim 4, characterized in that: The step S2 further comprises: Set default values, construct new column information, and record it in the linked list; According to the column information, if it is a hidden column, it is recorded in the linked list and the attribute value is adjusted at last.
6. The method for updating multiple tables in a PostgreSQL database according to claim 1, characterized in that: The step S3 specifically includes: Construct a ProjectionInfo node according to the update column; Based on the ProjectionInfo node, multiple table updates are performed.
7. The method for updating multiple tables in a PostgreSQL database according to claim 6, characterized in that: The step of constructing a ProjectionInfo node according to the update column includes: Traverse the current target column linked list to obtain the target column of each table to be updated; construct slot and tuple descriptors, and associate the slot and tuple descriptors; Construct the ProjectionInfo node information and save it.
8. The method for updating multiple tables in a PostgreSQL database according to claim 6, characterized in that: The performing multi-table update based on the ProjectionInfo node includes: Calculate the number of relations in MySQL mode, use them as the specified relations corresponding to the conditions, and update the columns of the specified table in sequence according to the specified relations; Get the corresponding planSlot and tuple ctid to determine whether it is valid. If it is valid, execute the next step; if it is invalid, skip this update; Initialize the projection data of the update tuple according to the target column corresponding to the table to be updated, and perform the update.
9. The method for updating multiple tables in a PostgreSQL database according to claim 6, characterized in that: The step 3 also includes: completing the update and clearing the slot information: When the update is performed for the first time, the slot is obtained and recorded, and the subsequent ctid is obtained from the slot; Determine whether the current ctid is valid. If not, set the flag; if valid, proceed to the next step; Determine the visibility in the snapshot and whether it can be updated. If yes, set the flag; if not, report an error. According to the projectioninfo corresponding to the table, get the corresponding slot and fill the corresponding ctid value into the tts_values structure; Call the database built-in function to clean up the current slot.
10. An electronic device, characterized in that: The electronic device comprises: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the computer program is executed by the processor, the steps of the method for updating multiple tables of a PostgreSQL database as claimed in any one of claims 1 to 9 are implemented.