Re: The problem is related to concurrent resquests

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 regards
Nam

2016-05-23 14:59 GMT+07:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>:
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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux