OpenGauss-based duplication name index creation and reconstruction method and apparatus, and electronic device

Abstract

The invention relates to a duplication name index creation and reconstruction method and device based on openGauss, and aims to solve the compatibility problem caused by index naming conflicts when heterogeneous databases such as MySQL migrate to openGauss. According to the method, a duplication index function switch is controlled through a GUC parameter ENABLEDUPLICATEINDEXNAME, when the duplication index function switch is started, a user table index name is rewritten (an affiliated table OID and an ASCII 0x1F invisible separator are added), and a unique system storage name is generated and written into a pgclass system table and other system tables; a REINDEX mechanism is provided to realize smooth transition of new and old index formats; and transparent analysis of user operations (such as INDEX HINT) is supported. According to the method, on the premise that the global uniqueness constraint of the openGauss index is not damaged, the MySQL cross-table duplicate-name index is compatible, the migration efficiency and the system compatibility are improved, the labor cost is reduced through automatic rewriting, parameter control and other mechanisms, and the accuracy and stability of large-scale database migration are guaranteed.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database operation, in particular to a method and device for creating and reconstructing duplicate index based on openGauss, a computer readable storage medium and an electronic device. BACKGROUND

[0002] In recent years, with the rapid development of domestic database technology, openGauss, as a new generation of enterprise-level relational database management system, has been applied on a large scale in key industries such as finance and manufacturing due to its high security, high scalability and good ecological compatibility. Many users are gradually migrating the original MySQL-based business system to openGauss in order to meet the requirements of self-control and improve system security and maintainability. However, the database structure compatibility problem has become an important obstacle in the migration process, and the difference in index naming mechanism is particularly prominent.

[0003] In the MySQL database, the index naming mechanism allows the creation of indexes with the same name in different data tables under the same schema (Schema). Although this feature provides flexibility for historical system development, it also leads to the widespread existence of cross-table index duplication in a large number of existing business systems. In sharp contrast, openGauss, as a database system that strictly follows the relational model standard, stores the metadata of its indexes, which are core system objects, in the pg_class system table, and the system enforces a global uniqueness constraint, requiring that index names be unique within the same database range (i.e., under all schemas). This mechanism makes it impossible to create cross-table duplicate indexes in the original MySQL that are legal due to name conflicts when migrating to openGauss, directly affecting the feasibility of business system migration and data compatibility.

[0004] To address the above index naming conflict problem, existing migration solutions rely on manual or simple scripts to rename conflicting index names. However, such methods have significant limitations: (1) All indexes need to be identified and renamed one by one, which is inefficient and prone to omissions; (2) After renaming, the application layer SQL and ORM mapping need to be modified simultaneously, resulting in high maintenance costs; (3) Manual operations are inefficient and prone to human error, making it difficult to ensure consistency in the renaming process; (4) Scripted renaming can improve batch processing capabilities, but when faced with large-scale and highly complex database systems (such as production systems containing hundreds of thousands of tables and indexes), the workload of index renaming grows exponentially. At this time, the script execution period is long, the failure rollback is complex, the maintenance cost is significantly increased, and the operation complexity is difficult to control. Therefore, under the premise of maintaining the original index naming mechanism of openGauss, how to achieve compatibility support for cross-table duplicate index names has become a technical problem that needs to be solved in the field of database migration. SUMMARY

[0005] To solve the above problems, the application proposes a new openGauss-based duplicate index creation and reconstruction method. The method is a technical solution based on index name rewriting. Through the system-level index name management mechanism, duplicate index management under the same namespace can be realized in openGauss, effectively solving the above contradictions, significantly improving the database migration efficiency and compatibility, and providing important technical support for the migration adaptation of openGauss.

[0006] In summary, the method automatically rewrites the original index name, so that the index naming method in the MySQL database is compatible without violating the global uniqueness mechanism of openGauss index, thereby improving the compatibility and convenience of heterogeneous database system migration to openGauss.

[0007] The index name rewriting mechanism in the application is the core strategy to support the function of duplicate indexes under the same namespace, mainly including the following key links: 1. System object metadata management: In the openGauss database system, the metadata management of index objects mainly depends on two core system tables: pg_class and pg_index. Among them, pg_class is the unified metadata table of all relational objects (including normal tables, indexes, materialized views, sequences, etc.) in openGauss. During the creation of an index, the system first inserts a new record into pg_class to record the basic attributes of the index object, including the index name (relname), the index object OID, and the access method used (such as B-Tree, GIN, etc.). pg_index is a system table in openGauss that is specifically used to manage index-specific metadata, and it establishes a one-to-one correspondence between the index and the data table through indexrelid and indrelid. During the index creation process, the system first creates the basic relationship record of the index in pg_class, and then supplements the detailed structure definition of the index in pg_index; in subsequent query optimization, state maintenance, and other scenarios, the system accesses both tables simultaneously, and uses the built-in auxiliary index to achieve fast and accurate metadata retrieval. The storage architecture of the index-related system source data table in openGauss is shown in Figure 1 .

