A database-based event trigger implementation method, system and electronic device

Abstract

This application discloses a database-based event trigger implementation method and system. The method includes: embedding a trigger call entry point into the database kernel error handling process; automatically triggering the SERVERERROR handling process when a runtime error event is detected; filtering and judging the error event by type; if it does not belong to the excluded type, retrieving the trigger list from the cache; executing the trigger function in an autonomous transaction; committing or rolling back the transaction based on the execution result; and sending an error report to the client. This invention achieves unified real-time capture and automated response to kernel-level error events, ensures the isolation of trigger execution from the main transaction through an autonomous transaction mechanism, and employs recursive protection and cache optimization design to ensure system stability and execution efficiency. It solves the problems of delayed application-layer error handling response, limited coverage, and difficulty in management in distributed scenarios in existing technologies.

Description

Technical Field

[0001] This application relates to the technical field of database operation and maintenance and event trigger implementation, and in particular to an event trigger implementation method, system, computer-readable storage medium and electronic device based on openGauss database. Background Technology

[0002] In the field of database operation and maintenance, real-time capture and automated processing of various error events are crucial for ensuring system stability and data security. Existing trigger mechanisms in the openGauss database are primarily designed for Data Manipulation Language (DML) and Data Definition Language (DDL) events, limiting their event monitoring scope to explicitly user-initiated SQL operations and failing to cover internal error events thrown during database kernel operation.

[0003] Currently, the industry commonly uses application-layer exception handling solutions for monitoring and handling database error events. This solution has the following technical drawbacks:

[0004] (1) High architecture coupling: The application layer needs to embed a large amount of exception handling logic in the business code, which leads to deep coupling between the business code and the error handling logic, significantly increasing the complexity of system development and maintenance;

[0005] (2) Poor response timeliness: Error events need to be passed through multiple levels from the database kernel to the driver layer to the application layer before they can be captured, resulting in significant processing delays and making it difficult to meet the requirements for rapid fault response in high availability scenarios;

[0006] (3) Limited coverage: The application layer can only perceive errors related to the current session and cannot capture background processes, system-level exceptions and other session-triggered error events, resulting in a large monitoring blind spot;

[0007] (4) Insufficient global control capabilities: In a distributed deployment architecture, errors on each node are scattered across different application instances, lacking a centralized error handling mechanism, making it difficult to achieve a globally unified error control strategy;

[0008] (5) Weak audit traceability: The existing system audit function mainly records operation logs and lacks the ability to automatically collect and persist complete context information (such as error stack, session state, system resource usage, etc.) at the time of the error, which is not conducive to post-fault root cause analysis.

[0009] (6) Lack of strategy flexibility: It is impossible to configure differentiated automated processing strategies (such as alarm notification, session termination, automatic repair, etc.) based on error type, severity level, occurrence scenario, etc., and the level of intelligent operation and maintenance needs to be improved.

[0010] In summary, existing technical solutions have significant shortcomings in terms of real-time performance, comprehensiveness, automation, and distributed adaptability in error event capture. There is an urgent need for a new mechanism that can directly capture and process error events at the database kernel level. Summary of the Invention

[0011] To address the aforementioned problems in the existing technology, this invention proposes a novel method for implementing event triggers based on the openGauss database.

[0012] More specifically, this invention aims to provide a method and system for implementing a database SERVERERROR trigger to solve the following problems:

[0013] (1) How to implement real-time capture of kernel-level error events in the openGauss database;

[0014] (2) How to provide a flexible error triggering rule definition mechanism to adapt to diverse error scenarios;

[0015] (3) How to avoid the risk of recursive triggering during the execution of triggers;

[0016] (4) How to ensure the efficiency and stability of trigger execution.

[0017] To achieve the above objectives, this application provides the following technical solution:

[0018] The first aspect of this application provides a database-based event trigger implementation method, the method being implemented using the openGauss database, such as... Figure 1 As shown, the method includes the following steps:

[0019] S1. Embed trigger call entry points in the database kernel's error handling process. When the database kernel detects a runtime error event, it automatically triggers the SERVERERROR event handling process before reporting the error to the client.

