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@xxxxxxxxxx] Sent: Tuesday, May 24, 2016 5:32 PM To: 'Nguyen Hoai Nam *EXTERN*' Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: RE: 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