[0008] 2、Index name rewriting mechanism: On the pg_class table, the system defines the following one main unique index pg_class_relname_nsp_index by default, which takes relname (relation name) and relnamespace (belonging namespace OID) as joint keys and is set as a unique index to ensure the uniqueness of the relation name in the same namespace. For index objects, the name of pg_class is also stored and is subject to this unique index constraint. This means that under the default configuration, it is not allowed to create two indexes with the same name within the same namespace, even if they belong to different tables. This mechanism is one of the root causes of the incompatibility between MySQL and openGauss index naming. The present application makes the system not directly use the index name specified by the user as the unique identifier stored in the system table, but appends the OID (object identifier) of the data table to which the index belongs to the index name after the index name, forming the format "index name_OID" as the actual index identifier used internally by the system, and writing it into the relname field in the pg_class table. To ensure that the suffix automatically added by the system in the index name splicing process has a clear boundary with the index name specified by the user, while avoiding ambiguity or conflict caused by common characters (such as underscores _ and hyphens -), the present application uses the invisible control character with ASCII code value 0x1F (i.e. "Unit Separator") as the special separator between the internal index name and the table OID. This character rarely appears in regular SQL statements and user names, so it effectively reduces the risk of naming conflicts and ensures the accuracy and robustness of the index rewriting mechanism in various complex naming scenarios. To ensure the stability and compatibility of key objects within the database, the system automatically skips index objects belonging to system tables (such as indexes of system core tables such as pg_class, pg_index, pg_attribute, etc.) when performing REINDEX. Such indexes still use the traditional naming rules to avoid potential system behavior abnormalities caused by index rewriting. Figure 2 The index rewriting mechanism is demonstrated for the respective processing methods of system table indexes and user table indexes.

[0009] 3、GUC control parameters: In order to be compatible with the index structure without an OID suffix in the old database and to ensure the smoothness of database upgrade, the function design in the application uses the user-defined (User-Defined) level GUC parameter ENABLE_DUPLICATE_INDEXNAME to control the enabling state of the function. When the parameter is in the off state, the system uses the original index naming mechanism and prohibits the creation of duplicate indexes; when the parameter is turned on, the system enables the index rewriting logic to support duplicate indexes. This scheme does not need to modify the original SQL of the user, provides compatibility protection through parameterized control, and has good scalability and backward compatibility.

[0010] 4、REINDEX index rebuilding mechanism: Since the application enables the index duplication function, the index name retrieval logic will be switched from "pure index name" to "index name + separator (0x1F) + table OID", therefore, the original indexes created according to the old mechanism without a suffix in the database will not be correctly identified and used after the function is turned on. In order to ensure the normal operation and smooth transition of the database, the application designs an automatic index rewriting mechanism based on the REINDEX command, which supports rebuilding indexes at the database, schema or table level.

[0011] The specific mechanism is as follows: (1) The system provides extension support for REINDEX DATABASE, REINDEX SCHEMA and REINDEX TABLE commands. When the user executes the above commands, the system will scan all index objects in the target range in turn.

[0012] (2) For each index to be rebuilt, the system first determines whether the name contains a separator (0x1F) and an OID suffix: a) If the index name contains a suffix, it means that the index has been created using the duplicate index mechanism, and the system will skip the index to avoid duplicate processing; b) If the index name does not contain the 0x1F suffix, the system will automatically add an OID suffix to the index name during the rebuilding process and update the system table metadata to realize automatic rewriting of the index name.

[0013] This mechanism ensures that after the function is turned on, the user can migrate the historical indexes to the new format through a one-time or phased REINDEX operation, avoiding manual intervention and improving efficiency and accuracy.

