From: "Hoai Nam" <namptit307@gmail.com>
To: "'Albe Laurenz'" <laurenz.albe@wien.gv.at>
Cc: <pgsql-admin@postgresql.org>
References: 
 <CA+vg4mN16A_-63iX+MdeppFOi5iDOUaufWkoQQnqHPW1OLdZ=Q@mail.gmail.com>
	<A737B7A37273E048B164557ADEF4A58B53852E5D@ntex2010i.host.magwien.gv.at>
	<CA+vg4mPNKGkUYWgoS=44Hzs6tctUupcvcAeQyNwj=mphxL9AtA@mail.gmail.com>
	<A737B7A37273E048B164557ADEF4A58B53852F56@ntex2010i.host.magwien.gv.at>
	<CA+vg4mM+XssGs-CMfn0gLAf7fckNC0o+4mjg9gUZ2HJcPUg-kQ@mail.gmail.com>
	<A737B7A37273E048B164557ADEF4A58B53852FE7@ntex2010i.host.magwien.gv.at>
	<CA+vg4mP2HqUWF19xHi_t2awAaR75PmdkL9nJ-GWMzmbFJK2Z9Q@mail.gmail.com>
	<A737B7A37273E048B164557ADEF4A58B53853373@ntex2010i.host.magwien.gv.at>
 <CA+vg4mMn57tLGO3xHMmpEPkPNvk1d1PRo7cW64aKJAajmKA=jQ@mail.gmail.com>
 <A737B7A37273E048B164557ADEF4A58B5385385F@ntex2010i.host.magwien.gv.at>
In-Reply-To: 
 <A737B7A37273E048B164557ADEF4A58B5385385F@ntex2010i.host.magwien.gv.at>
Subject: Re: The problem is related to concurrent resquests
Date: Tue, 24 May 2016 22:56:47 +0700
Message-ID: <005301d1b5d4$e2434a50$a6c9def0$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: 
 AQJAfIt5yz4TomieJ73NOhflbNRpaAH2q+/iAdApSisBiBhfIwGjEVBjAczB58kCr/oyAAKBuUm/Ai8PY8MBWvag6Z5e1kpg
Content-Language: en-us
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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.albe@wien.gv.at]=20
Sent: Tuesday, May 24, 2016 5:32 PM
To: 'Nguyen Hoai Nam *EXTERN*'
Cc: pgsql-admin@postgresql.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=20
> id, network_id, subnet. It's like below:
>=20
> +--------------------+---------------+
> |  id    |network_id |  subnet       |
> +------------------------------------+
> |   1    |     aa    |192.168.1.0/24 |
> |        |           |               |
> |        |           |               |
> +--------+-----------+---------------+
>=20
> This table have condition: If a new record with overlap subnet and=20
> same value's network_id then DB will not allow inster to DB
>=20
> For example:
>=20
> Request1: test=3D> INSERT INTO network VALUES (2, aa,'192.168.1.0/24');=20
> The result is that DB doesn't allow to insert to DB. Becase it violate=20
> overlap CIDR and same value's network_id
>=20
> Request2: test=3D> INSERT INTO network VALUES (3, bb,'192.168.1.0/24');=20
> The result is that DB ALLOW to insert to DB. Because this reqest has=20
> network_id =3D 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 ope=
rator class for varchar:

test=3D# 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 =3D,
            subnet inet_ops WITH &&
         );

Then you get:

test=3D> INSERT INTO network VALUES (1, 'aa','192.168.1.0/24'); INSERT 0 1

test=3D> 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)=3D(aa, 192.168.1.0/24) conflicts with exi=
sting key (network_id, subnet)=3D(aa, 192.168.1.0/24).

test=3D> 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 con=
straint is probably better.

Yours,
Laurenz Albe


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin