From: | Nguyen Hoai Nam <namptit307(at)gmail(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | "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 09:55:26 |
Message-ID: | CA+vg4mM+XssGs-CMfn0gLAf7fckNC0o+4mjg9gUZ2HJcPUg-kQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(dot)albe(at)wien(dot)gv(dot)at>:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2016-05-23 11:51:09 | Re: The problem is related to concurrent resquests |
Previous Message | zz_11 | 2016-05-23 09:49:09 | Re: very slow postgresql startup |