[0014] Specifically, the application provides the following technical solutions: The first aspect of the application provides a duplicate index creation and rebuilding method based on openGauss, which comprises: S1: Obtain an index creation request, and determine whether a duplicate index function is enabled; the duplicate index function is controlled by a GUC parameter ENABLE_DUPLICATE_INDEXNAME, when the parameter is in an open state, step S2 is executed; when the parameter is in a closed state, an index is created according to an openGauss native index naming mechanism; S2: Determine whether an object to which a current index belongs is a system table index; if yes, an index is created according to an openGauss native index naming mechanism; if no, step S3 is executed. S3: Rewriting processing is performed on a name of a user table index, to generate a rewritten index name; the rewriting processing is to append an OID of a data table to which the index belongs to a user-specified original index name, and use an invisible control character of ASCII code 0x1F as a separator, to form a format of “original index name_0x1F table OID”. S4: It is checked whether a length of the rewritten index name exceeds a limit constant NAMEDATALEN of openGauss; if no, the rewritten index name is written into a relname field of a pg_class system table, and an association relationship between the index and the data table is supplemented in a pg_index system table, to complete index creation; if yes, an error is reported to terminate the creation process.

[0015] Further, in the method of the application, the separator in step S3 is an invisible control character of ASCII code 0x1F (unit separator), which is used to clearly distinguish the user-specified index name and the appended table OID, to avoid naming ambiguity or conflict caused by common characters (such as underscores and hyphens).

[0016] Further, the method of the application further includes a REINDEX index rebuilding mechanism, which is used to realize smooth transition of new and old index formats; specifically including: When a user executes a REINDEX DATABASE, REINDEX SCHEMA or REINDEX TABLE command, all index objects in a target range are scanned, and the following determination is performed on each index to be rebuilt: If the index name already contains the separator 0x1F and the table OID suffix (that is, the index is created by using the rewriting mechanism), the index is skipped; If the index name does not contain the separator 0x1F and the table OID suffix (that is, the index is created by using the native mechanism), the table OID and the separator 0x1F are automatically appended to the index during the rebuilding process, the index name field in the pg_class system table is updated, and the index format migration is completed.

[0017] Further, in the method of the present application, when the duplicate index name function is closed (i.e. the ENABLE_DUPLICATE_INDEXNAME parameter is in the closed state), the user executing the REINDEX command will trigger the following processing: Scan all index objects in the target range, if the index name contains the separator 0x1F and the table OID suffix, detect the conflict with the names of other indexes in the same namespace; if there is a conflict, report an error to the user to modify the conflicting index name through the ALTER TABLE command and then re-execute the REINDEX.

[0018] Further, in the method of the present application, when the user executes the INDEX HINT or ALTER INDEX command, the system automatically concatenates the index name input by the user with the OID of the data table to which it belongs and the separator 0x1F in the internal parsing stage, restores the rewritten index name actually stored in the system table, and uses it to accurately retrieve the target index in the pg_class and pg_index system tables.

[0019] Further, in the method of the present application, the system table includes pg_class and pg_index; wherein pg_class stores the basic metadata of the index (including the rewritten index name relname, index OID, access method, etc.), and pg_index stores the association relationship between the index and the data table (establishes a one-to-one association relationship through indexrelid and indrelid).

[0020] The second aspect of the present application provides a duplicate index creation and reconstruction device based on openGauss, the device comprises: A duplicate index judgment module is configured to obtain an index creation request and judge whether to enable a duplicate index function; the duplicate index function is controlled by a GUC parameter ENABLE_DUPLICATE_INDEXNAME, when the parameter is in the open state, a system table judgment is performed; when the parameter is in the closed state, an index is created according to the original index naming mechanism of openGauss; A system table judgment module is configured to judge whether the current index belongs to a system table index; if it is a system table index, an index is created according to the original index naming mechanism of openGauss; if it is a user table index, a rewriting process is performed; A rewriting processing module is configured to perform rewriting processing on the name of the user table index to generate a rewritten index name; the rewriting processing is to append the OID of the data table to which the index belongs to the original index name specified by the user, and use the invisible control character of ASCII code 0x1F as a separator to form the format of "original index name_0x1F table OID"; The rewritten name detection module is configured to check whether the length of the rewritten index name exceeds the openGauss limit constant NAMEDATALEN, and if not, write the rewritten index name to the relname field of the pg_class system table and supplement the association between the index and the data table in the pg_index system table to complete the index creation, and if so, report an error to terminate the creation process.

[0021] The device realizes the steps of the aforementioned openGauss-based duplicate index creation and reconstruction method in operation.