[0020] S2. Perform error type filtering and judgment on the error event. If the error event belongs to a predefined exclusion type, skip the execution of the trigger; if it does not belong to an exclusion type, search for the SERVERERROR trigger in the database and obtain the list of triggers that match the error event from the cache.

[0021] S3. Traverse the trigger list and execute the trigger functions corresponding to the trigger list in the autonomous transaction of the database kernel to achieve logic isolation from the main transaction;

[0022] S4. Perform branching based on the execution result of the trigger function: If execution is normal, submit the trigger function and send an error report to the client; if an execution error occurs, roll back the trigger function, capture the error, and send an error report to the client.

[0023] Furthermore, in the method of this application, the embedding method of the trigger call entry in step S1 includes:

[0024] Extend the database's CREATE TRIGGER syntax to natively support the definition of AFTER SERVERERROR ON DATABASE class server error triggers;

[0025] Integrate the SERVERERROR trigger's call entry point into the database kernel's error handling main loop to ensure that the predefined processing flow is triggered before reporting errors to the client.

[0026] Furthermore, in the method of this application, the error type filtering judgment in step S2 is implemented through the following mechanism: establishing error type "whitelist" or "blacklist" filtering rules to automatically exclude the following types of errors:

[0027] Internal system error (e.g., SQLSTATE code is of class XX000);

[0028] Data corruption type error (e.g., SQLSTATE code XX001 / XX002);

[0029] Resource exhaustion type errors (such as SQLSTATE codes 53000 disk full, 54000 insufficient memory).

[0030] Furthermore, in the method of this application, the error type filtering and judgment in step S2 is achieved by directly comparing the current error code with the target error code. If the current error code and the target error code are consistent, the process exits directly; otherwise, the subsequent triggering process is executed.

[0031] Furthermore, in the method of this application, the execution of the IS_SERVERERROR(error_code) interface includes: verifying whether the current execution context is in the SERVERERROR trigger context; if not, throwing a usage restriction error; if so, obtaining the current error code from the autonomous transaction context or error stack, converting the SQLSTATE encoding to an Oracle format error code, and then comparing it with the target error code.

[0032] Furthermore, in the method of this application, the step S2 of obtaining the list of triggers matching the error event from the cache includes: constructing an event trigger caching mechanism based on a hash table, and pre-constructing a rule index by scanning the system table pg_event_trigger. When an error occurs (especially in high-frequency triggering scenarios), the corresponding trigger list is directly searched and obtained from the cache according to the error type.

[0033] Furthermore, in the method of this application, the execution of the trigger function corresponding to the trigger list in the autonomous transaction of the database kernel in step S3 includes: passing the event trigger data to an independent autonomous transaction execution environment through shared memory, so that the trigger function is executed in the autonomous transaction to isolate the rollback effect of the main transaction.

[0034] Furthermore, the method in this application also includes a recursive protection step:

[0035] The built-in recursive call detection logic maintains the trigger call stack during trigger execution. When a recursive call is detected, the trigger execution is terminated to prevent the trigger itself from causing a recursive infinite loop due to an error in its execution.

[0036] A second aspect of this application provides a database-based event trigger implementation system, wherein the system, when running, implements the steps of the aforementioned database-based event trigger implementation method, such as... Figure 5 As shown, the system includes:

[0037] The event definition module supports creating SERVERERROR triggers using SQL syntax, configuring triggering rules and associated processing logic functions;

[0038] An error identification module, embedded in the database kernel, is used to capture error events thrown by the kernel in real time and determine the error type through the IS_SERVERERROR interface;

[0039] The error filtering module is used to filter error events according to configured rules and exclude error events of predefined types.

[0040] The trigger execution module is used to execute trigger functions associated with filtered error events within an autonomous transaction;

[0041] The cache management module is used to cache trigger rules and execution results to improve lookup and execution efficiency;

[0042] The recursion protection module is used to detect recursive calls by maintaining the trigger call stack, preventing recursive infinite loops from occurring during trigger execution.

[0043] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0044] Memory: Used to store computer programs;

[0045] Processor: Used to execute the computer program to implement the steps of the aforementioned database-based event trigger implementation method.

[0046] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned database-based event trigger implementation method.

