The problem is related to concurrent resquests

Hi everyone,

I am newbie. I have a problem with Postgresql, could you please help me
with this problem.

I will explain my problem:

Step 1: I create a "network" table including "id" and "subnet" column.

- the "subnet" colum contain CIDR. For example: 192.168.1.0/24

Step 2:

I create a function to dectect overlap subnet. If there is overlap CIDR
then it return "True" and vice versa.
For example: 192.168.0.0/16 overlap with 192.168.1.0/24

Then I create a trigger as below:

- Funtion for trigger:
"""
CREATE OR REPLACE FUNCTION preventing_overlap_cidr()
RETURNS trigger AS
$BODY$
DECLARE msg VARCHAR(200);
BEGIN
IF (EXISTS(SELECT * FROM network WHERE cidr_overlap(subnet,
NEW.subnet))) THEN
msg = CONCAT(
'inserted subnet ', NEW.subnet,
' conflicts with existing subnets');
RAISE EXCEPTION USING message = msg, ERRCODE = 'XX000';
END IF;
RETURN NEW;
END;
$BODY$
""""
create trigger:

"""
"CREATE TRIGGER no_overlap_cidr_subnets
BEFORE INSERT ON network
FOR EACH ROW
EXECUTE PROCEDURE preventing_overlap_cidr()

But in my case, if I have two currenty requests with overlap CIDR, they
insert "network" table at the sam time. The trigger can not prevent this,
so we can still create two subnets with overlap CIDR.
That all my test. Could you please help with how to prevent this in case
of concurent request in Postgresql.

Best regards
NamNH