[0022] Further, the device further comprises: The function control module is configured to control the enablement or disablement of the duplicate index function through the GUC parameter ENABLE_DUPLICATE_INDEXNAME. The index reconstruction module is configured to scan the index objects in the target range in response to the REINDEX command, automatically append a suffix to the index that has not been rewritten and update the metadata, and skip the index that has been rewritten. The transparent parsing module is configured to automatically splice the index name input by the user, the table OID and the delimiter 0x1F to restore the index name actually stored in the system table when the user executes the INDEX HINT or ALTER INDEX command, and guarantee the correctness of the user operation.

[0023] The third aspect of the present application provides an electronic device, comprising a memory and a processor. The memory is configured to store a computer program. The processor is configured to execute the computer program to realize the steps of the aforementioned openGauss-based duplicate index creation and reconstruction method.

[0024] The fourth aspect of the present application provides a computer readable storage medium having a computer program stored thereon, wherein the computer program is executed by a processor to realize the steps of the aforementioned openGauss-based duplicate index creation and reconstruction method.

[0025] In summary, the present application proposes a system-level management mechanism based on index name rewriting, which controls the function switch through the GUC parameter, dynamically rewrites the index name (appends the table OID and the ASCII 0x1F invisible delimiter), performs the REINDEX index reconstruction transition and transparently parses the user operation, and realizes the compatible management of the duplicate index in the same namespace without violating the global uniqueness constraint of the openGauss index. Compared with the prior art, the present method has the following advantages: (1) Solving the migration obstacles caused by index naming conflicts when migrating from heterogeneous databases such as MySQL to openGauss, improving the migration efficiency and compatibility.

[0026] (2) Through the system-level automatic rewriting and transparent analysis mechanism, the cost of manual intervention is reduced, and the consistency and accuracy of large-scale database migration are guaranteed.

[0027] (3) Through the REINDEX function, smooth transition of new and old index formats is supported, and through GUC parameter control, the scalability and backward compatibility of system upgrade are ensured.

[0028] (4) Using invisible separator and system table index protection strategy, naming ambiguity and system behavior anomaly are avoided, and the stability of database core objects is considered.

[0029] Other features and advantages of the present application will be described in detail in the following description, or can be understood by implementing the related technical solutions of the present application. The purpose and other advantages of the present application can be achieved by the technical features and technical means explicitly indicated in the specification, claims and drawings, and obtained by the implementation process of these technical contents. BRIEF DESCRIPTION OF DRAWINGS

[0030] In order to more clearly explain the technical solutions of the present application, the drawings involved in the description of the present application will be briefly introduced below. It should be pointed out that the drawings only show part of the embodiments of the present application. For those skilled in the art, other related drawings can be derived from these drawings without creative labor.

[0031] Figure 1 The figure is the index object metadata storage system table mode architecture diagram in the present application, wherein the index object is managed through the system metadata table centered on pg_class and pg_index.

[0032] Figure 2 The figure is an example of rewriting the index name of the system table and the user table in the present application, wherein the pg_attribute_relid_attnam_index of the pg_attribute table of the system table is not affected by the index name rewriting mechanism when the parameter is started, and is stored in pg_class in the original name; The OID of the user table orders is 12345, which is rewritten to the form of idx_price+0x1f+12345 when creating the index idx_price.

[0033] Figure 3A flow chart is created for the index object creation based on index name rewriting in the application, wherein if the GUC parameter is turned on, the index_create function rewrites the incoming index name to provide an index name that meets the uniqueness constraint for the index by splicing the OID of the table to which the index belongs if it is judged that the table does not belong to a system table.

[0034] Figure 4 A flow chart is created for the index object REINDEX based on index name rewriting in the application, wherein when the GUC parameter is turned on, the user can rebuild the index object in the database at the DATABASE / TABLE / INDEX three granularities.

[0035] Figure 5 A flow chart is created for the overall implementation of the method of the application.

[0036] Figure 6 A structure diagram of the device of the application is provided.

[0037] Figure 7 A structure diagram of the electronic device provided by the embodiment of the application is provided.

[0038] Figure 8 A first step operation example diagram in the embodiment of the application is provided.

[0039] Figure 9 A second step operation example diagram in the embodiment of the application is provided.

[0040] Figure 10 A third step operation example diagram in the embodiment of the application is provided.

[0041] Figure 11 A fourth step operation example diagram in the embodiment of the application is provided.

[0042] Figure 12 A fifth step operation example diagram in the embodiment of the application is provided.

[0043] Figure 13 A sixth step operation example diagram in the embodiment of the application is provided.

[0044] Figure 14 A seventh step operation example diagram in the embodiment of the application is provided.

[0045] Figure 15 An eighth step operation example diagram in the embodiment of the application is provided.