[0047] In summary, compared with the prior art, the solution of the present invention has the following advantages:

[0048] (1) Kernel-level unified management and control: Error handling capabilities are pushed down to the database kernel, realizing unified, real-time monitoring and automated response to errors across the entire database.

[0049] (2) Highly flexible strategy: Allows database administrators to define differentiated processing strategies (such as alarms, logging, and attempts to repair) based on different error codes and error types.

[0050] (3) Transaction security without interference: Based on the execution model of autonomous transactions, it ensures that the execution of trigger logic is completely decoupled from the success / failure of the main business.

[0051] (4) Robust and reliable system: Through special error filtering and recursive protection design, the risk of database service avalanche caused by the introduction of trigger mechanism is fundamentally eliminated.

[0052] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0053] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0054] Figure 1 This is a flowchart illustrating the overall implementation of the database-based event trigger method of the present invention.

[0055] Figure 2 This is a flowchart illustrating the execution of the SERVERERROR event trigger in the method of this invention.

[0056] Figure 3 This is a diagram showing the overall design architecture of the system of the present invention.

[0057] Figure 4 This is a flowchart illustrating the execution of the error identification interface in the method of this invention.

[0058] Figure 5 This is a structural diagram of the database-based event trigger implementation system of the present invention.

[0059] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0060] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0061] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0062] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0063] An implementation method for event triggers based on the openGauss database

[0064] I. This invention provides a method for implementing a SERVERERROR trigger, including the following core design:

[0065] (I) Trigger Definition and Integration

[0066] 1. New syntax: Extend the CREATE TRIGGER syntax in openGauss to natively support the definition of AFTERSERVERERROR ON DATABASE class server error triggers.

[0067] 2. Kernel-level integration: Seamlessly integrate the trigger call entry point into the database kernel's error handling main loop, ensuring that the predefined SERVERERROR event handling process can be automatically and immediately triggered before any errors are reported to the client.

[0068] (ii) Accurate error identification and filtering

[0069] 1. Dedicated Interface: Set up the IS_SERVERERROR(error_code) judgment interface for use only by the SERVERERROR trigger, for accurate identification of error type.

[0070] 2. Security Filtering Mechanism: A built-in error type "whitelist" or "blacklist" mechanism has been established to automatically exclude the triggering of specific types of errors, for example:

[0071] Internal system errors (such as XX000): These are system-specific problems and should not trigger user programs.

[0072] Data corruption errors (such as XX001 / XX002): Triggering user actions at this time may exacerbate the problem.

[0073] Resource exhaustion errors (e.g., 53000 disk full, 54000 insufficient memory): The system is no longer able to effectively execute additional user code.

[0074] (III) Implementing a stability guarantee mechanism

[0075] 1. Recursion Protection: Built-in recursive call detection logic prevents infinite loop "error-trigger" deadlocks caused by errors in the execution of the trigger itself, ensuring system stability.

[0076] 2. Autonomous Transaction Support: Since the trigger is fired in the context of a transaction that has encountered an error and is about to be rolled back, this solution uses the technique of passing the context through shared memory to pass the critical error code information to an independent autonomous transaction (Worker thread) to execute the user function, thereby completely isolating the rollback impact of the main transaction and ensuring that the trigger logic can be reliably executed and committed (such as logging).

[0077] (iv) Performance optimization design

[0078] Trigger Caching: This solution introduces a hash table-based event trigger caching mechanism. By scanning the pre-built rule index of the system table pg_event_trigger, the list of triggers that need to be executed can be quickly found when an error occurs, significantly improving the search and execution efficiency in high-frequency triggering scenarios.

[0079] Figure 2 The diagram illustrates the execution flow of the SERVERERROR event trigger in the method of this invention. It shows the complete processing mechanism by which the database kernel triggers and executes a SERVERERROR type trigger after capturing an error event, including key steps such as error filtering, trigger lookup, function execution, and transaction processing. Specifically, it includes the following main steps:

[0080] 1. Error event triggering

[0081] When a database error occurs, that is, when the database kernel detects a runtime error event.

[0082] 2. Error type filtering and judgment

[0083] The system first performs an error type filtering check to determine whether the current error belongs to a predefined exclusion type:

