Postgresql (Postgis) parallel triggers on insert

Question

I have a table the following table:

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

);

I want to make pois unique by ST_DISTANCE < 0.00001. What i mean:

Inserting 2 records:

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

You can see, that point for both records are very close. Those are acrually same location, but saved with slight gps coordinate difference (can be few meters).

Those records can be inserted as a BATCH or single transaction or one after another.

I want to be able to "catch" such duplicates. Name 2 should be discarded and not saved.

What i try now is using BEFORE INSERT and AFTER INSERT triggers (PER ROW):

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$;

When two records come (refined logs):

BEFORE TRIGGER: New Name 1
BEFORE TRIGGER: New Name 1 - unique - save!

... record 1 saved and AFTER trigger raised notification, so i can see in the logs the order... Then, record 2 comes milliseconds after:

BEFORE TRIGGER: New Name 2
BEFORE TRIGGER: New Name 2 - unique - save!

But WHY? Rercord 2 should detect record 1, that is already saved in the DB and the distance between them is < 0.00001, so record 2 is actually duplication of record 1 and i expected to see:

BEFORE TRIGGER: New Name 2 - exists - skip!

I try with AFTER triggers also. Save condition, but if exists -

Since i am relativery new to RDBs (Postgis), this task give me headache.

1. BEFORE trigger for record 2 start few milliseconds record 1 AFTER trigger, but in the IF condition record 1 is not fetched. Why?

2. How can i archieve this "uniqueness", based on very close distance between points?

3. Maybe exclude constraint index?

Answer 1

If record 2 arrives after record 1 is inserted but before it is committed, then the trigger being fired for 2 won't see record 1 and will allow the duplicate. Uncommitted records cannot be seen by other processes.

Yes, an EXCLUDE constraint is the solution. In that case, the "trigger" for 2 doesn't need to follow normal user visibility rules, so it would detect the possible confliction with uncommitted record 1, and block until that resolves to either commit or abort.