[0046] Figure 16 A ninth step operation example diagram in the embodiment of the application is provided.

[0047] Figure 17 A tenth step operation example diagram in the embodiment of the application is provided.

[0048] Figure 18The figure shows the operation example of the 11th step in the embodiment of the present application. DETAILED DESCRIPTION

[0049] To make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. It should be noted that the described embodiments are only some of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the protection scope of the present application.

[0050] In this document, the term “comprising” and any form of the term “comprise”, such as “comprise”, “comprises” and “comprising”, are open-ended terms that are used to indicate the inclusion of one or more elements, features, or components, but do not exclude the presence of other elements, features, or components. The term “based on” is intended to mean “based at least in part on”, that is, the stated value or condition is one of several that can influence the present value or condition. The term “one embodiment” is intended to mean “at least one embodiment”, that is, the described embodiment is one of several possible embodiments, and other similar embodiments can also exist.

[0051] In this document, the terms “one” and “multiple” are used to modify the relevant elements or features, and the description is illustrative rather than restrictive. Unless otherwise explicitly stated in the context, “one” should be understood as “at least one”, and “multiple” should be understood as “at least two”. Those skilled in the art should interpret these terms reasonably and logically according to the semantics and logical relationship of the context, and ensure that they cover the possibility of “one or more”.

[0052] Figure 5 The figure shows the overall implementation process of the method for creating and rebuilding duplicate indexes based on openGauss provided by the present application, which includes the following steps: S1: Obtain an index creation request and determine whether to enable the duplicate index function; the duplicate index function is controlled by the GUC parameter ENABLE_DUPLICATE_INDEXNAME, when the parameter is in the open state, step S2 is executed; when the parameter is in the closed state, the index is created according to the openGauss native index naming mechanism; S2: Determine whether the object to which the current index belongs is a system table index; if it is a system table index, create the index according to the openGauss native index naming mechanism; if it is a user table index, execute step S3; S3: Rewriting the name of the user table index, generating the rewritten index name; the rewriting process is to append the OID of the data table to which the index belongs to the original index name specified by the user, and use the invisible control character of ASCII code 0x1F as a separator to form the format of "original index name_0x1F table OID"; S4: Check if the length of the rewritten index name exceeds the openGauss limit constant NAMEDATALEN; if not, write the rewritten index name to the relname field of the pg_class system table, and supplement the association relationship between the index and the data table in the pg_index system table to complete the index creation; if it exceeds, an error is reported to terminate the creation process.

[0053] In order to more clearly set forth the technical solutions of the present application, the following will be further described through specific scene embodiments.

[0054] The present application introduces the GUC (Grand Unified Configuration) parameter ENABLE_DUPLICATE_INDEXNAME to control the enable state of the index duplicate name function. When the parameter is closed, the system uses the traditional index naming method and does not allow duplicate index names in the same namespace; when the parameter is enabled, the system will automatically append the OID of the table and a special separator (ASCII 0x1F) to the index name to complete the rewriting of the index name, thereby realizing the support for duplicate index names.

[0055] As shown in Figure 3 During the index creation process: 1. The system first determines whether the index duplicate name function is enabled (i.e., whether the GUC parameter ENABLE_DUPLICATE_INDEXNAME is on); 2. If it is enabled, it is further determined whether the index belongs to a system table; if it belongs to a system table, the original index name is used to skip rewriting to ensure the stability of the system behavior; otherwise, the system will rewrite the index name; 3. Determine whether the length of the rewritten index name exceeds the limit constant NAMEDATALEN, and if it exceeds, report an error and terminate, otherwise write the index metadata to the pg_class table.

[0056] In addition, to ensure the usability of existing index objects in the database after parameter switching, the present application also provides a matching index reconstruction mechanism. As shown in Figure 4 When the user performs reconstruction on the database, schema or table through the REINDEX statement: 1、System will traverse all index objects in the target range, in turn judge whether it enables index name rewriting function, whether it is a system table index, and whether it has suffix format after rewriting; 2、For user indexes without suffix, the index name rewriting process will be automatically triggered, and the index name field in the pg_class system table will be updated to realize the transition upgrade of index format.

[0057] When using INDEX HINT (such as specifying to use a certain index in the query) or executing statements such as ALTER INDEX to modify index metadata, in order to avoid the problem that the index cannot be recognized due to the inconsistency between the index name input by the user and the internal storage name, the system will automatically append the OID suffix of the table to which the index belongs to the index name specified by the user in the internal analysis stage, and restore the actual index name by combining the 0x1F separator, which is used to accurately retrieve the target index in the system table. This processing is transparent to the user and does not need to change the usage habits of the existing SQL.

