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.
Step 2:
I create a function to dectect overlap subnet. If there is overlap CIDR then it return "True" and vice versa.
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
