Method, device, storage medium and program product for processing temporary table of standby in database cluster

Abstract

The embodiment of the application provides a kind of database cluster in standby machine temporary table processing method, equipment, storage medium and program product, it is related to distributed field.The method comprises: receiving data definition language (DDL) operation request;In response to determining that the DDL operation request is the request of creating standby machine local temporary table, the pre-write log (WAL) level of DDL operation is set to the log level specially used for the creation of standby machine local temporary table;Local temporary table is allocated identifier, and the identifier includes object identifier and the cluster node identifier of backup node;The metadata of local temporary table is stored in the memory storage area locally of backup node, and the memory storage area is independent of the physical system table synchronized with master node.The method of the application increases the ability of standby machine to independently process local temporary data operation, and improves the utilization efficiency of overall resources of database cluster.

Description

Technical Field

[0001] This application relates to the field of distributed systems, and in particular to a method, device, storage medium, and program product for processing temporary tables on standby machines in a database cluster. Background Technology

[0002] As data volume and business complexity increase, database clusters employing a "one master, multiple standby" architecture have become a key technology for achieving high availability and load balancing. In this architecture, the primary master handles all data writes and consistency maintenance, while the standby machines provide data redundancy and query services by synchronizing with the primary master's write-ahead log (WAL). However, in practical applications, users often need to perform temporary data processing on the standby machines (such as temporary storage of intermediate results, preprocessing of complex queries, etc.), thus requiring the standby machines to have the ability to create local temporary tables.

[0003] In existing database cluster implementations, to ensure strong data consistency between primary and standby nodes, the operation permissions of the standby machine are typically strictly limited. A common implementation is to set the standby machine to read-only mode, allowing only the execution of data query statements, while all data definition language (DDL) and data manipulation language (DML) operations must be executed uniformly by the primary machine and recorded in the WAL log, and then synchronized to the standby machine for replay.

[0004] However, the above implementation method cannot support the temporary data processing needs of the standby machine, thus limiting the computing power of the standby machine. Summary of the Invention

[0005] This application provides a method, device, storage medium, and program product for processing temporary tables on a standby machine in a database cluster, in order to solve the technical problem that the standby machine cannot support local temporary data processing needs.

[0006] In a first aspect, embodiments of this application provide a method for processing temporary tables on a standby machine in a database cluster, applied to a standby node in a database cluster, which also includes a master node. The method includes:

[0007] Receive Data Definition Language (DDL) operation requests;

[0008] In response to determining that the DDL operation request is a request to create a local temporary table on the standby machine, the write-ahead log (WAL) level of the DDL operation is set to a log level dedicated to the creation of local temporary tables on the standby machine.

[0009] Assign an identifier to the local temporary table. The identifier includes the object identifier and the cluster node identifier of the standby node.

[0010] The metadata of the local temporary table is stored in the local memory storage area of ​​the standby node, which is independent of the physical system tables synchronized with the master node.

[0011] In this embodiment, by identifying and tiered processing of standby machine local temporary table creation requests, the write-ahead log level for such operations is adjusted to a specific level, preventing standby machine local temporary table operations from being written to the master-slave synchronization WAL log stream. Simultaneously, by embedding the standby machine node identifier into the object identifier allocation strategy, the globally unique identifier independently allocated by the standby machine is ensured within the cluster. Furthermore, by storing temporary table metadata in a separate local memory area on the standby machine, the binding management of temporary table metadata with the session lifecycle is achieved, completely isolating it from the global physical system tables. This allows the standby machine to securely and independently create and manage local temporary tables, effectively utilizing standby machine computing resources while ensuring strong consistency between master and standby data, thus improving the flexibility and overall performance of the database cluster in handling complex temporary computing tasks.

[0012] In one possible implementation, a dedicated log level for creating local temporary tables on the standby machine is used to instruct the log processing module to skip generating and broadcasting WAL records for operations marked at that level.

[0013] In this implementation, by limiting the special log level, the operation of creating a local temporary table on the standby machine is strictly restricted to a purely local operation, preventing the log of this operation from entering the global WAL synchronization stream. This allows the standby machine to create temporary tables while avoiding potential interference with the consistency of primary and standby data.

[0014] In one possible implementation, the metadata stored in the memory storage area is temporary metadata, and the data operations in the memory storage area are independent of the master node's log synchronization mechanism.

[0015] In this implementation, by defining the metadata of the standby local temporary table as temporary in-memory data and making its operation independent of the WAL synchronization mechanism, the temporary table is bound to the session lifecycle while avoiding interference with the core process of primary-standby data consistency. This enables the standby machine to independently manage the temporary data structure.

[0016] In one possible implementation, an identifier is assigned to the local temporary table, including:

