A database field duplicate value detection method, device and medium

Abstract

The application relates to the technical field of databases, and discloses a database field duplicate value detection method, equipment and medium, which are applied to migration of unique identifier data of different types of databases to a PostgreSQL database, and comprise the following steps: in a PostgreSQL plug-in, a target table for persistently storing existing unique identifier data is created; an instance of an in-memory database is started, a communication connection with the in-memory database is established, and a hook function for duplicate detection when a unique identifier is generated in the PostgreSQL database is registered; when a duplicate checking function of the PostgreSQL plug-in is enabled, existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and data in the target table is synchronized to the in-memory database, wherein a unique identifier of each piece of data is stored as a key and is associated with a predetermined value; when an operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a duplicate checking operation.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, device and medium for detecting duplicate values ​​in database fields. Background Technology

[0002] As enterprise business systems evolve and data architectures upgrade, migrating data from various source databases (such as MySQL and Oracle) to PostgreSQL databases has become a common requirement. During such migrations, ensuring data integrity and consistency is crucial, and the uniqueness of unique identifiers (such as UUIDs and auto-incrementing sequences) is a key aspect. These identifiers typically serve as primary keys or unique credentials for business entities in the original business system; after migration, they must remain globally unique in the new PostgreSQL database to prevent data conflicts and business logic errors.

[0003] Currently, after data migration is complete, the PostgreSQL database continuously generates new unique identifiers during subsequent operation. Existing technologies have significant shortcomings in ensuring that newly generated identifiers do not duplicate previously migrated identifiers.

[0004] There are two common approaches: one is to add a database-level unique constraint to the new identifier field. However, this method has a serious drawback: if the generation operation happens to produce a value that duplicates historical data, the database will throw a unique constraint violation error, causing the entire generation transaction to be forcibly interrupted, affecting business continuity and user experience. The other approach is to execute an additional query after the identifier is generated to check its uniqueness across the entire target table. While this approach avoids transaction interruption, it introduces additional, high-frequency database query operations. Especially when dealing with large amounts of data or high generation concurrency, this post-hoc check can lead to significant performance overhead, increasing the response time of business operations and impacting the overall system throughput. Summary of the Invention

[0005] This specification provides one or more embodiments of a method, device, and medium for detecting duplicate values ​​in database fields, which are used to solve the technical problems mentioned in the background art.

[0006] One or more embodiments of this specification employ the following technical solutions: This specification provides one or more embodiments of a database field duplicate value detection method, applied to the migration of unique identifier data from different types of databases to a PostgreSQL database. The method includes: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0007] It's worth noting that this method innovatively introduces a real-time deduplication mechanism supported by an in-memory database at the critical path of unique identifier generation by designing a plugin deeply integrated into the PostgreSQL database. This plugin first persistently stores existing unique identifiers (including historical migration data) in a database table and simultaneously loads them all into the in-memory database for high-speed access. Its core lies in registering a hook function that is immediately invoked after the database performs the new identifier generation operation, subsequently initiating a query to the in-memory database at millisecond speeds. If a duplicate is detected, the plugin not only immediately triggers a retry to ensure business continuity but also automatically cleans up conflicting data in the cache and persistent tables to ensure state consistency; if the detection passes, the result is returned immediately. This design transforms duplicate detection from a traditional, post-hoc, high-latency, and high-overhead disk query into a pre-hoc, embedded, in-memory high-speed verification process. Therefore, it fundamentally avoids the risk of unexpected transaction interruption caused by adding database unique constraints, ensuring business continuity; at the same time, it completely eliminates the performance loss caused by performing extra full table queries afterward. In scenarios with large amounts of data or high concurrency generation, it can significantly reduce the response latency of business operations and improve the overall throughput and stability of the database system.

[0008] Furthermore, the step of calling the hook function to perform the deduplication operation includes: Use the newly generated unique identifier as the query key to initiate a query request to the in-memory database; If the query key is found in the memory database, it is determined to be a duplicate. The query key is deleted from the memory database, the corresponding data record is deleted from the target table, and the operation of generating a new unique identifier is instructed to be re-executed to generate another new unique identifier. If the query key is not found in the memory database, it is determined to be non-duplicate, and the newly generated unique identifier is output as the final result.

[0009] It's important to note that this method intervenes immediately after the database generates a new identifier, initiating a millisecond-level query to the in-memory database to determine if it's a duplicate of an existing set. If a duplicate is detected, the mechanism doesn't simply terminate with an error; instead, it automatically and silently executes a closed-loop process within the database: immediately deleting the conflicting record from the cache and persistent tables and instructing a retry of generation. This design transforms duplicate checking from an external constraint that could cause transaction failure or an additional query that inevitably increases latency into a seamless, high-speed internal routine within the database kernel. Therefore, this method fundamentally eliminates the risk of unexpected business operation interruptions due to unique constraint conflicts, ensuring absolute process continuity; simultaneously, it completely avoids the performance overhead of performing full table scan queries afterward. In scenarios with high concurrency generating massive amounts of data, this mechanism can significantly reduce the response time of each generation operation, effectively improving the overall throughput and stability of the database system.

[0010] Furthermore, starting an instance of the in-memory database and establishing a communication connection with the in-memory database includes: During the initialization process of the PostgreSQL plugin, an attempt is made to acquire a file lock; If the file lock is successfully acquired, the execution path of the in-memory database is obtained according to the PostgreSQL execution path. An instance of the in-memory database is started through a system call, and a communication connection is established with the started instance in an inter-process communication manner.

[0011] It's important to note that this method, during plugin initialization, acquires a file lock to ensure that only one in-memory database instance controlled by the plugin is running at any given time within the same PostgreSQL database environment. This fundamentally prevents issues such as memory resource conflicts and inconsistent data states that might arise from repeated instance startups. Building on this, the mechanism directly starts the instance via system calls, deeply binding the in-memory database's lifecycle to the plugin and achieving reliable management of the caching service. Subsequently, inter-process communication is used to establish a connection, which typically provides a lower latency and higher throughput data transmission channel compared to network communication. Therefore, this entire design provides a stable, exclusive, and efficient dedicated caching channel for subsequent high-frequency duplicate value detection operations, ensuring the high performance and reliability of the deduplication function at the infrastructure level and avoiding additional overhead and instability that might be introduced due to chaotic instance management or communication bottlenecks.

