Re: The problem is related to concurrent resquests

Many thank you, Mr.Laurenz

This is useful for me. I will apply this to my DB.

Best regards
Nam
-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Tuesday, May 24, 2016 5:32 PM
To: 'Nguyen Hoai Nam *EXTERN*'
Cc: pgsql-admin(at)postgresql(dot)org
Subject: RE: [ADMIN] The problem is related to concurrent resquests

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;

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