[0017] Generate a unique object sequence number within the standby node for the local temporary table, which serves as the object identifier;

[0018] Obtain the unique node identifier of the backup node within the database cluster, and use it as the cluster node identifier;

[0019] The object identifier is combined with the cluster node identifier to form a globally unique identifier within the database cluster.

[0020] In this implementation, through the above steps, the identifier allocation module completes all the work from generation and acquisition to synthesis, ensuring that the backup node can efficiently allocate a unique identifier that is conflict-free across the entire cluster for the local temporary table without communicating with the master node or needing a global lock, thus providing a foundation for the independent management and secure isolation of the temporary table.

[0021] In one possible implementation, the method further includes:

[0022] After determining that the DDL operation request is for creating a local temporary table on the standby machine, query the context properties of the database session that initiated the DDL operation request.

[0023] Based on the context attributes, determine whether to use a log level specifically for creating local temporary tables on the standby machine for DDL operations.

[0024] This implementation, by introducing the aforementioned optional control logic, provides a more granular strategy for database management. It allows for the unified enabling of local temporary tables on standby machines at the cluster level, while selective enabling or disabling at the session or user level. This better meets business compatibility requirements and enhances the adaptability of the solution.

[0025] In one possible implementation, the method further includes:

[0026] For database sessions that create local temporary tables, maintain a temporary table management context;

[0027] Register the assigned identifier to the temporary table management context;

[0028] In response to the termination of the database session, the temporary table management context is destroyed, and invalidation cleanup of all identifiers registered therein is triggered.

[0029] In this implementation, by introducing a session-level temporary table management context, each session's temporary table resources are encapsulated within an independent context, avoiding interference or accidental cleanup between sessions. Furthermore, the lifecycle of temporary tables is bound to the user session, enabling automated management and preventing the residue or leakage of temporary table resources. Additionally, when a session terminates, all associated temporary tables are cleaned up at once by traversing the context, improving cleanup efficiency and ensuring that all temporary resources for that session are effectively released.

[0030] Secondly, this application provides a database cluster system, including a master node and a backup node, wherein the backup node includes:

[0031] The request processing module is configured to receive DDL operation requests;

[0032] The log level control module is configured to, in response to the request processing module determining that the DDL operation request is a request to create a local temporary table on the standby machine, set the WAL level of the operation to a log level dedicated to the creation of the local temporary table on the standby machine.

[0033] The identifier allocation module is configured to assign identifiers to the created local temporary tables, including object identifiers and cluster node identifiers for standby nodes.

[0034] The metadata storage module is configured to store the metadata of local temporary tables in a memory storage area maintained locally on the standby node, which is independent of the physical system tables synchronized with the master node.

[0035] The standby node of the database cluster system executes the method used to implement any one of the first aspects.

[0036] Thirdly, this application provides an electronic device, including: a processor and a memory communicatively connected to the processor;

[0037] The memory stores the instructions that the computer executes;

[0038] The processor executes computer-executable instructions stored in memory to implement any of the methods of the first aspect.

[0039] Fourthly, this application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method of any one of the first aspects.

[0040] Fifthly, this application provides a computer program product, including a computer program that, when executed by a processor, implements the method of any one of the first aspects. Attached Figure Description

[0041] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0042] Figure 1 A schematic diagram of a database master-slave cluster communication architecture provided for an embodiment of this application;

[0043] Figure 2 This application provides a schematic diagram of the process for handling temporary tables on standby machines in a database cluster.

[0044] Figure 3 A flowchart illustrating the execution rules of DDL operations in a database cluster, provided as an embodiment of this application;

[0045] Figure 4 This is a flowchart illustrating the WAL log writing rules in a standby environment, as provided in an embodiment of this application.

[0046] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0047] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.

[0048] It should be noted that the method, device, storage medium and program product for processing temporary tables on standby machines in a database cluster provided in this application can be used in the distributed field, or in any field other than distributed. This application does not limit the application field of the method, device, storage medium and program product for processing temporary tables on standby machines in a database cluster.

[0049] This application can be applied to distributed database cluster systems using a master-slave architecture, particularly in business scenarios where localized, temporary data operations and computations need to be performed on the standby node. In these scenarios, the standby node not only undertakes the conventional responsibilities of data redundancy and query load balancing, but also needs to act as an independent computing node to support temporary data storage and processing within a session. Typical applications include, but are not limited to:

[0050] Intermediate computations for complex analytical queries: When executing complex queries involving multi-stage aggregation, joins, or sorting on a standby machine, temporary tables can be created in the local session to store intermediate computation results, avoiding repeated access to remote data sources or the primary database, improving analytical efficiency and reducing the load on the primary database.