[0012] Furthermore, establishing the communication connection with the launched instance using inter-process communication includes: The instance of the memory database controlled by the startup is started and listens in the form of a domain socket; Construct a file descriptor corresponding to the domain socket to establish the communication connection.

[0013] It's important to note that this method starts and listens on a domain socket by controlling the in-memory database instance. The plugin then constructs a corresponding file descriptor to establish a connection, essentially creating a dedicated, localized, high-speed data channel within the operating system kernel. Compared to TCP / IP sockets, which require processing through the complete network protocol stack, domain sockets eliminate the overhead of packet encapsulation, routing, and verification at the network and transport layers, enabling the most direct data exchange between two processes on the same host. Therefore, this design provides a communication foundation with extremely low latency and extremely high throughput for subsequent high-frequency duplicate value lookup operations. It minimizes the network round-trip overhead for each detection request, reducing the performance loss introduced by the duplicate value detection mechanism itself at the underlying communication level, thereby ensuring that the entire deduplication function can operate efficiently without affecting the response speed of the main business process.

[0014] Furthermore, synchronizing the data in the target table to the in-memory database includes: Establish a connection to the PostgreSQL database through the PostgreSQL server programming interface; Data is retrieved from the target table using a cursor, and the unique identifier in each row of data retrieved is assembled into an insert command that conforms to the memory database communication protocol; The insert command is synchronized to the instance of the in-memory database.

[0015] It's important to note that this method establishes connections and manipulates data directly within the database process via PostgreSQL's Server Programming Interface (SPI), avoiding the network overhead and authentication latency associated with external program access to the database. Using cursors for queries allows data to be retrieved from the target table in batches and streams, effectively controlling memory usage per operation and reliably handling datasets of any size. Subsequently, each row of data is assembled in real-time into commands using the native communication protocol of the in-memory database, achieving an "in-situ conversion" of data from relational tables to key-value storage, eliminating the need for intermediate caching or temporary storage. Finally, the formatted commands are streamed to the in-memory database instance, completing the data pipeline from persistent storage to high-speed caching. This entire process, from data extraction and format conversion to final loading, is completed efficiently and seamlessly within the database server. This ensures that the pre-loading of massive amounts of historical identifier data into the in-memory cache can be performed with extremely high throughput and low resource consumption, laying a solid and timely data foundation for subsequent millisecond-level real-time deduplication functionality.

[0016] Furthermore, assembling the unique identifier from each row of queried data into an insert command conforming to the memory database communication protocol includes: A command that assembles a unique identifier in each row of data with a predetermined numerical value into a key-value pair format, wherein the format of the command conforms to the text protocol of the in-memory database.

[0017] It's important to note that after retrieving each row of data from the database, this method doesn't temporarily store or perform complex processing on the data. Instead, it immediately combines the target field (unique identifier) ​​with a predefined value and directly formats it into an insert command that conforms to the native text protocol of the in-memory database. Because the generated command fully adheres to the in-memory database's own text protocol, each instruction can be directly parsed and executed by the in-memory database instance in the most efficient way, without any additional protocol conversion or interpretation layer. This "query-convert-send-as-you-go" pipelined processing eliminates intermediate data storage and multiple serialization overheads. This allows the process of synchronously loading massive amounts of historical identifier data into the memory cache to be completed within the database server at near-theoretical efficiency, significantly shortening the data preparation time before enabling the deduplication function and ensuring the real-time performance and integrity of the cached data.

[0018] Furthermore, the step of importing existing unique identifier data that needs to be prevented from being duplicated into the target table includes: Receive unique identifier data from an external data source, which is historical data migrated from other types of databases to the PostgreSQL database.

[0019] It's important to note that this method explicitly defines the imported target table as "historical data migrated from other types of databases to PostgreSQL." This design ensures that the duplicate value detection benchmark established during plugin initialization is highly consistent with the fundamental contradiction that the core business scenario of data migration aims to resolve—the risk of data identifier conflicts between the old and new systems. This targeted data loading mechanism allows subsequent high-speed deduplication operations based on the in-memory database to focus entirely on comparing historical identifiers migrated from external sources. This avoids unnecessary performance overhead and logical complexity caused by including irrelevant or internally generated data from the new system in the deduplication set. This ensures that the entire deduplication function, from the data preparation stage, has a clear and efficient objective, laying an accurate data foundation for achieving uninterrupted, high-performance uniqueness assurance.

[0020] Furthermore, the registration of hook functions for performing duplicate detection when generating unique identifiers in the PostgreSQL database includes: In the initialization function of the PostgreSQL plugin, the hook function is registered so that it is called after the PostgreSQL database calls the preset unique identifier generation function.

[0021] It's important to note that this method ensures the deduplication function is immediately ready upon plugin loading by registering the hook function during plugin initialization, achieving synchronous activation with the database's running state. More importantly, by explicitly setting the hook function execution time to "after the database calls the preset unique identifier generation function," the deduplication logic is precisely implanted into the critical gap between the creation of a new identifier and its formal use. This approach neither interferes with nor replaces the database's existing, mature identifier generation logic, but also allows for real-time review of the generated result immediately. This "immediate intervention after the fact" approach ensures that every newly generated identifier undergoes deduplication checking without exception. Furthermore, the entire process is transparent and seamless for both the database's own transaction processes and external business calls. This completely eliminates the risk of identifier duplication while maintaining full compatibility with existing business code and database behavior, achieving "zero-awareness" security hardening.

