The problem is related to concurrent resquests

[Nguyen Hoai Nam <namptit307@xxxxxxxxx>]

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