[0051] Temporary storage during data processing: When executing the entire process from data extraction and transformation to loading in a data warehouse standby environment, raw data extracted from the source system, intermediate tables during the transformation process, or verification results before final loading can be temporarily stored in local temporary tables. This allows the data processing flow to achieve a complete closed loop on the standby machine without affecting the consistency of the primary machine's data.

[0052] Business logic testing and fault drills: When conducting system high availability testing or fault switching drills, technicians can create temporary tables in the standby session to simulate the real business data state or store test verification results, thereby safely verifying the recovery logic such as standby data synchronization and service takeover, and ensuring that the drill process does not affect production data.

[0053] Report generation and data preprocessing: When generating periodic reports or performing data preprocessing on the standby machine, preprocessed summary data, filtered datasets, or calculated derived indicators can be stored in temporary tables, enabling report queries to respond quickly based on local temporary results while avoiding interference with the primary database transaction performance.

[0054] In these scenarios, local temporary tables are only visible to the database session that created them. Their lifecycle is bound to the session or transaction and they are automatically cleared after the session ends. This satisfies the local computing needs of the standby machine while avoiding conflicts with the primary machine's data and does not disrupt the data synchronization and consistency guarantee mechanism of the primary-standby architecture.

[0055] For the above application scenarios, existing technical solutions typically employ a strict separation mechanism between primary and backup roles and operational permissions. The specific implementation is as follows:

[0056] In a typical master-slave database cluster architecture, the master node, as the sole writable node, is responsible for handling all data definition and data operation requests. When a user needs to create a temporary table, this operation must be performed on the master node. After the master node completes the creation of the temporary table, it records this operation as a DDL transaction, along with any subsequent possible DML transactions, in the WAL log.

[0057] The standby machine is set to strict read-only mode, and its core task is to continuously receive and replay the WAL log stream from the primary machine. Through this mechanism, the standby machine sequentially reproduces all data changes that occur on the primary machine, thus logically maintaining a high degree of consistency with the primary machine's data state. If a user creates a temporary table on the primary machine, the table's definition and subsequent data changes will be synchronized to all standby machines via the WAL log. From the user's perspective, this temporary table is also visible on the standby machines, but the creation, management, and lifecycle control of this temporary table belong entirely to the primary machine.

[0058] The essence of this approach is to treat the standby machine as a read-only copy of the primary machine's data state. All creation and management processes for temporary objects are centralized under the primary machine, aiming to avoid risks such as data consistency conflicts, synchronization logic confusion, and inconsistent system states that may arise from the standby machine independently performing write operations.

[0059] However, the solution described above has the following technical problems:

[0060] Existing solutions avoid synchronization disruptions and data redundancy that might occur when the standby machine performs any write operations (including creating temporary tables) by prohibiting it from writing to the WAL. However, this restriction also directly results in the standby machine completely losing its ability to process local temporary data, making it unsuitable for complex computing scenarios that require temporary storage of intermediate results.

[0061] Furthermore, to avoid global conflicts that might arise from independently assigning object identifiers to standby machines, existing technologies centralize all identifier allocation rights to the primary machine. While this approach guarantees global uniqueness, it also prevents standby machines from logically becoming nodes capable of independently managing some metadata, thus limiting the flexibility and scalability of the distributed cluster architecture.

[0062] Furthermore, to prevent the metadata of the standby machine's temporary tables from polluting the host's global system tables and causing inconsistencies between the primary and standby systems, the existing solution prohibits the standby machine from creating any table objects that require persistent metadata. While this is the simplest to manage, it prevents the standby machine's local computing resources from being effectively used for computing tasks that require temporary storage structures, resulting in resource waste.

[0063] This application provides a method for handling temporary tables on standby machines in a database cluster, aiming to solve the aforementioned technical problems in existing technologies. By introducing a hierarchical WAL log mechanism, a distributed unique identifier allocation strategy, and memory-based metadata caching technology, it enables the creation and management of local temporary tables in the standby environment of a database cluster, while ensuring data consistency between the primary and standby machines. Specifically, addressing the problem that standby machines in traditional database clusters cannot create temporary tables, this application proposes three core innovations to respectively correspond to and solve the aforementioned three major technical problems:

[0064] Tiered WAL log mechanism: By adding a "standby temporary table creation log level," WAL log writing is skipped when a temporary table is created on the standby machine. This mechanism resolves the WAL log broadcast conflict issue, ensuring that local operations on the standby machine do not affect the core log stream used to ensure data consistency between the primary and standby machines.

[0065] Distributed unique identifier allocation strategy: A unique cluster node identifier (master or standby) is embedded within the existing global object identifier encoding format. This allows the standby node to independently allocate a unique identifier within its assigned identifier segment. This strategy avoids unique identifier allocation conflicts, ensuring that the identifier allocated by the standby node will not conflict with the master or other standby nodes across the entire cluster.

