Re: The problem is related to concurrent resquests

[Nguyen Hoai Nam <namptit307@xxxxxxxxx>]

Dear Mr.Laurenz

Thanks for your support

In fact, in mysql, trigger can detect this case. But when I apply this method to Postgresql, it's not ok. Do you know why?

I will follow your suggestion. However imaging we add a "network_id" column (it contain the id of network)  to "subnet" tables.. so I need to do what to prevent a record with overlap CIDR and same network_id.?

Currently, I am Openstacker (do you know Openstack)  and try to fix bug related to overlap CIDR with concurent request. I have done with Mysql but not Postgresql.

Best regards

Nam

2016-05-23 16:32 GMT+07:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>:

Nguyen Hoai Nam wrote:
> 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).
[...]
> 2.2 Writing a function for trigger and a trigger as follows:
>
[...]
> - Create trigger:
[...]
>
>
> 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.

A trigger cannot guarantee that the condition will always be satisfied.
At the time that the concurrent triggers run the SELECT, everything is fine.

Do use an SQL contraint to enforce a table constraint.
If you really need to store "subnet" as varchar (why?), define the constraint like this:

EXCLUDE USING gist (subnet::cidr inet_ops WITH &&)

Yours,
Laurenz Albe