[0022] This specification provides one or more embodiments of a database field duplicate value detection device, applied to the migration of unique identifier data from different types of databases to a PostgreSQL database, including: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0023] This specification provides one or more embodiments of a non-volatile computer storage medium used for migrating unique identifier data from different types of databases to a PostgreSQL database. The medium stores computer-executable instructions that, when executed by a computer, can achieve the following: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0024] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects: This method innovatively introduces a real-time deduplication mechanism supported by an in-memory database at the critical path of unique identifier generation by designing a plugin deeply integrated into the PostgreSQL database. The plugin first persists existing unique identifiers (including historical migration data) in a database table and simultaneously loads them all into the in-memory database for high-speed access. Its core lies in registering a hook function that is immediately invoked after the database performs the new identifier generation operation, subsequently initiating a query to the in-memory database at millisecond speeds. If a duplicate is detected, the plugin not only immediately triggers a retry to ensure business continuity but also automatically cleans up conflicting data in the cache and persistent tables to ensure state consistency; if the detection passes, the result is returned immediately. This design transforms duplicate detection from a traditional, post-hoc, high-latency, and high-overhead disk query into a pre-hoc, embedded, in-memory high-speed verification process. Therefore, it fundamentally avoids the risk of unexpected transaction interruption caused by adding database unique constraints, ensuring business continuity; at the same time, it completely eliminates the performance loss caused by performing extra full table queries afterward. In scenarios with large amounts of data or high concurrency generation, it can significantly reduce the response latency of business operations and improve the overall throughput and stability of the database system. Attached Figure Description

[0025] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart illustrating a method for detecting duplicate values ​​in a database field provided in one or more embodiments of this specification; Figure 2 This specification provides a flowchart of the overall architecture of the plugin for one or more embodiments. Figure 3 Flowchart for starting a memcached instance using a plugin provided in one or more embodiments of this specification; Figure 4 Flowchart for importing data from a table into memcached, provided for one or more embodiments of this specification; Figure 5 A flowchart for duplicate collision detection and handling provided in one or more embodiments of this specification; Figure 6 This is a schematic diagram of the structure of a database field duplicate value detection device provided in one or more embodiments of this specification. Detailed Implementation

[0026] This specification provides a method, device, and medium for detecting duplicate values ​​in database fields.

[0027] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0028] Figure 1 This diagram illustrates a process flow for a database field duplicate value detection method provided in one or more embodiments of this specification. This process can be executed by a PostgreSQL plugin or a database field duplicate value detection system. It can be applied to migrating unique identifier data from different types of databases to a PostgreSQL database. Certain input parameters or intermediate results in the process can be manually adjusted to help improve accuracy.

[0029] The method flow steps of the embodiments in this specification are as follows: S101, in the PostgreSQL plugin, creates a target table for persistently storing existing unique identifier data.

[0030] In the embodiments described in this specification, during the installation or initialization phase of the PostgreSQL plugin, a specific physical table (such as the resolve_conflict table) is created in the PostgreSQL database by executing Database Structured Query Language commands. The table's structure includes at least one field for storing unique identifiers (such as UUID strings). This table serves as the final persistent storage repository for all unique identifier data that needs to be protected and prevented from future duplication, ensuring that even if in-memory data is lost, the complete set of data to be deduplicated can be recovered from this table.

[0031] S102, start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for duplicate detection when generating unique identifiers in the PostgreSQL database.

[0032] In the embodiments described in this specification, the lifecycle management logic of the in-memory database (such as memcached) instance is implemented in the plugin's initialization function. First, a file lock is acquired to ensure that only one in-memory database process is started within a single database instance. Then, the executable file path of the in-memory database is located based on the system environment, and the process instance is started via an operating system call, configured to listen on a domain socket. After the process starts, the plugin immediately creates a connection handle corresponding to the domain socket, thereby establishing a high-speed inter-process communication link with the in-memory database instance.

[0033] Similarly, during the plugin initialization phase, a custom hook function is registered with the PostgreSQL database core. During registration, this hook function must be explicitly bound to the execution point of a specific function within the database that generates unique identifiers (e.g., `uuid_generate_v4`). This ensures that whenever the database calls this generation function to produce a new identifier, the control flow immediately jumps to our detection logic.

[0034] S103, when the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value.

[0035] In the embodiments described in this specification, this step is the "data preheating" stage of the plagiarism detection function, which is usually manually triggered once after the plugin is enabled. The implementation plan is to execute two data flow tasks sequentially: First, the unique identifier data requiring deduplication prevention is imported from an external data source (i.e., historical data migrated from other types of databases to PostgreSQL) into the target table created in step S101 via batch insert operations, completing the data persistence to disk. Next, a synchronization task is initiated. The plugin establishes a connection within the database via the Server Programming Interface (SPI) provided by PostgreSQL and reads data from the target table in batches using a cursor. For each row read, the value of its unique identifier field is extracted, combined with a predefined fixed value (e.g., 0), and assembled into a complete key-value insert command strictly according to the text protocol format of the in-memory database. Finally, this command is sent to the in-memory database instance for execution via the communication connection established in S102. This process is repeated until all data in the target table has been read and synchronized to the in-memory database, at which point the complete set of deduplication keys is cached in memory.

[0036] S104, When performing the operation of generating a new unique identifier in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0037] In the embodiments described in this specification, this step is the core detection logic triggered once every time an identifier is generated during plugin runtime, and is specifically implemented by the hook function registered in S102. When the PostgreSQL database performs the operation of generating a new unique identifier, the hook function is called and receives the newly generated identifier as input. Its processing flow is as follows: the function immediately uses this new identifier as a key and initiates a query to the in-memory database through the existing connection. If the query result shows that the key already exists, it is determined to be duplicated. At this time, the function sends a command to the in-memory database to delete this key, and at the same time deletes the corresponding record from the persistent target table through the server programming interface (SPI) to ensure consistency of state, and returns a "duplicate" signal, prompting the upper layer to regenerate a new identifier and trigger this detection process again. If the in-memory database returns "key does not exist", it is determined to be non-duplicate, the function directly returns a "pass" signal, and the newly generated identifier is allowed to be used. This process runs in a loop until a non-duplicate identifier is generated, thereby completing real-time deduplication the moment the identifier is generated.