[0066] Memory-based metadata caching technology: This technology designs an independent memory-based metadata management area for the standby machine, storing metadata such as the table structure and permissions of local temporary tables in this cache area instead of writing them to the globally shared physical system tables. This technology addresses the issue of temporary table metadata storage, achieving localized and session-level lifecycle management of temporary table metadata without affecting the stability and consistency of the host system tables.

[0067] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.

[0068] The following is combined with Figure 1 and Figure 2 This application describes a method for handling temporary tables on standby machines in a database cluster, as provided in an embodiment. Figure 1 This is a schematic diagram of a database master-slave cluster communication architecture provided in an embodiment of this application. Figure 2 This is a flowchart illustrating the processing of temporary tables on a standby machine in a database cluster, as provided in this embodiment. This embodiment uses a standby node in the database cluster as the executing entity. The database cluster adopts a master-slave (or master-standby) architecture, including one master node and at least one standby node. Data synchronization between the master node and the standby node is achieved through write-ahead logs.

[0069] First, combine Figure 1 The system architecture on which this method operates is described. For example... Figure 1 As shown, this embodiment relates to a typical master-slave database cluster architecture, whose core communication logic is based on write-ahead logs to achieve data synchronization, and mainly includes the following roles and components:

[0070] Master node: As the host of the cluster, it is the only node responsible for handling all read and write requests and maintaining its own database instance.

[0071] Backup node: Serves as a backup machine in the cluster (e.g.) Figure 1 The standby nodes (standby 1, standby 2, and standby 3) are typically used to distribute read request load or provide fault backup. Each standby node runs an independent database instance.

[0072] WAL synchronization link: The master node synchronizes its write-ahead log (WAL) that records all data changes to all standby nodes. Standby nodes receive and replay these WAL logs to keep their local database state consistent with the master node. This is the core mechanism for achieving data redundancy, failover, and read / write separation.

[0073] The following is combined with Figure 2This section provides a detailed explanation of the specific implementation steps of this method on the backup node. For example... Figure 2 As shown, the method includes the following steps:

[0074] S201, Receive Data Definition Language (DDL) operation request.

[0075] Specifically, the backup node receives SQL statements from the connection session. After syntax parsing, the statement is identified as a DDL operation request. This DDL operation request may include creating a table, modifying a table, or deleting a table. In the scenario of this embodiment, the focus is on processing requests to create tables, especially requests to create temporary tables.

[0076] S202. In response to determining that the DDL operation request is a request to create a local temporary table on the standby machine, the write-ahead log (WAL) level of the DDL operation is set to a log level dedicated to the creation of local temporary tables on the standby machine.

[0077] The standby node needs to distinguish between regular DDL requests and local temporary table creation requests. In practice, the standby node can detect whether the DDL request contains keywords related to local temporary tables (e.g., CREATE TEMPORARY TABLE... or CREATE TEMP TABLE...) and whether the request explicitly specifies local attributes or is identified in the standby node context as a table that does not need to be synchronized to the primary node.

[0078] Once a request to create a local temporary table on the standby node is identified, the log level for that operation when generating the WAL log is set to a predefined, special level, such as "LOCAL_TEMP". The semantics of this level are: operations marked at this level have their WAL record generation process skipped, therefore they are neither written to the standby node's WAL buffer nor included in the WAL log stream that needs to be synchronized. This ensures that the creation of the local temporary table on the standby node is transparent to the primary node and other standby nodes, avoiding log broadcast conflicts.

[0079] S203. Assign an identifier to the local temporary table. The identifier includes the object identifier and the cluster node identifier of the standby node.

[0080] When creating a table object, it needs to be assigned a unique identifier within the database system. To resolve identifier conflicts in a distributed environment, this embodiment employs a node identifier embedding allocation strategy. Specifically, when assigning identifiers, backup nodes do not obtain them from a globally unified sequence, but instead use a composite identifier that combines the following two pieces of information:

[0081] Local sequence number: generated by an auto-incrementing sequence maintained locally by the backup node to ensure uniqueness within the backup node.

[0082] Cluster node identifier: A unique node ID assigned to the standby node during database cluster initialization, and is used throughout the entire cluster. It may be an integer encoding.

[0083] These two pieces of information are combined using bitwise operations or encoding rules to form a globally unique composite identifier. For example, the higher bits can be used to store the node identifier, and the lower bits can be used to store the local sequence number. In this way, even if different backup nodes are assigned identifiers simultaneously, the resulting composite identifier will always be globally unique because the node identifiers are different, thus avoiding conflicts with object identifiers assigned to the master node or other backup nodes.