[0058] The following will combine Figure 3 and Figure 4 to illustrate how the mechanism introduced by the present application interacts with the original index creation process of openGauss, and how the mechanism realizes the duplicate index management function compatible with MySQL.

[0059] 1、Initialize the B-compatible mode database ( Figure 8 ); 2、In the default state, the enable_duplicate_indexnames state is OFF, and the index created in this state will not be rewritten when writing metadata, and the actual stored index name can be seen by executing the related query ( Figure 9 ); 3、Modify the GUC parameter enbale_duplicate_indexnames to ON and restart the database instance ( Figure 10 ); 4、After restarting, check the GUC parameter state, execute REINDEX on the index in the database, and check the index name corresponding to the relationship table t in pg_class again, which is rewritten to the form with \x1F suffix ( Figure 11 ); 5、Create two relationship tables t1 and t2 with different names in the namespace public, and create the same name index idx for the two relationship tables respectively ( Figure 12 ); 6. Execute the `show index` command to view the index information attached to t1 and t2 respectively. You can see that the duplicate indexes in pg_class, distinguished by the suffix \x1F, have been masked when displayed to the user. The index information perceived by the user is still the same as when it was created. Figure 13 ); 7. Users can modify existing indexes with the same name using the ALTER TABLE command. Figure 14 ); 8. If the user needs to disable this function, first change the GUC parameter to OFF. Figure 15 ); 9. After the instance restarts, refresh the index metadata in the database using the REINDEX command. At this time, the REINDEX index rebuilding mechanism will check for conflicts in the refreshed index names. If the user-specified index name conflicts with the index name of another relation table in the same namespace, the user will be prompted to use the ALTER TABLE command to modify the conflicting index name before re-REINDEX. Figure 16 ); like Figure 16 As shown, since both t1 and t2 in the public namespace have an index named idx_114514, pg_class cannot be written during REINDEX due to a uniqueness conflict. The system prompts the user to resolve the index name conflict by reporting an error. 10. After changing the conflicting index name to the new name using the ALTER TABLE command, re-execute the REINDEX command. Figure 17 ); 11. Checking the index metadata in pg_class again, it can be seen that the index name has been restored to the traditional index creation mode. Figure 18 ).

[0060] The above describes the specific implementation process of the MySQL-compatible index creation and management mechanism based on the openGauss database, as described in this invention. This method controls the database's index name rewriting behavior through GUC parameters. Transparently for users, it implicitly rewrites index names to solve the migration problem caused by the inability to create indexes when migrating from databases like MySQL (which allow duplicate index names) to openGauss databases, where object names in the same namespace cannot be duplicated under the existing metadata architecture of the openGauss database. The invention's solution is simple in principle, requires no modification to pg_class, does not introduce significant changes to the kernel, and possesses both security and stability.

[0061] Figure 6The application provides a duplicate index creation and reconstruction device based on openGauss. The duplicate index judgment module is used for obtaining an index creation request and judging whether a duplicate index function is enabled; the duplicate index function is controlled through a GUC parameter ENABLE_DUPLICATE_INDEXNAME, when the parameter is in an open state, a system table judgment is performed, and when the parameter is in a closed state, an index is created according to an openGauss original index naming mechanism; The system table judgment module is used for judging whether an object to which a current index belongs is a system table index; if yes, an index is created according to an openGauss original index naming mechanism; if not, a rewriting processing procedure is performed; The rewriting processing module is used for performing rewriting processing on the name of a user table index to generate a rewritten index name; the rewriting processing is to append an OID of a data table to which the index belongs to a user-specified original index name, and use an invisible control character with an ASCII code 0x1F as a separator to form a format of "original index name_0x1F table OID"; The rewritten name detection module is used for checking whether the length of the rewritten index name exceeds an openGauss limit constant NAMEDATALEN; if not, the rewritten index name is written into a relname field of a pg_class system table, and an association relationship between the index and a data table is supplemented in a pg_index system table, and index creation is completed; if yes, an error is reported to terminate the creation procedure.

[0062] The device realizes the steps of the duplicate index creation and reconstruction method based on openGauss disclosed by the application.