[0038] It's worth noting that this method innovatively introduces a real-time deduplication mechanism supported by an in-memory database at the critical path of unique identifier generation by designing a plugin deeply integrated into the PostgreSQL database. This plugin first persistently stores existing unique identifiers (including historical migration data) in a database table and simultaneously loads them all into the in-memory database for high-speed access. Its core lies in registering a hook function that is immediately invoked after the database performs the new identifier generation operation, subsequently initiating a query to the in-memory database at millisecond speeds. If a duplicate is detected, the plugin not only immediately triggers a retry to ensure business continuity but also automatically cleans up conflicting data in the cache and persistent tables to ensure state consistency; if the detection passes, the result is returned immediately. This design transforms duplicate detection from a traditional, post-hoc, high-latency, and high-overhead disk query into a pre-hoc, embedded, in-memory high-speed verification process. Therefore, it fundamentally avoids the risk of unexpected transaction interruption caused by adding database unique constraints, ensuring business continuity; at the same time, it completely eliminates the performance loss caused by performing extra full table queries afterward. In scenarios with large amounts of data or high concurrency generation, it can significantly reduce the response latency of business operations and improve the overall throughput and stability of the database system.

[0039] Furthermore, during the process of calling the hook function to perform the deduplication operation, the method flow steps of this embodiment are as follows: S201, use the newly generated unique identifier as the query key to initiate a query request to the memory database.

[0040] In the embodiments described in this specification, this step is the starting point for deduplication detection. When the hook function is called, its input parameter is the newly generated unique identifier (e.g., a UUID string) from the PostgreSQL database. The implementation involves the hook function immediately converting this identifier data into a string format suitable for network transmission and using this string as the query key. Subsequently, it sends a query command (e.g., a get command) conforming to its text protocol to the in-memory database instance via a previously established inter-process communication connection (e.g., a domain socket). This operation aims to leverage the extremely high read speed of the in-memory database to confirm whether the key already exists in the deduplication-prevention cache set within microseconds.

[0041] S202, if the query key is found in the memory database, it is determined to be a duplicate, the query key is deleted from the memory database, the corresponding data record is deleted from the target table, and the operation of generating a new unique identifier is instructed to be re-executed to generate another new unique identifier.

[0042] In the embodiments described in this specification, this step represents the complete processing logic when a duplicate is detected. If the response of the in-memory database to the S201 query indicates that the key already exists, it is determined that the newly generated identifier conflicts with historical data. The implementation at this point is a series of operations to ensure eventual data consistency: First, the hook function sends a delete command to the in-memory database, immediately removing the duplicate key from the cache. Then, it performs a delete operation in the persistent target table via the PostgreSQL server programming interface, removing the entire row of data records corresponding to the key with that identifier, thereby ensuring that the persistent storage and cache states are synchronized. After completing the above cleanup, the hook function returns a specific "duplicate" status signal to the upper-level caller. Upon receiving this signal, the upper-level logic will re-trigger a new unique identifier generation operation (e.g., calling the uuid_generate_v4 function again). After the new identifier is generated, the hook function will be called again, i.e., it will jump back to step S201 and start a new round of duplicate checking. This design ensures that when a conflict occurs, the system can automatically and silently retry until a unique result is produced, without interrupting database transactions or throwing errors to the application layer.

[0043] S203, if the query key is not found in the memory database, it is determined to be non-duplicate, and the newly generated unique identifier is output as the final result.

[0044] In the embodiments described in this specification, this step is the result return path after successful detection. If the response of the in-memory database to the S201 query indicates that the key does not exist, it is determined that the newly generated identifier is unique and does not conflict with the existing set. At this time, the implementation scheme is very simple and direct: the hook function does not need to perform any write operations on the in-memory database or the target table; it only needs to return a "non-duplicate" or "success" status signal to the upper-layer caller. After receiving this signal, the upper-layer logic will adopt the newly generated unique identifier as the final valid result and continue subsequent database operations (such as inserting it into the business table) or return it to the client application. Thus, a complete generation and real-time deduplication process is successfully completed.

[0045] It's important to note that this method intervenes immediately after the database generates a new identifier, initiating a millisecond-level query to the in-memory database to determine if it's a duplicate of an existing set. If a duplicate is detected, the mechanism doesn't simply terminate with an error; instead, it automatically and silently executes a closed-loop process within the database: immediately deleting the conflicting record from the cache and persistent tables and instructing a retry of generation. This design transforms duplicate checking from an external constraint that could cause transaction failure or an additional query that inevitably increases latency into a seamless, high-speed internal routine within the database kernel. Therefore, this method fundamentally eliminates the risk of unexpected business operation interruptions due to unique constraint conflicts, ensuring absolute process continuity; simultaneously, it completely avoids the performance overhead of performing full table scan queries afterward. In scenarios with high concurrency generating massive amounts of data, this mechanism can significantly reduce the response time of each generation operation, effectively improving the overall throughput and stability of the database system.

[0046] Furthermore, the method flow steps in this embodiment of the specification for starting an instance of the in-memory database and establishing a communication connection with the in-memory database are as follows: S301, during the initialization process of the PostgreSQL plugin, an attempt is made to acquire a file lock.

[0047] In the embodiments described in this specification, this step is a prerequisite for preventing resource conflicts. The implementation scheme involves the plugin attempting to acquire a file lock at a predetermined path in the operating system when its initialization function begins execution. This lock acquisition essentially utilizes the file lock mechanism provided by the operating system to detect and ensure that no other process (especially other similar plugins) has already created an in-memory database instance in the current runtime environment. If the plugin successfully acquires the file lock, it indicates that there is currently no active in-memory database instance managed by this plugin, and the plugin can safely continue the process of starting a new instance. This mechanism avoids, at the system level, problems such as wasted memory resources, port conflicts, or data state corruption that could result from the accidental startup of multiple identical instances.

[0048] S302, if the file lock is successfully acquired, the execution path of the in-memory database is obtained according to the PostgreSQL execution path, an instance of the in-memory database is started through a system call, and a communication connection is established with the started instance in an inter-process communication manner.

[0049] In the embodiments described in this specification, this step is the specific execution process after obtaining exclusive startup rights, and includes a series of sequentially dependent operations. The implementation scheme is unfolded in sequence as follows: First, the plugin needs to locate the executable file of the in-memory database. It deduces the storage path of the in-memory database service program on the server host based on the current PostgreSQL database server's installation path or configured environment variables.

[0050] Next, the plugin executes the memory database executable found in the previous step via the operating system's system call interface, thereby starting a new memory database service process in the background. When starting this process, the plugin configures it using startup parameters, crucially specifying that it should start and listen for connection requests using domain sockets, rather than using common network ports. This allows the instance to communicate through a special socket file in the operating system kernel, bypassing the network protocol stack.