[0084] S204. Store the metadata of the local temporary table in the local memory storage area of ​​the standby node. The memory storage area is independent of the physical system table synchronized with the master node.

[0085] Furthermore, the storage of metadata (such as table names, column definitions, constraints, etc.) is another key point. In traditional processing, table metadata is stored in a global system table on disk and synchronized via WAL. For local temporary tables on the standby node, this embodiment stores their metadata in a dedicated in-memory storage area. This in-memory storage area can be a hash table, a B-tree, or other efficient in-memory data structures, associated with the session or transaction context. Specific implementation logic includes:

[0086] Storage: After steps S202 and S203 are completed, the parsed table structure, the assigned identifier, the session ID, and other information are written as a record to the memory storage area.

[0087] Isolation: This memory storage area is physically and logically independent of the physical system table file that stores global persistent metadata. Other nodes (including the master node) cannot access this memory area.

[0088] Lifecycle Management: The lifecycle of this metadata record is bound to the session or transaction that created it. When the session ends or the transaction is rolled back, the corresponding metadata record is automatically cleared from the in-memory storage area, and related resources, such as temporary table data files, are released.

[0089] The standby temporary table handling method provided in this embodiment identifies and tiers standby local temporary table creation requests, adjusting the write-ahead log level for such operations to a specific level to prevent standby local temporary table operations from being written to the master-slave synchronization WAL log stream. Simultaneously, by embedding the standby node identifier into the object identifier allocation strategy, the globally unique identifier independently allocated by the standby is ensured across the cluster. Furthermore, by storing temporary table metadata in a separate local memory area on the standby, the binding management of temporary table metadata with the session lifecycle is achieved, completely isolating it from the global physical system tables. This allows the standby to securely and independently create and manage local temporary tables, effectively utilizing standby computing resources while ensuring strong consistency between master and standby data, thus improving the flexibility and overall performance of the database cluster in handling complex temporary computing tasks.

[0090] Optionally, this application also provides tiered processing of DDL requests based on the execution environment and operation type, particularly enabling a special WAL logging strategy for requests to create local temporary tables on standby machines. The following first combines... Figure 3 This implementation method will be explained. Figure 3 This is a flowchart illustrating the execution rules of DDL operations in a database cluster, as provided in an embodiment of this application.

[0091] like Figure 3 As shown, when a cluster node receives a DDL operation request (such as creating or modifying a table), the execution flow is as follows:

[0092] Determine the current environment: First, determine whether the node currently executing the request is in the primary or standby environment.

[0093] Host environment processing: If the current host is the host, the DDL operation is executed according to the normal process, the corresponding database object is created or modified, and the operation generates a standard WAL record, which is then broadcast synchronously to all standby nodes.

[0094] Standby environment processing: If the current system is a standby machine, the DDL request will be further subdivided and judged.

[0095] Non-local temporary table creation requests: For all other DDL operations (such as creating regular tables, modifying table structures, etc.), the standby environment will refuse to execute them by default and return an error message (e.g., "Standby cannot create global object"). This ensures that the standby will not perform operations that could compromise the consistency of data between the primary and standby servers.

[0096] Request to create a local temporary table: The standby machine is allowed to perform the operation and enter the processing flow defined in this application only if the DDL request is determined to be to create a local temporary table on the standby machine (for example, the SQL statement contains the explicit semantics of CREATE LOCAL TEMPORARY TABLE).

[0097] In combination with the above Figure 3 In this application, the key operation in step S202 of the above embodiment, "setting the write-ahead log (WAL) level of the DDL operation to a log level dedicated to the creation of local temporary tables on the standby machine," is further defined and clarified.

[0098] Specifically, when the backup environment is based on Figure 3 The rule-based request is for setting a specific log level (e.g., WAL_LEVEL_LOCAL_TEMP) after the creation of a local temporary table. Its core semantics and function are to instruct the log processing module in the database kernel to skip generating and broadcasting WAL records for operations marked at this level. The specific WAL processing rules triggered by this log level are determined by... Figure 4 The process is specified as shown. Figure 4 This is a flowchart illustrating a WAL log writing rule in a standby environment, as provided in an embodiment of this application. Figure 4 As shown, when the standby machine performs operations, its WAL log writing logic is as follows:

[0099] Operation type determination: Determine whether the operation to be executed is "create local temporary table in standby environment".

[0100] Branch execution:

[0101] If this is not the operation: For other operations permitted to be performed on the standby machine, the system will normally generate and write the WAL log.

[0102] If this operation is performed: that is, it is... Figure 3 If an operation is allowed to proceed and is marked as WAL_LEVEL_LOCAL_TEMP level, the system will skip generating and writing WAL records for that operation.

