Re: The problem is related to concurrent resquests

Hello Laurenz Albe

Do you remember me. :-)

Last time, You supported me about by Gist in Postgresql. Currently, I have
a problem with DB, could you please help me.

2016-05-24 17:31 GMT+07:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:

> Please keep the list posted!
>
> Nguyen Hoai Nam wrote:
> > Step1: I would to create "network" table with three columns including
> id, network_id, subnet. It's
> > like below:
> >
> > +--------------------+---------------+
> > | id |network_id | subnet |
> > +------------------------------------+
> > | 1 | aa |192.168.1.0/24 |
> > | | | |
> > | | | |
> > +--------+-----------+---------------+
> >
> > This table have condition: If a new record with overlap subnet and same
> value's network_id then DB
> > will not allow inster to DB
> >
> > For example:
> >
> > Request1: test=> INSERT INTO network VALUES (2, aa,'192.168.1.0/24');
> > The result is that DB doesn't allow to insert to DB. Becase it violate
> overlap CIDR and same value's
> > network_id
> >
> > Request2: test=> INSERT INTO network VALUES (3, bb,'192.168.1.0/24');
> > The result is that DB ALLOW to insert to DB. Because this reqest has
> network_id = bb, this value is
> > different with existing value (aa)
>
> This is getting more difficult, but you can *still* do it with an exclusion
> constraint in PostgreSQL. You need to install an extension with a
> GiST operator class for varchar:
>
> test=# CREATE EXTENSION btree_gist;
>
>
At this point, the system require superuser permission. But in my case, an
user doesn't have superuser permission so they can't create a btree_gist
extersion. Do you any idea to solve this? Currently, I am trying to use
"GIN" but it's not still success. How about "GIN"?

Then you can do the following:
>
> CREATE TABLE network (
> id integer PRIMARY KEY,
> network_id varchar(20) NOT NULL,
> subnet cidr NOT NULL
> );
>
> ALTER TABLE network
> ADD CONSTRAINT network_subnet_excl
> EXCLUDE USING gist (
> network_id gist_text_ops WITH =,
> subnet inet_ops WITH &&
> );
>
> Then you get:
>
> test=> INSERT INTO network VALUES (1, 'aa','192.168.1.0/24');
> INSERT 0 1
>
> test=> INSERT INTO network VALUES (2, 'aa', '192.168.1.0/24');
> ERROR: conflicting key value violates exclusion constraint
> "network_subnet_excl"
> DETAIL: Key (network_id, subnet)=(aa, 192.168.1.0/24) conflicts with
> existing key (network_id, subnet)=(aa, 192.168.1.0/24).
>
> test=> INSERT INTO network VALUES (3, 'bb', '192.168.1.0/24');
> INSERT 0 1
>
>
> As Kevin said, using SERIALIZABLE transactions is an alternative, but a
> constraint
> is probably better.
>
> Yours,
> Laurenz Albe
>

Best and regards
Nam