[0051] After issuing the start command, the plugin will briefly wait and confirm that the in-memory database instance has successfully started and entered a serviceable state. Then, the plugin performs the final connection establishment operation: within its own process, it creates a corresponding socket file descriptor based on the domain socket file path specified during instance startup. By manipulating this file descriptor, the plugin establishes a stable, domain socket-based inter-process communication connection with the newly started in-memory database instance process. This connection provides a high-speed, low-latency data transmission channel for all subsequent data synchronization and query requests.

[0052] It's important to note that this method, during plugin initialization, acquires a file lock to ensure that only one in-memory database instance controlled by the plugin is running at any given time within the same PostgreSQL database environment. This fundamentally prevents issues such as memory resource conflicts and inconsistent data states that might arise from repeated instance startups. Building on this, the mechanism directly starts the instance via system calls, deeply binding the in-memory database's lifecycle to the plugin and achieving reliable management of the caching service. Subsequently, inter-process communication is used to establish a connection, which typically provides a lower latency and higher throughput data transmission channel compared to network communication. Therefore, this entire design provides a stable, exclusive, and efficient dedicated caching channel for subsequent high-frequency duplicate value detection operations, ensuring the high performance and reliability of the deduplication function at the infrastructure level and avoiding additional overhead and instability that might be introduced due to chaotic instance management or communication bottlenecks.

[0053] Furthermore, in the process of establishing the communication connection with the started instance using inter-process communication, the method flow steps of this embodiment are as follows: S401, The instance of the memory database controlled to start is started and listens in the form of a domain socket.

[0054] In the embodiments described in this specification, this step is a critical configuration action when starting the in-memory database instance. Its implementation is not independent, but rather performed as part of the operation "starting the in-memory database instance via a system call" in step S302. Specifically, when the plugin executes the startup command via a system call, it explicitly specifies a critical configuration item in the command parameters, namely, controlling the newly started in-memory database instance to run in "domain socket" mode. This means that the plugin includes a parameter pointing to a specific file system path in the startup command, which will serve as the binding address for the domain socket. With this configuration, after startup, the in-memory database instance will not listen on a network port like a regular service, but will instead listen in the operating system kernel on a special socket file at the specified path. This configuration method strictly restricts communication to processes on the same host, preparing for the subsequent establishment of high-performance inter-process communication links.

[0055] S402, construct a file descriptor corresponding to the domain socket to establish the communication connection.

[0056] In this embodiment, this step is initiated by the plugin after the instance has successfully started and entered the listening state according to the S401 configuration. The implementation involves the plugin program obtaining the specific path to the domain socket file configured in step S401. Then, the plugin calls the operating system's programming interface for connecting to the domain socket and passes in this path. The operating system kernel processes this connection request. If the in-memory database instance is already listening correctly, the kernel establishes a communication channel between the plugin process and the in-memory database instance process. As the representative of this channel within the plugin process, the operating system returns a "file descriptor." The plugin obtaining and saving this file descriptor signifies that a dedicated communication connection with the in-memory database instance has been successfully established. Subsequently, all queries or commands that the plugin needs to send to the in-memory database, as well as receiving return results, will be accomplished by manipulating this file descriptor.

[0057] It's important to note that this method starts and listens on a domain socket by controlling the in-memory database instance. The plugin then constructs a corresponding file descriptor to establish a connection, essentially creating a dedicated, localized, high-speed data channel within the operating system kernel. Compared to TCP / IP sockets, which require processing through the complete network protocol stack, domain sockets eliminate the overhead of packet encapsulation, routing, and verification at the network and transport layers, enabling the most direct data exchange between two processes on the same host. Therefore, this design provides a communication foundation with extremely low latency and extremely high throughput for subsequent high-frequency duplicate value lookup operations. It minimizes the network round-trip overhead for each detection request, reducing the performance loss introduced by the duplicate value detection mechanism itself at the underlying communication level, thereby ensuring that the entire deduplication function can operate efficiently without affecting the response speed of the main business process.

[0058] Furthermore, in the process of synchronizing the data in the target table to the in-memory database, the method flow steps of this embodiment are as follows: S501, establishes a connection with the PostgreSQL database through the PostgreSQL server programming interface.

[0059] In the embodiments described in this specification, this step is a prerequisite for performing subsequent data query operations. The implementation scheme involves the plugin's code calling the server programming interface provided by PostgreSQL when it initiates the data synchronization function. This interface allows the plugin code to securely initiate a new database session within the database server process, just like a normal client. After calling this interface, the plugin obtains a valid internal connection to its own PostgreSQL database instance. This connection differs from a network connection; it bypasses the network protocol stack and authentication overhead, enabling the plugin to execute SQL operations with extremely high efficiency. The sole purpose of establishing this connection is to subsequently enable direct access and manipulation of the "target table" created in step S101 for persistent storage.

[0060] S502, query data from the target table using a cursor, and assemble the unique identifier in each row of data into an insert command that conforms to the memory database communication protocol.

[0061] In the embodiments described in this specification, this step is the core of data extraction and format conversion. The implementation scheme consists of two consecutive actions: First, based on the internal connection established by S501, the plugin submits an SQL statement to the database querying the "target table". The key is that it executes this query by declaring a "cursor". The cursor mechanism allows the plugin to retrieve data rows from the result set in batches, for example, retrieving a fixed number of rows each time (such as the number specified by SPI_PER_FETCH_COUNT). This effectively controls the memory usage of a single operation, thus reliably handling tables with extremely large amounts of data.

[0062] Then, for each row of data retrieved from the cursor, the plugin extracts the value of the "unique identifier" field. Next, the plugin combines this identifier string with a predefined fixed value (such as the default value 0) to form a "key-value pair." Finally, the plugin strictly adheres to the text line protocol format of in-memory databases, encapsulating this key-value pair into a complete insert command. For example, it generates a raw command string like "set [unique identifier string]\r\n0\r\n". This process completes the instantaneous conversion of data from relational table rows to native in-memory database commands.

[0063] S503, synchronize the insert command to the instance of the in-memory database.

