Re: The problem is related to concurrent resquests

On Mon, May 23, 2016 at 12:31 AM, Nguyen Hoai Nam <namptit307(at)gmail(dot)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