[0103] Corresponding to Figure 4 The implementation process of the "skip" branch in the code can be broken down as follows:

[0104] Level Tag: When the transaction processing module is preparing to generate a WAL record for this DDL operation, it adds the WAL_LEVEL_LOCAL_TEMP level tag to the WAL record header according to the request type (creating a local temporary table).

[0105] Log generation interception: When the WAL log writer receives and processes WAL records, it checks the corresponding level tag. When it finds the tag is WAL_LEVEL_LOCAL_TEMP, the log writer will not serialize the content and write it to the WAL file segment on disk as it would with a normal WAL record, thus preventing this from happening. Figure 4 Skip WAL log write operations in the process.

[0106] Broadcast process skipped: Accordingly, since no actual WAL record data is generated, the component responsible for log synchronization will not read the content related to this operation from the WAL log stream, so the operation will not be encapsulated in the WAL packet and broadcast to the master node or other standby nodes.

[0107] Local processing complete: Although WAL generation and broadcasting are skipped, other processing on the standby machine (such as allocating identifiers, creating metadata in local memory, etc.) still completes normally. Therefore, the temporary table is only visible and available in the standby machine session that created it.

[0108] By limiting the log level in this way, the operation of creating a local temporary table on the standby machine is strictly restricted to a purely local operation, preventing the log of this operation from entering the global WAL synchronization stream. This allows the standby machine to create temporary tables while avoiding potential interference with the consistency of primary and standby data.

[0109] Optionally, regarding the operation of storing the metadata of the local temporary table in the local memory storage area of ​​the standby node in step S204, this embodiment further clarifies its core implementation features as follows:

[0110] Temporary metadata storage: Metadata (such as table structure and column information) stored in this in-memory storage area has a clear temporality, and its lifecycle is bound to the database session or transaction that created the temporary table. When the session ends or the transaction is rolled back, the system automatically clears the corresponding metadata records and releases memory, and no persistent storage is performed on this type of metadata throughout the entire process.

[0111] Independent of the master node synchronization mechanism: All metadata operations on this local temporary table (including creation, structure modification, and deletion at the end of the session) are independent of the master node's WAL log synchronization mechanism. Specifically, these operations are not recorded in the standby machine's local WAL log file, and therefore are not broadcast to the master node or other standby nodes via WAL stream, thus achieving complete isolation from the master-standby data synchronization process in the architecture.

[0112] By defining the metadata of the standby local temporary table as temporary in-memory data and making its operation independent of the WAL synchronization mechanism, the temporary table is bound to the session lifecycle while avoiding interference with the core process of primary-standby data consistency. This enables the standby machine to independently manage the temporary data structure.

[0113] Optionally, in the complete process of creating a local temporary table on the standby node, the allocation of identifiers is a crucial step. This embodiment describes the specific steps for executing this step internally within the standby node. This process is completed autonomously by the standby node and does not rely on the master node or other external coordination.

[0114] Step 1: Generate a unique object sequence number within the backup node.

[0115] When the request processing module of the standby node determines that the received DDL request is to create a local temporary table and triggers subsequent processes, the identifier allocation module is invoked. This module first uses its internal sequence number generator to generate an object sequence number (i.e., the local sequence number mentioned earlier) that is unique only within the current standby node for the temporary table to be created. This generator typically maintains a local memory counter, which is incremented atomically to ensure the uniqueness of the sequence number in concurrent scenarios. The generated object sequence number serves as the object identifier for this temporary table.

[0116] Step 2: Obtain the cluster node identifier of the backup node.

[0117] The identifier allocation module needs to obtain the unique identifier of its node within the cluster while generating object serial numbers. This module reads the globally unique cluster node identifier assigned to its node during cluster initialization by accessing the standby node's local configuration information or cluster metadata. This identifier is used to distinguish different nodes across the cluster.

[0118] Step 3: Combine to form a globally unique identifier

[0119] After obtaining the two sets of data, the identifier allocation module combines the object sequence number (object identifier) ​​with the cluster node identifier into a composite identifier according to predefined, cluster-wide unified encoding rules. A typical combination method uses bitwise operations: the node identifier is placed in the high-order bit segment, and the object sequence number is placed in the low-order bit segment, concatenating them to form an integer value. Due to the global uniqueness of the node identifier, even if different backup nodes generate the same object sequence number, the combined complete identifier is still globally unique. Finally, this globally unique identifier is associated with a newly created local temporary table, and the metadata storage module can store it as one of the key attributes along with other metadata of the temporary table.

[0120] Through the above steps, the identifier allocation module completes all the work from generation and acquisition to synthesis, ensuring that the backup node can efficiently allocate a unique identifier that is conflict-free across the entire cluster for the local temporary table without communicating with the master node or needing a global lock, thus providing a foundation for the independent management and secure isolation of the temporary table.