[0064] In the embodiments described in this specification, this step involves loading the transformed data into a cache. The implementation scheme is as follows: the plugin, through the inter-process communication connection established in step S102 (such as a domain socket-based connection), sends the protocol-compliant insert commands generated for each row of data in step S502, one by one, continuously to the running in-memory database instance. Upon receiving each command, the in-memory database instance immediately parses and executes it, thereby storing the corresponding key (unique identifier) ​​and its associated value in its memory space. The plugin continues this process, processing each batch of data returned by the cursor sequentially, until all data rows in the "target table" have been queried, transformed, and sent. At this point, the entire set of unique identifiers in the persistent "target table" has been completely and accurately synchronized to the in-memory database, preparing the data for subsequent real-time deduplication.

[0065] It's important to note that this method establishes connections and manipulates data directly within the database process via PostgreSQL's Server Programming Interface (SPI), avoiding the network overhead and authentication latency associated with external program access to the database. Using cursors for queries allows data to be retrieved from the target table in batches and streams, effectively controlling memory usage per operation and reliably handling datasets of any size. Subsequently, each row of data is assembled in real-time into commands using the native communication protocol of the in-memory database, achieving an "in-situ conversion" of data from relational tables to key-value storage, eliminating the need for intermediate caching or temporary storage. Finally, the formatted commands are streamed to the in-memory database instance, completing the data pipeline from persistent storage to high-speed caching. This entire process, from data extraction and format conversion to final loading, is completed efficiently and seamlessly within the database server. This ensures that the pre-loading of massive amounts of historical identifier data into the in-memory cache can be performed with extremely high throughput and low resource consumption, laying a solid and timely data foundation for subsequent millisecond-level real-time deduplication functionality.

[0066] Furthermore, in the process of assembling the unique identifiers from each row of queried data into an insert command conforming to the memory database communication protocol, the method flow steps in this embodiment are as follows: S601, a command that assembles a unique identifier in each row of data with a predetermined value into a key-value pair format, wherein the format of the command conforms to the text protocol of the memory database.

[0067] In the embodiments described in this specification, this step is performed after a row of data is successfully retrieved from the target table via a cursor. Its core task is to perform real-time data format conversion, and the implementation scheme includes two levels of operations: First, data extraction and key-value pair combination are performed. The plugin code precisely extracts the value of the "unique identifier" field from the data row pointed to by the current cursor. This value is usually a string, such as a UUID. Subsequently, the plugin uses this identifier string as a "key" and pairs it with a fixed value (such as 0) that was predefined during the plugin design, thus logically forming a "key-value pair" in memory. The fixed value (0) here mainly serves as a placeholder, its significance being to identify the existence of the key, rather than storing business data.

[0068] Next, the protocol format is encapsulated. The plugin needs to assemble the logical key-value pairs combined in the previous step into a command string that can be directly recognized and executed by the in-memory database instance, strictly adhering to the text line protocol specifications of the in-memory database (such as memcached). For example, the keys and values ​​are assembled into a complete command like "set [unique identifier string]\r\n0\r\n". The "set" keyword, spaces, and newline characters \r\n must be concatenated strictly according to the protocol. This process ensures that every command generated is in a format natively supported by the in-memory database, requiring no additional parsing or conversion from the receiving end.

[0069] It's important to note that after retrieving each row of data from the database, this method doesn't temporarily store or perform complex processing on the data. Instead, it immediately combines the target field (unique identifier) ​​with a predefined value and directly formats it into an insert command that conforms to the native text protocol of the in-memory database. Because the generated command fully adheres to the in-memory database's own text protocol, each instruction can be directly parsed and executed by the in-memory database instance in the most efficient way, without any additional protocol conversion or interpretation layer. This "query-convert-send-as-you-go" pipelined processing eliminates intermediate data storage and multiple serialization overheads. This allows the process of synchronously loading massive amounts of historical identifier data into the memory cache to be completed within the database server at near-theoretical efficiency, significantly shortening the data preparation time before enabling the deduplication function and ensuring the real-time performance and integrity of the cached data.

[0070] Furthermore, in the process of importing existing unique identifier data that needs to be prevented from being duplicated into the target table, the method flow steps of this embodiment are as follows: S701, receive unique identifier data from an external data source, which is historical data migrated from other types of databases to the PostgreSQL database.

[0071] In this embodiment, this step is implemented when the deduplication function of the PostgreSQL plugin is enabled, and its core task is to complete the initial data loading. The key to this implementation is clarifying the data source and executing the data migration operation. The data source is explicitly defined as an "external data source," specifically referring to historical data migrated from other types of databases (such as MySQL, Oracle, etc.) to the current PostgreSQL database. This historical data contains a set of unique identifiers generated in the old business system that need to be protected in the new system to prevent future duplication. Operationally, the database administrator or migration tool typically executes batch insert SQL statements (e.g., INSERT INTO ...SELECT ...) to write this historical unique identifier data from the migration temporary table or data file, either all at once or in batches, into the persistent "target table" created in step S101. This process formally records the complete set of critical identifiers left over from the external system and to be protected into the authoritative storage managed by the plugin, thereby establishing the original data benchmark upon which all subsequent duplicate value detection is based. This step ensures that the deduplication mechanism's protection scope is precisely focused on the historical data set most likely to conflict, introduced by the data migration.

[0072] It's important to note that this method explicitly defines the imported target table as "historical data migrated from other types of databases to PostgreSQL." This design ensures that the duplicate value detection benchmark established during plugin initialization is highly consistent with the fundamental contradiction that the core business scenario of data migration aims to resolve—the risk of data identifier conflicts between the old and new systems. This targeted data loading mechanism allows subsequent high-speed deduplication operations based on the in-memory database to focus entirely on comparing historical identifiers migrated from external sources. This avoids unnecessary performance overhead and logical complexity caused by including irrelevant or internally generated data from the new system in the deduplication set. This ensures that the entire deduplication function, from the data preparation stage, has a clear and efficient objective, laying an accurate data foundation for achieving uninterrupted, high-performance uniqueness assurance.

