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.
A trigger is not the right thing for that, what you need is a constraint.
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