Обсуждение: The problem is related to concurrent resquests

Nguyen Hoai Nam wrote:
> Step 1: I create a "network" table including "id" and "subnet" column.
> 
> - the "subnet" colum contain CIDR. For example: 192.168.1.0/24
> 
> Step 2:
> 
> I create a function to dectect overlap subnet. If there is overlap CIDR then it return "True" and vice versa.
> For example: 192.168.0.0/16 overlap with 192.168.1.0/24
> 
> Then I create a trigger as below:
> 
> - Funtion for trigger:
> """
> CREATE OR REPLACE FUNCTION preventing_overlap_cidr()
>   RETURNS trigger AS
> $BODY$
>     DECLARE msg VARCHAR(200);
>     BEGIN
>     IF (EXISTS(SELECT * FROM network WHERE  cidr_overlap(subnet, NEW.subnet))) THEN
>         msg = CONCAT(
>             'inserted subnet ', NEW.subnet,
>             ' conflicts with existing subnets');
>         RAISE EXCEPTION  USING message = msg, ERRCODE = 'XX000';
>     END IF;
>     RETURN NEW;
>     END;
>     $BODY$
> 
> """"
> create trigger:
> """
> "CREATE TRIGGER no_overlap_cidr_subnets
>     BEFORE INSERT ON network
>     FOR EACH ROW
>     EXECUTE PROCEDURE preventing_overlap_cidr()
> 
> 
> But in my case, if I have two currenty requests with overlap CIDR, they insert "network" table at the
> sam time. The trigger can not prevent this, so we can still create two subnets with overlap CIDR.
> 
> That all my test. Could you please help with how to prevent this in case of  concurent request in
> Postgresql.

A trigger is not the right thing for that, what you need is a constraint.

First, is there a difference between your function "cidr_overlap" and the "&&" operator?

If not, you can easily achieve your goal with an exclusion constraint:

test=> CREATE TABLE network (
          id integer PRIMARY KEY,
          subnet cidr NOT NULL,
          EXCLUDE USING gist (subnet inet_ops WITH &&)
       );

test=> INSERT INTO network VALUES (1, '192.168.0.0/16');
INSERT 0 1
test=> INSERT INTO network VALUES (2, '192.168.1.0/24');
ERROR:  conflicting key value violates exclusion constraint "network_subnet_excl"
DETAIL:  Key (subnet)=(192.168.1.0/24) conflicts with existing key (subnet)=(192.168.0.0/16).

Yours,
Laurenz Albe

Nguyen Hoai Nam wrote:
> Thank you so much for your time. Let me explain my problem:
> 
> 1. There are some conditions which I must follow it.
> 
> Condition 1: the type of "subnet" is varchar(30),
> Condition 2: currently, I am about to use trigger and function to detect and prevent CIDR overlapping
> (subnet's value)
> 
> 2. What I am doing
> 
> 2.1 Writing a function can detect overlap CIDR (called cidr_overlap).
[...]
> 2.2 Writing a function for trigger and a trigger as follows:
> 
[...]
> - Create trigger:
[...]
> 
> 
> Please don't follow detail syntax. Something like that. Actually, this method is good active in case
> of request sequently. But in case of we have two resquest which insert to DB at the same time (it mean
> there are concurrent request). Have you ever see this problem, could please give me some advices to
> slove it.

A trigger cannot guarantee that the condition will always be satisfied.
At the time that the concurrent triggers run the SELECT, everything is fine.

Do use an SQL contraint to enforce a table constraint.
If you really need to store "subnet" as varchar (why?), define the constraint like this:

EXCLUDE USING gist (subnet::cidr inet_ops WITH &&)

Yours,
Laurenz Albe

Nguyen Hoai Nam wrote:
> In fact, in mysql, trigger can detect this case. But when I apply this method to Postgresql, it's not
> ok. Do you know why?

I cannot tell you about MySQL, but if you insert two rows at the same time
in PostgreSQL, the SQL queries run from the trigger can run concurrently.
None of them will see the row of the other insert, because this will become
only visible when the transaction (which includes the execution trigger)
has completed.

> I will follow your suggestion. However imaging we add a "network_id" column (it contain the id of
> network)  to "subnet" tables.. so I need to do what to prevent a record with overlap CIDR and same
> network_id.?

There is no need to add a new column, just add a constraint.

If the table was defined with a varchar column like this:

   CREATE TABLE network (id integer PRIMARY KEY, subnet varchar(30) NOT NULL);

Add the following constraint:

   ALTER TABLE network ADD CONSTRAINT network_subnet_excl EXCLUDE USING gist ((subnet::cidr) inet_ops WITH &&);

Then you are done.

Yours,
Laurenz Albe

On Mon, May 23, 2016 at 12:31 AM, Nguyen Hoai Nam <namptit307@gmail.com> wrote:

> [description of problem, sort of...]

You are making this harder on people who want to help than you have
to do.  You omitted steps, included code that didn't actually run,
and just gave hand-wavey descriptions of some parts of the issue.
Whenever possible it is best to include a script of the steps to
show the problem, starting from an empty database.  Something like
this would have been better:


-- connection 1

CREATE TABLE network (id int NOT NULL PRIMARY KEY, subnet cidr NOT NULL);

CREATE FUNCTION cidr_overlap (cidr1 inet, cidr2 inet)
  RETURNS BOOLEAN
  LANGUAGE SQL
AS $$ SELECT ((cidr1 <<= cidr2) OR (cidr2 <<= cidr1)); $$;

CREATE OR REPLACE FUNCTION preventing_overlap_cidr()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$BODY$
  DECLARE msg VARCHAR(200);
  BEGIN
    IF (EXISTS(SELECT * FROM network WHERE  cidr_overlap(subnet,
NEW.subnet))) THEN
      msg = CONCAT(
            'inserted subnet ', NEW.subnet,
            ' conflicts with existing subnets');
      RAISE EXCEPTION USING message = msg, ERRCODE = 'XX000';
    END IF;
    RETURN NEW;
  END;
$BODY$;

CREATE TRIGGER no_overlap_cidr_subnets
    BEFORE INSERT ON network
    FOR EACH ROW
    EXECUTE PROCEDURE preventing_overlap_cidr();

BEGIN;
INSERT INTO network VALUES (1, '192.168.0.0/16');

-- connection 2

BEGIN;
INSERT INTO network VALUES (2, '192.168.1.0/24');

-- connection 1

COMMIT;

-- connection 2

COMMIT;


Both rows are inserted, and that's not what you want.  Now try
again (starting from an empty database) but first run this on each
connection (or set the option in postgresql.conf and reload the
configuration):

set default_transaction_isolation = 'serializable';

Now when you run this, the second COMMIT gets this error:

test=# COMMIT;
ERROR:  could not serialize access due to read/write dependencies
among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT:  The transaction might succeed if retried.

If you retry the transaction from the start (as the hint suggests) you get:

test=# INSERT INTO network VALUES (2, '192.168.1.0/24');
ERROR:  inserted subnet 192.168.1.0/24 conflicts with existing subnets
CONTEXT:  PL/pgSQL function preventing_overlap_cidr() line 8 at RAISE

So the behavior you want is available from triggers, but only if
you use serializable transactions.  You might want to read these
pages:

http://www.postgresql.org/docs/current/static/transaction-iso.html

https://wiki.postgresql.org/wiki/SSI

That said, when a declarative constraint is available which is
capable of enforcing the exact business rule you need, it is almost
always better to use the declarative constraint than to put
imperative coding into a trigger for it.  You should try what Albe
has been suggesting.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

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]
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
> 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
installan 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

Nguyen Hoai Nam wrote:
> 2016-05-24 17:31 GMT+07:00 Albe Laurenz <laurenz.albe@wien.gv.at>:
>> 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"?

Yes, you need a superuser to install the extension for you.
This has to be done only once, and from then on everybody can
use the feature.

I'm sure there is a superuser who can do that for you, right?

Yours,
Laurenz Albe