[0073] Furthermore, in the process of registering the hook function for performing duplicate detection when generating unique identifiers in the PostgreSQL database, the method flow steps of this embodiment are as follows: S801, in the initialization function of the PostgreSQL plugin, the registration of the hook function is completed so that the hook function is called after the PostgreSQL database calls the preset unique identifier generation function.

[0074] In the embodiments described in this specification, this step is implemented during the initialization phase of the PostgreSQL plugin, which belongs to the same initialization process as the startup of the in-memory database instance. Its core objective is to complete the "mounting" of the detection logic, and the implementation scheme focuses on the timing of registration triggering and the specific binding operation.

[0075] First, the timing of the registration action is explicitly defined as "within the initialization function of the PostgreSQL plugin." This means that when the PostgreSQL database is loaded and the plugin is enabled, the plugin's defined initialization function will be automatically called. While this function performs tasks such as starting the in-memory database, the registration of hook functions must be completed simultaneously to ensure that the plugin's deduplication capability is immediately ready once it is successfully loaded.

[0076] Secondly, the specific implementation of the registration operation involves the plugin binding its custom hook function to a pre-defined function (such as `uuid_generate_v4`) within the database by calling the extended interface provided by the PostgreSQL database. During registration, the calling relationship must be precisely set, ensuring that the hook function is called after the PostgreSQL database calls the pre-defined unique identifier generation function. This is equivalent to setting a rule in the database kernel: whenever any database operation (such as transaction insertion) calls the pre-defined generation function, the control flow must immediately execute the registered hook function. In this way, the plugin successfully gains control over real-time checks of each new unique identifier the moment it is "born," laying the foundation for subsequent in-memory database queries and conflict handling.

[0077] It's important to note that this method ensures the deduplication function is immediately ready upon plugin loading by registering the hook function during plugin initialization, achieving synchronous activation with the database's running state. More importantly, by explicitly setting the hook function execution time to "after the database calls the preset unique identifier generation function," the deduplication logic is precisely implanted into the critical gap between the creation of a new identifier and its formal use. This approach neither interferes with nor replaces the database's existing, mature identifier generation logic, but also allows for real-time review of the generated result immediately. This "immediate intervention after the fact" approach ensures that every newly generated identifier undergoes deduplication checking without exception. Furthermore, the entire process is transparent and seamless for both the database's own transaction processes and external business calls. This completely eliminates the risk of identifier duplication while maintaining full compatibility with existing business code and database behavior, achieving "zero-awareness" security hardening.

[0078] This invention provides a plugin design method for PostgreSQL databases (hereinafter referred to as PG). The technical problem it solves is that after migrating unique identifier data from different types of databases to a new PG database, the newly generated unique identifier data may duplicate the unique identifiers in the old database. Furthermore, there is no method for detecting duplicate values ​​between the newly generated unique identifier data and the old database's unique identifier data after migration from other types of databases, and no method for handling duplicate occurrences. This invention uses a combination of memory caching (memcached) for accelerated queries and persistent storage in PG physical tables to ensure the absolute uniqueness of unique identifier data in the migrated new database.

[0079] This invention uses the in-memory key-value database memcached and the relational database PostgreSQL. This invention is a plugin for PostgreSQL. The access efficiency of the in-memory key-value database memcached is higher than that of PostgreSQL tables.

[0080] In database data migration scenarios, it may be necessary to migrate data from different databases to PG. To avoid duplicate unique identifier values ​​between different databases before and after the migration, this plugin is designed to verify existing data of this type each time a unique identifier is generated, and handle duplicate conflicts.

[0081] Existing data migration methods cannot guarantee that the newly generated data after migration will not be duplicated from the unique identifier data generated before migration. When this type of data is duplicated, it will have a significant impact on the front-end business. If a unique constraint is added to avoid this situation, the transaction that generates the unique identifier data will be interrupted by error. If the result is checked after generation, additional queries will be generated, which will increase performance overhead and affect the business execution time.

[0082] To address the issue of potential data duplication and minimize the performance overhead associated with resolving this problem, this invention designs a PG plugin. This plugin manages memcached and determines the duplication status of generated unique identifiers in real time. Meanwhile, the in-memory key-value database memcached minimizes the performance loss caused by duplication detection.

[0083] This invention provides a PostgreSQL plugin design method. The plugin has three main functional designs: start / stop and connection management functions for memcached in-memory key-value database, SPI function for managing PostgreSQL ordinary tables, and duplicate collision detection and handling functions.

[0084] The main process of this invention is described in [reference]. Figure 2 The flowchart of the plugin's overall architecture is shown. When creating a plugin, the table resolve_conflict is created first. Then, PG initializes the plugin. The initialization function calls the startup and connection functions in the memcached management function. To ensure performance, the memcached instance is started and connected in the form of domain socket. After the initialization function completes the registration of the unique identifier value hook function, the plugin initialization is completed.

[0085] When enabling the plugin for the first time, you need to insert the data of the unique identifier type that you want to protect into the table resolve_conflict created by the plugin. Then, import the data in the table into memcached one by one, with each data as a key and the default value being 0. Using the memcached communication protocol, assemble it into a command to insert the key-value pair and send it to the memcached instance.

[0086] After the unique identifier type data is generated, the duplicate value detection processing logic will be entered. The decision on whether to generate it again will be based on the processing result, until non-duplicate data is generated.

[0087] For the logic of starting a memcached instance and establishing a connection for the plugin management memcached function, please refer to [link / reference]. Figure 3 The flowchart shown illustrates the plugin's process for starting a memcached instance. To avoid repeatedly starting multiple memcached instances, the plugin first attempts to acquire a file lock. If successful, it indicates that no memcached instance created by the plugin is running; otherwise, an existing instance is used. Taking a successful acquisition as an example, the plugin locates the memcached execution path based on the PostgreSQL execution path and runs a memcached instance via a system call. This instance starts by specifying a domain socket and waits for it to start successfully and be able to accept connections before completing the memcached instance startup. Afterward, a domain socket file descriptor is constructed, and a connection is established.