[0121] Optionally, during the standby node's processing of a request to create a local temporary table, a session context-based control logic can be introduced to further determine whether to enable the aforementioned dedicated log level for the current operation. This control logic is executed after the standby node determines the DDL request type and before formally setting the WAL level, and specifically includes the following steps:

[0122] Step 1: Query the context attributes of the database session

[0123] When a standby node determines that a received DDL operation request is intended to create a local temporary table, the system will query the current context attributes of the database session that initiated the request before proceeding with the log level setting process. These attributes may include, but are not limited to:

[0124] Connection attributes of the session, such as options or flags in the connection string.

[0125] The configuration parameters currently active in the session, such as session-level parameters that control the behavior of temporary tables, set via the SET command.

[0126] The user role or permission group to which the session belongs.

[0127] Explicitly declared session-level identifiers or tags.

[0128] These attributes can be queried by accessing the local session management data structure on the backup node, without the need for cross-node communication.

[0129] Step 2: Determine whether to apply a dedicated log level based on the context.

[0130] After obtaining the relevant context attributes, the system will determine whether to apply the aforementioned log level specifically for creating local temporary tables on the standby machine, based on preset decision rules. These decision rules can be, for example, the following mechanisms:

[0131] Whitelist mechanism: Dedicated log levels are only allowed when the session connection attributes contain a specific flag (e.g., allow_local_temp=true) or the user role belongs to a specific management group.

[0132] Parameter control mechanism: Check the value of the session-level parameter. If the value is on, apply the special level; if it is off, execution may be refused even if a request is made to create a local temporary table.

[0133] Security policy mechanism: Combining user permissions and operational sensitivity, it determines whether to allow the creation of fully localized temporary tables that do not retain WAL.

[0134] If the decision is to apply, the process continues, setting up the dedicated WAL level as planned, and performing subsequent identifier allocation and metadata storage steps.

[0135] If the decision is not to apply, alternative actions may be taken, such as rejecting the DDL request and returning an error. The decision result and reason can be logged in the local log of the backup node.

[0136] By introducing the aforementioned optional control logic, a more granular strategy for database management is provided. This allows for the unified enabling of local temporary tables on standby machines at the cluster level, while selective enabling or disabling at the session or user level. This better meets business compatibility requirements and enhances the adaptability of the solution.

[0137] Optionally, while allowing the creation of local temporary tables on the standby machine, this embodiment provides a session-level temporary table management mechanism to ensure the isolation of temporary resources and automatic session-level reclamation. This mechanism maintains an independent temporary table management context on a per-database-session basis and binds it to the lifecycle of the session.

[0138] Step 1: Create and maintain the temporary table management context

[0139] Once the standby node determines that a dedicated WAL level has been applied to a local temporary table creation request for a database session and the temporary table has been successfully created, the system initializes and maintains a temporary table management context for that database session. If this is the first time a local temporary table is created in this session, a new context structure is created; otherwise, it is reused. This context is typically a data structure stored in the standby node's memory, associated with the session control block, and used to centrally manage the metadata and status of all local temporary tables created within this session.

[0140] Step 2: Register the identifier to the management context

[0141] Whenever a local temporary table is successfully created in a database session and assigned a globally unique identifier, the system registers this identifier and its associated key metadata (such as table name, creation timestamp, etc.) with the session's temporary table management context. This registration allows the system to quickly locate all local temporary table resources owned by the session through the session context.

[0142] Step 3: Trigger unified cleanup when the session terminates.

[0143] The core of this management mechanism is its tight integration with the session lifecycle. When a database session terminates due to normal disconnection, user logout, or system anomaly, the system will trigger a destruction process.

[0144] Specifically, the system first destroys the temporary table management context corresponding to the session. Before destruction, it iterates through all temporary table identifiers registered in the context and triggers a cleanup operation for each identifier. This cleanup operation includes:

[0145] Metadata deletion: Notify the metadata storage module to delete the temporary table metadata record corresponding to the identifier from the local memory storage area of ​​the standby node.

[0146] Storage space reclamation: Release all physical storage resources, such as data blocks and memory buffers, that the temporary table may occupy.

[0147] Invalid identifier: Mark the identifier as invalid internally to ensure that any subsequent attempts to access the temporary table through this identifier will fail or report an error.

[0148] By introducing a session-level temporary table management context, each session's temporary table resources are encapsulated within an independent context, avoiding interference or accidental cleanup between sessions. Furthermore, the lifecycle of temporary tables is bound to the user session, enabling automated management and preventing the residue or leakage of temporary table resources. In addition, when a session terminates, all associated temporary tables are cleaned up at once by traversing the context, improving cleanup efficiency and ensuring that all temporary resources for that session are effectively released.

