From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "'Nguyen Hoai Nam *EXTERN*'" <namptit307(at)gmail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: The problem is related to concurrent resquests |
Date: | 2016-05-23 07:59:10 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B53852E5D@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | zz_11 | 2016-05-23 08:33:44 | very slow postgresql startup |
Previous Message | Nguyen Hoai Nam | 2016-05-23 05:31:58 | The problem is related to concurrent resquests |