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

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