[0063] The flowcharts and block diagrams in the drawings show possible implementation manners of the devices, methods and computer program products according to various embodiments of the application, including architecture, functions and operations. In these diagrams, each block can represent a module, a program segment or a part of code, which contains one or more executable instructions for realizing the specified logical function. It should be noted that each block in the block diagram and / or flowchart and the combination of these blocks can realize the specified function or operation by using a special hardware-based system or by combining special hardware and computer instructions.

[0064] As Figure 7As shown, the embodiments of the present application also disclose an electronic device, comprising: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. Wherein the processor 310, the communication interface 320 and the memory 330 complete mutual communication through the communication bus 340. The processor 310 realizes the steps of the openGauss-based duplicate index creation and reconstruction method described above by running the executable computer program.

[0065] It can be understood that, in addition to the memory and the processor, the electronic device can also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through an I / O interface (i.e., an input / output interface).

[0066] The operations of the present application can be implemented by using one or more program design languages or combinations thereof to write computer program codes.

[0067] The execution modes of the program codes include but are not limited to: completely on a user computer; partly on a user computer and partly on a remote computer; as a separate software package; completely on a remote computer or server.

[0068] In the scenario involving a remote computer, the remote computer can be connected to the user computer through any type of network, including but not limited to a local area network (LAN) or a wide area network (WAN). In addition, the remote computer can also be connected to an external computer through an Internet service provider, such as a connection through the Internet.

[0069] Further, the present application also discloses a computer-readable storage medium, when the instructions in the computer-readable storage medium are executed by the processor of the electronic device, the electronic device can execute each step of the openGauss-based duplicate index creation and reconstruction method disclosed by the present application.

[0070] In the context of the present application, the computer-readable storage medium refers to a tangible medium capable of storing computer program codes and related data. Specific examples include but are not limited to the following: (1) Portable computer disk: such as a removable magnetic storage medium such as a floppy disk.

[0071] (2) Hard disk: including fixed storage devices such as mechanical hard disks and solid state disks.

[0072] (3) Random Access Memory (RAM): a volatile storage medium used for temporary storage of data and program code.

[0073] (4) Read-Only Memory (ROM): a non-volatile storage medium used for storing fixed programs and data.

[0074] (5) Erasable Programmable Read-Only Memory (EPROM) or Flash Memory: a non-volatile storage medium that supports multiple erasing and programming.

[0075] (6) Optical Fiber Storage Device: a storage medium based on fiber optic technology.

[0076] (7) Portable Compact Disc Read-Only Memory (CD-ROM): a read-only medium that stores data in the form of optical discs.

[0077] (8) Optical Storage Device: a storage medium based on optical principles such as DVD, Blu-ray Disc, etc.

[0078] (9) Magnetic Storage Device: a storage medium based on magnetic principles such as magnetic tape, magnetic disk, etc.

[0079] These computer-readable storage media can be used to store the program code and related data described in this application to support the running of programs and the persistent storage of data.

[0080] In particular, according to the embodiments of the present application, the processes described in the flowcharts can be implemented as a computer software program. For example, the embodiments of the present application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. The computer program contains program code for executing the openGauss-based duplicate index creation and reconstruction method disclosed in this application. When the computer program is executed by a processing device, the above-mentioned functions defined in the embodiments of the present application can be realized.

[0081] Although the above discussion contains several specific implementation details, these details should not be interpreted as limiting the scope of the present application. The above description is only a preferred embodiment of the present application and an explanation of the principles of technology used. Those skilled in the art should understand that the scope of the disclosure involved in the present application is not limited to the technical solutions formed by the specific combination of the above technical features. At the same time, the present application should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concept.

[0082] Those skilled in the art should understand that they can make modifications to the technical solutions recorded in the foregoing embodiments, or make equivalent replacements to some of the technical features without departing from the spirit and scope of the technical solutions of the embodiments of the present application. The modifications or replacements will not cause the nature of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. An openGauss-based method for creating and reconstructing a duplicate index, characterized in that, The method comprises: S1: Obtain an index creation request, and judge whether a duplicate index function is enabled; the duplicate index function is controlled by a GUC parameter ENABLE_DUPLICATE_INDEXNAME; when the parameter is in an open state, step S2 is executed; when the parameter is in a closed state, an index is created according to an openGauss native index naming mechanism; S2: Judge whether an object to which a current index belongs is a system table index; if yes, an index is created according to an openGauss native index naming mechanism; if no, step S3 is executed; S3: Rewrite a name of a user table index, and generate a rewritten index name; the rewriting comprises appending an OID of a data table to which the index belongs to a user-specified original index name, and using an invisible control character with an ASCII code 0x1F as a separator to form a format of "original index name_0x1F table OID"; S4: Check whether a length of the rewritten index name exceeds an openGauss limit constant NAMEDATALEN; if no, the rewritten index name is written into a relname field of a pg_class system table, and an association relationship between the index and the data table is supplemented in a pg_index system table, and index creation is completed; if yes, an error is reported to terminate the creation process.