[0084] If it belongs to the exclusion type, the trigger is skipped and the trigger processing flow is terminated directly.

[0085] If it does not fall under the exclusion category, continue with the subsequent procedures.

[0086] 3. Trigger Finding and Retrieval

[0087] Find the SERVERERROR trigger and retrieve the list of triggers from the cache.

[0088] 4. Trigger function execution

[0089] Execute the trigger function (the function is executed within an autonomous transaction) to ensure that the trigger logic is isolated from the main transaction and avoids mutual interference.

[0090] 5. Process the execution result branch and output an error report.

[0091] Conditional branching is based on the execution result of the trigger function:

[0092] If the execution is successful, the trigger function is submitted, and then an error report is sent to the client.

[0093] If an error occurs during execution, the trigger function is rolled back, the error in the trigger is captured, and then an error report is sent to the client.

[0094] II. This invention provides an event trigger implementation system based on the openGauss database. Figure 3 The figure shows the overall design architecture of the system of this invention. It illustrates the main components and collaborative operation mode of the event-triggered SERVERERROR trigger within the openGauss database kernel. This system includes the following components:

[0095] (a) Event Definition Module

[0096] This module provides users with methods for defining and managing triggers. It supports creating SERVERERROR triggers using SQL syntax. Users can specify the type of error to trigger (such as syntax error, permission error, resource exhaustion error, etc.), the error code range, the trigger timing (after the error occurs), and the associated processing logic function. This module has a built-in syntax validator to ensure the legality and correctness of rule definitions, and also supports importing and exporting rules for easy batch management and cross-environment migration.

[0097] (ii) Error Detection Module

[0098] The database SERVERERROR trigger is embedded in the openGauss database kernel, capturing all error events thrown by the kernel in real time. The IS_SERVERERROR interface is used to determine the error type, and execution strategies are flexibly set according to different types.

[0099] Figure 4 The diagram shows the execution flow of the error identification interface, which includes the following processes:

[0100] 1. Call the IS_SERVERERROR(target_error_code) function;

[0101] 2. Branching depends on the execution context:

[0102] If not in a trigger context, an error is thrown (can only be used in triggers);

[0103] If the following operations are performed in the trigger context:

[0104] (1) Obtain the current SQLerrcode: First obtain it from the autonomous transaction context; if it is not obtained, obtain it from the error stack.

[0105] (2) Convert the SQLSTATE encoding format to the Oracle format error code;

[0106] (3) Compare the converted error code with the target error code (target_error_code);

[0107] (4) Return the comparison result (TRUE or FALSE).

[0108] (III) Error Filtering Module

[0109] Based on the rules configured in the event definition module, this module filters the error events output by the error identification module. This module supports multi-condition filtering, such as error codes and database objects, and only passes error events that match the rules to the triggering execution module.

[0110] (iv) Recursive Protection Module

[0111] To prevent new errors caused by processing logic during trigger execution, which could lead to an infinite loop of recursive triggering, this module has a built-in recursion detection mechanism. By maintaining the trigger call stack, it avoids sending recursion errors.

[0112] (v) Triggering the execution module

[0113] This module receives valid error events from the error filtering module, loads and executes the associated processing logic functions. These processing logic functions must be executed within an autonomous transaction to avoid interference between the main transaction's transaction state and the trigger's transaction state. Furthermore, this module supports the passing of error event context information, providing rich error details for the processing logic.

[0114] (vi) Cache Management Module

[0115] Frequently triggered error event rules, processing scripts, and execution results are cached to improve system response speed and execution efficiency. This module employs an Least Recently Used (LRU) cache eviction strategy, dynamically adjusting cache size based on cache hit rate to balance memory usage and performance improvement. It also supports proactive cache refresh and invalidation mechanisms, ensuring timely synchronization of cached content when rules or scripts are updated. This caching mechanism effectively reduces redundant calculations and resource consumption, minimizing the impact on normal database operations.

[0116] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0117] Example 1: Automated Error Audit Log Recording

[0118] 1. Create an error log table

