Re: The problem is related to concurrent resquests - Mailing list pgsql-admin

From Nguyen Hoai Nam
Subject Re: The problem is related to concurrent resquests
Date
Msg-id CA+vg4mNuCH3KrjtHO0PZqvP9Ek9ARhiyNVHc=LyTxyor9uEURg@mail.gmail.com
Whole thread Raw
In response to Re: The problem is related to concurrent resquests  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Responses Re: The problem is related to concurrent resquests
List pgsql-admin
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.albe@wien.gv.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

pgsql-admin by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Postgres v9.6
Next
From: Albe Laurenz
Date:
Subject: Re: The problem is related to concurrent resquests