2. The method of claim 1, wherein, The separator in step S3 is the invisible control character with the ASCII code 0x1F, which is used to clearly distinguish the user-specified index name and the appended table OID, so as to avoid naming ambiguity or conflict caused by common characters.

3. The method of claim 1, wherein, The method further comprises a REINDEX index reconstruction mechanism, which is used to realize smooth transition of new and old index formats; specifically comprising: When a user executes a REINDEX DATABASE, REINDEX SCHEMA or REINDEX TABLE command, all index objects in a target range are scanned, and the following judgment is executed for each index to be reconstructed: If the index name already contains the separator 0x1F and the table OID suffix, the index is skipped; If the index name does not contain the separator 0x1F and the table OID suffix, the table OID and the separator 0x1F are automatically appended to the index during the reconstruction process, the index name field in the pg_class system table is updated, and migration of the index format is completed.

4. The method of claim 1, wherein, When the duplicate index function is closed (that is, the ENABLE_DUPLICATE_INDEXNAME parameter is in a closed state), the user executes the REINDEX command to trigger the following processing: All index objects in a target range are scanned, and if the index name contains the separator 0x1F and the table OID suffix, a name conflict of the index with other indexes in the same namespace is detected; if there is a conflict, an error is reported to prompt the user to modify the conflict index name through an ALTER TABLE command and then re-execute the REINDEX.

5. The method of claim 1, wherein, When the user executes the INDEX HINT or ALTER INDEX command, the system automatically splices the index name input by the user with the OID of the data table to which the index belongs and the separator 0x1F in the internal parsing stage, restores the rewritten index name actually stored in the system table, and is used for accurate retrieval of the target index in the pg_class and pg_index system tables.

6. The method of claim 1, wherein, The system table includes pg_class and pg_index; wherein, the pg_class stores the basic metadata of the index, and the pg_index stores the association relationship between the index and the data table.

7. An openGauss-based duplicate index creation and reconstruction apparatus, characterized by comprising: The device includes: The duplicate index judgment module is used for obtaining an index creation request and judging whether to enable the duplicate index function; the duplicate index function is controlled by the GUC parameter ENABLE_DUPLICATE_INDEXNAME; when the parameter is in the open state, the system table judgment is executed; when the parameter is in the closed state, the index is created according to the openGauss native index naming mechanism; The system table judgment module is used for judging whether the object to which the current index belongs is a system table index; if it is a system table index, the index is created according to the openGauss native index naming mechanism; if it is a user table index, the rewriting processing flow is executed; The rewriting processing module is used for rewriting the name of the user table index to generate a rewritten index name; the rewriting processing is to append the OID of the data table to which the index belongs to the original index name specified by the user, and use the invisible control character of ASCII code 0x1F as a separator to form the format of "original index name_0x1F table OID"; The rewritten name detection module is used for checking whether the length of the rewritten index name exceeds the openGauss limit constant NAMEDATALEN; if not, the rewritten index name is written into the relname field of the pg_class system table, and the association relationship between the index and the data table is supplemented in the pg_index system table to complete the index creation; if it exceeds, an error is reported to terminate the creation process.

8. The apparatus of claim 7, wherein, The device further includes: The function control module is used for controlling the enablement or closed state of the duplicate index function through the GUC parameter ENABLE_DUPLICATE_INDEXNAME; The index reconstruction module is used for responding to the REINDEX command, scanning the index objects in the target range, automatically appending the suffix to the index that has not been rewritten and updating the metadata, and skipping the processing of the index that has been rewritten; The transparent parsing module is used for automatically splicing the index name input by the user with the table OID and the separator 0x1F to restore the index name actually stored in the system table when the user executes the INDEX HINT or ALTER INDEX command, and guaranteeing the correctness of the user operation.

9. A computer-readable storage medium having stored thereon a computer program, characterized in that, The computer program is executed by the processor to realize the steps of the duplicate index creation and reconstruction method based on openGauss according to any one of claims 1-6.

10. An electronic device, comprising: It includes: Memory and processor; Memory: used for storing computer programs; Processor: used to execute the computer program to realize the steps of the openGauss-based duplicate index creation and reconstruction method according to any one of claims 1-6.

Images