PostGIS中基于点距离的并行插入去重问题排查与实现

数据表结构 #

CREATE TABLE pois (
id bigserial NOT NULL,
name int8 NOT NULL,
point geometry(point) NOT NULL
);

需求说明 #

当插入的点与表中已有点的ST_DISTANCE小于0.00001时，判定为重复并丢弃该记录。例如以下两条极近的点记录，第二条应被丢弃：

INSERT INTO pois (id,"name",point) VALUES
     (1, 'Name 1', 'SRID=4326;POINT (14.071422731481 50.142209143518)');
INSERT INTO pois (id,"name",point) VALUES
     (1, 'Name 2', 'SRID=4326;POINT (14.071422781481 50.142209142518)');

这些记录可能以批量、单事务或连续插入的方式进入数据库。

尝试的解决方案 #

使用BEFORE INSERT行级触发器实现，触发器函数如下：

CREATE OR REPLACE FUNCTION check_duplicates()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    RAISE NOTICE 'New: %', NEW.name;
    
    if EXISTS (
        select * FROM pois 
        WHERE 
            ST_Distance(point, ST_SetSRID(NEW.point, 4326)) < 0.00001
        ) 
    then 
            RAISE NOTICE 'New: % exists - skip!', NEW.name;
            RETURN NULL;
    else
            RAISE NOTICE 'New: % unique - save!', NEW.name;
            RETURN NEW;
    end if;

end; $function$;

但实际测试中，第二条记录的触发器未检测到刚插入的第一条记录，仍判定为唯一并保存。

问题解答 #

1. 为何第二条记录的BEFORE触发器无法检测到刚插入的第一条记录？ #

这是因为PostgreSQL的事务可见性规则：在默认的READ COMMITTED隔离级别下，行级BEFORE触发器执行时，同一事务中之前插入的记录还未完成正式写入（触发器返回NEW后才会写入表），触发器内的查询无法看到这些尚未提交的事务内记录。简单来说，第一条记录的插入流程还没走完，第二条记录的触发器查询pois表时，看不到第一条记录的存在。

另外，触发器里多余的ST_SetSRID(NEW.point, 4326)如果NEW.point本身已带SRID，可能会引发问题，但这不是导致检测不到的原因。

2. 如何实现基于点近距离的唯一性校验？ #

有几种可靠的方案：

方案1：改进触发器，跟踪事务内待插入记录

通过临时表跟踪同一事务中已通过校验的记录，触发器同时查询正式表和临时表：

CREATE OR REPLACE FUNCTION check_duplicates()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    -- 创建临时表存储事务内已通过校验的点
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'pg_temp' AND tablename = 'temp_pois') THEN
        CREATE TEMP TABLE temp_pois (point geometry(point));
    END IF;

    -- 同时检查正式表和临时表中的近点，用ST_DWithin替代ST_Distance以利用空间索引
    IF EXISTS (
        SELECT 1 FROM pois WHERE ST_DWithin(point, NEW.point, 0.00001)
        UNION ALL
        SELECT 1 FROM pg_temp.temp_pois WHERE ST_DWithin(point, NEW.point, 0.00001)
    ) THEN
        RAISE NOTICE 'New: % exists - skip!', NEW.name;
        RETURN NULL;
    ELSE
        INSERT INTO pg_temp.temp_pois VALUES (NEW.point);
        RAISE NOTICE 'New: % unique - save!', NEW.name;
        RETURN NEW;
    END IF;
end; $function$;

临时表会在事务结束后自动销毁，解决了同一事务内的可见性问题，同时ST_DWithin能利用空间索引大幅提升查询效率。

方案2：空间索引+批量预处理

如果是批量插入场景，先创建空间索引优化查询：

CREATE INDEX idx_pois_point ON pois USING GIST (point);

在插入前用ST_DWithin快速筛选重复点，过滤后再插入，不管是应用端还是数据库端处理都能大幅提升效率。

方案3：AFTER触发器删除重复（不推荐高并发场景）

先允许插入，再在AFTER触发器中删除距离过近的重复记录。但这种方式会产生不必要的写入，高并发下可能出现竞态问题，仅适合低并发场景。

3. 是否可以使用排除约束索引实现该需求？ #

可以，这是推荐的最优方案。排除约束是数据库原生的约束机制，性能更好，能避免触发器的可见性问题，写法也更简洁：

1. 先安装支持空间+GIST结合的扩展（如果未安装）：

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 创建排除约束：

ALTER TABLE pois ADD CONSTRAINT exclude_close_points
EXCLUDE USING GIST (point)
WHERE (ST_DWithin(point, point, 0.00001));

这个约束会自动检查新插入的点与已有所有点的距离，当距离小于0.00001时直接拒绝插入，全程由数据库内核处理，性能和可靠性都优于触发器方案。

内容的提问来源于stack exchange，提问作者linderman