[0149] The database cluster system provided in this application includes a backup node, which further includes the modules described above. When the backup node is executed, the modules work together to implement the methods in any of the above method embodiments.

[0150] The electronic device provided in this application embodiment can execute the method provided in the above method embodiment. Its implementation principle and technical effect are similar, and will not be described in detail here.

[0151] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the methods in any of the above method embodiments.

[0152] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the methods in any of the above method embodiments.

[0153] All or part of the steps in the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a readable memory. When the program is executed, it performs the steps of the above method embodiments; and the aforementioned memory (storage medium) includes: read-only memory (ROM), RAM, flash memory, hard disk, solid-state drive, magnetic tape, floppy disk, optical disk, and any combination thereof.

[0154] This application describes embodiments with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processing unit of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processing unit of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0155] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0156] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0157] Obviously, those skilled in the art can make various modifications and variations to the embodiments of this application without departing from the spirit and scope of this application. Therefore, if these modifications and variations to the embodiments of this application fall within the scope of the claims of this application and their equivalents, this application also intends to include these modifications and variations.

[0158] In this application, the term "comprising" and its variations can refer to non-limiting inclusion; the term "or" and its variations can refer to "and / or". The terms "first", "second", etc., in this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. In this application, "multiple" refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0159] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily essential to this application.

[0160] It should be further noted that although the steps in the flowchart are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowchart may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the sub-steps or stages of other steps.

[0161] Furthermore, unless otherwise specified, the functional units / modules in the various embodiments of this application can be integrated into one unit / module, or each unit / module can exist physically separately, or two or more units / modules can be integrated together. The integrated units / modules described above can be implemented in hardware or as software program modules.

[0162] In the above embodiments, the descriptions of each embodiment have their own emphasis. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments. The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as the combination of these technical features does not contradict each other, it should be considered within the scope of this specification.

[0163] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this application are indicated by the following claims.

[0164] It should be understood that this application is not limited to the precise structure described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this application is limited only by the appended claims.

Claims

1. A method for handling temporary tables on standby machines in a database cluster, characterized in that, The method, applied to a backup node in a database cluster, which also includes a master node, comprises: Receive Data Definition Language (DDL) operation requests; In response to determining that the DDL operation request is a request to create a local temporary table on the standby machine, the write-ahead log (WAL) level of the DDL operation is set to a log level dedicated to the creation of local temporary tables on the standby machine. Assign an identifier to the local temporary table, the identifier including an object identifier and a cluster node identifier for the backup node; The metadata of the local temporary table is stored in the local memory storage area of ​​the backup node, which is independent of the physical system table synchronized with the master node.

2. The method according to claim 1, characterized in that, The log level specifically for creating local temporary tables on the standby machine is used to instruct the log processing module to skip generating and broadcasting WAL records for operations marked with the level.

3. The method according to claim 1, characterized in that, The metadata stored in the memory storage area is temporary metadata, and the data operations in the memory storage area are independent of the master node's log synchronization mechanism.

4. The method according to any one of claims 1-3, characterized in that, Assigning an identifier to the local temporary table includes: Generate a unique object sequence number within the backup node for the local temporary table, which serves as the object identifier; Obtain the unique node identifier of the backup node within the database cluster, and use it as the cluster node identifier; The object identifier is combined with the cluster node identifier to form a globally unique identifier within the database cluster.

5. The method according to claim 1, characterized in that, The method further includes: After determining that the DDL operation request is a request to create a local temporary table on the standby machine, query the context attributes of the database session that initiated the DDL operation request. Based on the context attributes, determine whether to apply the log level specifically for creating local temporary tables on the standby machine to the DDL operation.

6. The method according to claim 5, characterized in that, The method further includes: To create the database session for the local temporary table, a temporary table management context is maintained; Register the assigned identifier to the temporary table management context; In response to the termination of the database session, the temporary table management context is destroyed, and invalidation cleanup of all identifiers registered therein is triggered.

7. A database cluster system, comprising a master node and a standby node, characterized in that, The backup nodes include: The request processing module is configured to receive DDL operation requests; The log level control module is configured to, in response to the request processing module determining that the DDL operation request is a request to create a local temporary table on the standby machine, set the WAL level of the operation to a log level dedicated to the creation of a local temporary table on the standby machine. The identifier allocation module is configured to allocate identifiers to the created local temporary tables, the identifiers including object identifiers and cluster node identifiers of the backup node; The metadata storage module is configured to store the metadata of the local temporary table in a memory storage area maintained locally by the backup node, the memory storage area being independent of the physical system tables synchronized with the master node.

8. An electronic device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1 to 7.

10. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method of any one of claims 1 to 7.

Images