Dear Mr.Laurenz
Thank you so much for your time. Let me explain my problem:
1. There are some conditions which I must follow it.
Condition 1: the type of "subnet" is varchar(30),
Condition 2: currently, I am about to use trigger and function to detect and prevent CIDR overlapping (subnet's value)
2. What I am doing
2.1 Writing a function can detect overlap CIDR (called cidr_overlap).
Input: subnet
Ouput: True if cidr overlap, False if not
Here is function:
CREATE FUNCTION cidr_overlap (cidr1 VARCHAR(30), cidr2 VARCHAR(30))
RETURNS BOOLEAN AS
$$
DECLARE cidr_inet_1 inet;
DECLARE cidr_inet_2 inet;
BEGIN
cidr_inet_1 = cast(cidr1 as inet);
cidr_inet_2 = cast(cidr2 as inet);
RETURN
(cidr_inet_1 <<= cidr_inet_2) OR (cidr_inet_2 <<= cidr_inet_1);
END;
$$ LANGUAGE plpgsql;
2.2 Writing a function for trigger and a trigger as follows:
- 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
BEFORE INSERT ON network
FOR EACH ROW
EXECUTE PROCEDURE preventing_overlap_cidr()
Please don't follow detail syntax. Something like that. Actually, this method is good active in case of request sequently. But in case of we have two resquest which insert to DB at the same time (it mean there are concurrent request). Have you ever see this problem, could please give me some advices to slove it.
Best regardsThank you so much for your time. Let me explain my problem:
1. There are some conditions which I must follow it.
Condition 1: the type of "subnet" is varchar(30),
Condition 2: currently, I am about to use trigger and function to detect and prevent CIDR overlapping (subnet's value)
2. What I am doing
2.1 Writing a function can detect overlap CIDR (called cidr_overlap).
Input: subnet
Ouput: True if cidr overlap, False if not
Here is function:
CREATE FUNCTION cidr_overlap (cidr1 VARCHAR(30), cidr2 VARCHAR(30))
RETURNS BOOLEAN AS
$$
DECLARE cidr_inet_1 inet;
DECLARE cidr_inet_2 inet;
BEGIN
cidr_inet_1 = cast(cidr1 as inet);
cidr_inet_2 = cast(cidr2 as inet);
RETURN
(cidr_inet_1 <<= cidr_inet_2) OR (cidr_inet_2 <<= cidr_inet_1);
END;
$$ LANGUAGE plpgsql;
2.2 Writing a function for trigger and a trigger as follows:
- 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
BEFORE INSERT ON network
FOR EACH ROW
EXECUTE PROCEDURE preventing_overlap_cidr()
Please don't follow detail syntax. Something like that. Actually, this method is good active in case of request sequently. But in case of we have two resquest which insert to DB at the same time (it mean there are concurrent request). Have you ever see this problem, could please give me some advices to slove it.
2016-05-23 14:59 GMT+07:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>:
A trigger is not the right thing for that, what you need is a constraint.Nguyen Hoai Nam wrote:
> 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.
First, is there a difference between your function "cidr_overlap" and the "&&" operator?
If not, you can easily achieve your goal with an exclusion constraint:
test=> CREATE TABLE network (
id integer PRIMARY KEY,
subnet cidr NOT NULL,
EXCLUDE USING gist (subnet inet_ops WITH &&)
);
test=> INSERT INTO network VALUES (1, '192.168.0.0/16');
INSERT 0 1
test=> INSERT INTO network VALUES (2, '192.168.1.0/24');
ERROR: conflicting key value violates exclusion constraint "network_subnet_excl"
DETAIL: Key (subnet)=(192.168.1.0/24) conflicts with existing key (subnet)=(192.168.0.0/16).
Yours,
Laurenz Albe