[0088] Figure 3 After inserting the data of the unique identifier type to be protected into the `resolve_conflict` table created by the plugin, the data in the table needs to be imported into memcached. See [link to process details]. Figure 4 The flowchart shown illustrates the process of importing data from a table into memcached. Figure 4 In this context, the query method for resolving_conflict is to use the PG's SPI module to query in cursor mode. After establishing an SPI connection, all data in the table is queried, and a single query does not exceed the number of rows specified by the macro SPI_PER_FETCH_COUNT. Then, the unique identifier data in each row of the query result is used as the key to form key-value pairs with 0. The key-value pairs are then assembled into data in the memcached insertion format, such as "set B2BB47A8-3969-4A66-949A-F1A344F982EE\r\n0\r\n". This data is sent to the memcached instance using domain socket communication. After that, the instance's return value is received, and the memcached processing result is judged. If the insertion is unsuccessful, an error is thrown.

[0089] After querying all data in the table using a cursor, complete the memcached data import and disconnect the SPI connection.

[0090] The logic for generating unique identifier type data and detecting and handling duplicate values ​​is as follows: Figure 5 The flowchart shown above illustrates the duplicate conflict detection and handling process. The duplicate value detection and handling logic is provided by the hook function provided by this plugin.

[0091] The hook function is called after the function that generates the unique identifier is called. In this invention, the call occurs after the function `uuid_generate_v4`, provided by the PG built-in plugin `ossp-uuid`, is called. Furthermore, the plugin handles the call to `uuid_generate_v4`. Upon the first call to this function, assuming the hook function registration is complete, the plugin first converts the character-type UUID data generated from the `uuid_generate_v4` parameter into a string. Using this string as the key, it sends a request to memcached to retrieve the value. If the corresponding value is received during communication, it means that the key exists in memcached. Then, a message is sent to memcached to delete the key. Next, SPI is called to delete the row represented by the key in resolve_conflict. The hook function returns true. After the upper layer receives the return as true, uuid_generate_v4 is called again to generate a new uuid and pass it to the hook function. This continues until the hook function returns false, indicating that no duplicate cases have occurred. The uuid is then returned as the unique identifier generated by the non-duplicate conflict uuid. At this point, the duplicate value detection processing is completed.

[0092] Figure 6This specification provides a schematic diagram of the structure of a database field duplicate value detection device according to one or more embodiments, applied to the migration of unique identifier data from different types of databases to a PostgreSQL database, including: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0093] This specification provides one or more embodiments of a non-volatile computer storage medium used for migrating unique identifier data from different types of databases to a PostgreSQL database. The medium stores computer-executable instructions that, when executed by a computer, can achieve the following: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

[0094] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0095] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0096] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0097] In the embodiments provided in this application, it should be understood that the disclosed apparatus / network devices and methods can be implemented in other ways. For example, the apparatus / network device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0098] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0099] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The aforementioned units can be implemented in hardware or software.

[0100] If the integrated module / unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electrical carrier signals and telecommunication signals.

[0101] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A method for detecting duplicate values ​​in a database field, characterized in that, The method for migrating unique identifier data used in different types of databases to a PostgreSQL database includes: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

2. The method according to claim 1, characterized in that, The process of calling the hook function to perform the plagiarism detection includes: Use the newly generated unique identifier as the query key to initiate a query request to the in-memory database; If the query key is found in the memory database, it is determined to be a duplicate. The query key is deleted from the memory database, the corresponding data record is deleted from the target table, and the operation of generating a new unique identifier is instructed to be re-executed to generate another new unique identifier. If the query key is not found in the memory database, it is determined to be non-duplicate, and the newly generated unique identifier is output as the final result.

3. The method according to claim 1, characterized in that, The process of starting an instance of the in-memory database and establishing a communication connection with the in-memory database includes: During the initialization process of the PostgreSQL plugin, an attempt is made to acquire a file lock; If the file lock is successfully acquired, the execution path of the in-memory database is obtained according to the PostgreSQL execution path. An instance of the in-memory database is started through a system call, and a communication connection is established with the started instance in an inter-process communication manner.

4. The method according to claim 3, characterized in that, The process of establishing the communication connection with the launched instance using inter-process communication includes: The instance of the memory database controlled by the startup is started and listens in the form of a domain socket; Construct a file descriptor corresponding to the domain socket to establish the communication connection.

5. The method according to claim 1, characterized in that, The step of synchronizing the data in the target table to the in-memory database includes: Establish a connection to the PostgreSQL database through the PostgreSQL server programming interface; Data is retrieved from the target table using a cursor, and the unique identifier in each row of data retrieved is assembled into an insert command that conforms to the memory database communication protocol; The insert command is synchronized to the instance of the in-memory database.

6. The method according to claim 5, characterized in that, The step of assembling the unique identifiers from each row of queried data into an insert command conforming to the memory database communication protocol includes: A command that assembles a unique identifier in each row of data with a predetermined numerical value into a key-value pair format, wherein the format of the command conforms to the text protocol of the in-memory database.

7. The method according to claim 1, characterized in that, The step of importing existing unique identifier data that needs to be prevented from being duplicated into the target table includes: Receive unique identifier data from an external data source, which is historical data migrated from other types of databases to the PostgreSQL database.

8. The method according to claim 1, characterized in that, The registration of hook functions for performing duplicate detection when generating unique identifiers in the PostgreSQL database includes: In the initialization function of the PostgreSQL plugin, the hook function is registered so that it is called after the PostgreSQL database calls the preset unique identifier generation function.

9. A database field duplicate value detection device, characterized in that, Migrating unique identifier data used in different types of databases to PostgreSQL databases, including: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

10. A non-volatile computer storage medium, characterized in that, Unique identifier data used in different types of databases is migrated to a PostgreSQL database, which stores computer-executable instructions that, when executed by a computer, can achieve the following: In the PostgreSQL plugin, create a target table for persistently storing existing unique identifier data; Start an instance of the in-memory database and establish a communication connection with the in-memory database, and register a hook function for performing duplicate detection when the PostgreSQL database generates unique identifiers; When the deduplication function of the PostgreSQL plugin is enabled, the existing unique identifier data that needs to be prevented from being duplicated is imported into the target table, and the data in the target table is synchronized to the in-memory database. The unique identifier of each data record is stored as a key and associated with a predetermined value. When the operation of generating a new unique identifier is performed in the PostgreSQL database, the hook function is called to perform a deduplication operation.

Images