[0119] CREATE TABLE error_log (

[0120] id SERIAL PRIMARY KEY,

[0121] event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

[0122] event_name VARCHAR(100),

[0123] error_message VARCHAR(4000),

[0124] user_name VARCHAR(100),

[0125] session_id VARCHAR(50) );

[0127] 2. Create server error triggers

[0128] CREATE OR REPLACE TRIGGER servererror_trigger

[0129] AFTER SERVERERROR ON DATABASE

[0130] BEGIN

[0131] IF (IS_SERVERERROR(1017)) THEN

[0132] -- Oracle error code 1017: Invalid username / password

[0133] INSERT INTO error_log (event_name, error_message, user_name)

[0134] VALUES ('1017', 'Invalid username / password login attempt',

[0135] CURRENT_USER);

[0136] ELSIF (IS_SERVERERROR(1031)) THEN

[0137] -- Oracle error code 1031: Insufficient privileges

[0138] INSERT INTO error_log (event_name, error_message, user_name)

[0139] VALUES ('1031', 'Insufficient privileges',

[0140] CURRENT_USER);

[0141] ELSE

[0142] -- Other errors

[0143] INSERT INTO error_log (event_name, error_message, user_name)

[0144] VALUES ('OTHER', 'Other database error occurred',

[0145] CURRENT_USER);

[0146] END IF;

[0147] END;

[0148] /

[0149] -- No user is connected

[0150] SET ROLE not_exists_user PASSWORD'Aa!111111';

[0151] -- Query the error_log table to add a new row of data

[0152] SELECT * FROM error_log;

[0153] Effect: By creating an AFTER SERVERERROR ON DATABASE trigger, error details (time, user, session ID, etc.) can be automatically and independently inserted into a dedicated audit log table when a specific error (such as invalid login or insufficient privileges) occurs. Even if the main SQL statement is rolled back due to an error, this logging operation can still be successfully committed, achieving non-intrusive and reliable error auditing.

[0154] Example 2: Verifying Recursion Protection and Transaction Isolation

[0155] 1. Create an error log table

[0156] CREATE TABLE error_log (

[0157] id SERIAL PRIMARY KEY,

[0158] event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

[0159] event_name VARCHAR(100),

[0160] error_message VARCHAR(4000) );

[0162] 2. Create server error triggers

[0163] CREATE OR REPLACE TRIGGER servererror_trigger

[0164] AFTER SERVERERROR ON DATABASE

[0165] BEGIN

[0166] INSERT INTO error_log (event_name, error_message)

[0167] VALUES (TG_TAG, 'before trigger error');

[0168] RAISE_APPLICATION_ERROR(num => -20000, msg => 'trigger error');

[0169] INSERT INTO error_log (event_name, error_message)

[0170] VALUES (TG_TAG, 'after trigger error');

[0171] END IF;

[0172] END;

[0173] /

[0174] -- A division by zero error triggers the servererror trigger.

[0175] SELECT 1 / 0;

[0176] -- The error_log table is empty; no new data has been added.

[0177] SELECT * FROM error_log;

[0178] Effect: Design a trigger that actively throws an exception during execution. When it is triggered (e.g., by a "division by zero" error), the trigger's internal logic performs a partial operation first, then interrupts and rolls back automatically after explicitly throwing the exception. Test results show that: 1) the trigger's rollback does not affect the transaction state of the original external error at all; 2) the exception thrown internally is safely caught and logged without triggering a new SERVERERROR event, effectively avoiding recursive triggering.

[0179] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0180] like Figure 6 As shown in the illustration, an embodiment of this application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned database-based event trigger implementation method.

[0181] It is understood that, in addition to memory and a processor, this electronic device may 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 I / O interfaces (i.e., input / output interfaces).

[0182] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0183] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0184] Conventional procedural programming languages, such as "C" or similar programming languages.

[0185] The execution methods of program code include, but are not limited to:

[0186] It runs entirely on the user's computer;

[0187] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0188] Execute as a standalone software package;

[0189] It is executed entirely on a remote computer or server.

[0190] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0191] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the database-based event trigger implementation method disclosed in this application.

[0192] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0193] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0194] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

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

[0196] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0197] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0198] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0199] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0200] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0201] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0202] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

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

[0204] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the database-based event trigger implementation method disclosed in this application. When this computer program is executed by a processing system, it can achieve the functions defined in the embodiments of this application.

[0205] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0206] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A database-based event trigger implementation method, characterized in that, The method is based on the openGauss database and includes the following steps: S1. Embed trigger call entry points in the database kernel's error handling process. When the database kernel detects a runtime error event, it automatically triggers the SERVERERROR event handling process before reporting the error to the client. S2. Perform error type filtering and judgment on the error event. If the error event belongs to a predefined exclusion type, skip the execution of the trigger; if it does not belong to an exclusion type, search for the SERVERERROR trigger in the database and obtain the list of triggers that match the error event from the cache. S3. Traverse the trigger list and execute the trigger functions corresponding to the trigger list in the autonomous transaction of the database kernel to achieve logic isolation from the main transaction; S4. Perform branching based on the execution result of the trigger function: If execution is normal, submit the trigger function and send an error report to the client; if an execution error occurs, roll back the trigger function, capture the error, and send an error report to the client.

2. The method according to claim 1, characterized in that, The embedding method of the trigger call entry in step S1 includes: Extend the database's CREATE TRIGGER syntax to natively support the definition of AFTER SERVERERROR ON DATABASE class server error triggers; Integrate the SERVERERROR trigger's call entry point into the database kernel's error handling main loop to ensure that the predefined processing flow is triggered before reporting errors to the client.

3. The method according to claim 1, characterized in that, The error type filtering judgment in step S2 is achieved through the following mechanism: establishing error type "whitelist" or "blacklist" filtering rules to automatically exclude the following types of errors: Internal system error; Data corruption error; Resource depletion error.

4. The method according to claim 1, characterized in that, The error type filtering and judgment in step S2 is achieved by directly comparing the current error code with the target error code. If the current error code matches the target error code, the process exits directly; otherwise, the subsequent triggering process is executed.

5. The method according to claim 4, characterized in that, The execution of the IS_SERVERERROR(error_code) interface includes: verifying whether the current execution context is in the SERVERERROR trigger context; if not, throwing a usage restriction error; if so, obtaining the current error code from the autonomous transaction context or error stack, converting the SQLSTATE encoding to an Oracle format error code, and then comparing it with the target error code.

6. The method according to claim 1, characterized in that, Step S2, which involves retrieving the list of triggers matching the error event from the cache, includes: constructing an event trigger caching mechanism based on a hash table; scanning the pre-built rule index of the system table pg_event_trigger; and directly searching and retrieving the corresponding trigger list from the cache based on the error type when an error occurs.

7. The method according to claim 1, characterized in that, The step S3, which involves executing the trigger functions corresponding to the trigger list in the autonomous transaction of the database kernel, includes: passing the event trigger data to an independent autonomous transaction execution environment through shared memory, so that the trigger functions are executed in the autonomous transaction to isolate the rollback impact of the main transaction.

8. The method according to claim 1, characterized in that, The method also includes a recursive protection step: The built-in recursive call detection logic maintains the trigger call stack during trigger execution. When a recursive call is detected, the trigger execution is terminated to prevent the trigger itself from causing a recursive infinite loop due to an error in its execution.

9. A database-based event trigger implementation system, characterized in that, The system implements the steps of the database-based event trigger implementation method as described in any one of claims 1-8 during runtime, and the system includes: The event definition module supports creating SERVERERROR triggers using SQL syntax, configuring triggering rules and associated processing logic functions; An error identification module, embedded in the database kernel, is used to capture error events thrown by the kernel in real time and determine the error type through the IS_SERVERERROR interface; The error filtering module is used to filter error events according to configured rules and exclude error events of predefined types. The trigger execution module is used to execute trigger functions associated with filtered error events within an autonomous transaction; The cache management module is used to cache trigger rules and execution results to improve lookup and execution efficiency; The recursion protection module is used to detect recursive calls by maintaining the trigger call stack, preventing recursive infinite loops from occurring during trigger execution.

10. An electronic device, characterized in that, include: Memory and processor; Memory: Used to store computer programs; Processor: for executing the computer program to implement the steps of the database-based event trigger implementation method as described in any one of claims 1-8.

Images