Thread: Strange behavior of TRIGGER

There is a table with unique rows. But before insert trigger checks
data and returns NULL if such record exist and NEW if not.

But from time to time I'm getting an error in my log file

faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
('93701','41719')
context: ERROR: duplicate key violates unique constraint "viewed_search_members"

If to try execute this query manually - everything is ok. But what is
the reason of such behavior? How it can be?

Here is schema:

CREATE TABLE viewed_members
(
  member_id integer NOT NULL,
  viewed_id integer NOT NULL,
  viewed_date timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX viewed_search_members
  ON viewed_members
  USING btree
  (member_id, viewed_id);

ALTER TABLE viewed_members CLUSTER ON viewed_search_members;

CREATE OR REPLACE FUNCTION viewed_members()
  RETURNS "trigger" AS
$BODY$
DECLARE
        viewed RECORD;
BEGIN
        IF (TG_OP = 'INSERT') THEN
        SELECT * INTO viewed FROM viewed_members WHERE member_id =
NEW.member_id AND viewed_id = NEW.viewed_id;
                IF NOT FOUND THEN
                        --RAISE NOTICE 'Adding new record';
                        RETURN NEW;
                ELSE
                        --RAISE NOTICE 'Record exist';
                        RETURN NULL;
                END IF;
        END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


CREATE TRIGGER viewed_members
  BEFORE INSERT
  ON viewed_members
  FOR EACH ROW
  EXECUTE PROCEDURE viewed_members();

On Mon, 2007-10-01 at 23:19 +0600, Nurlan Mukhanov wrote:
> There is a table with unique rows. But before insert trigger checks
> data and returns NULL if such record exist and NEW if not.
>
> But from time to time I'm getting an error in my log file
>
> faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
> ('93701','41719')
> context: ERROR: duplicate key violates unique constraint "viewed_search_members"
>

The trigger you wrote does not lock the table. A concurrent transaction
might insert a record with the same (member_id, viewed_id) that might
not yet be visible at the time of your trigger's SELECT.

To do this kind of thing safely, your trigger needs to lock the table
against writes before the SELECT operation.

That has a performance penalty, of course. What you should probably do
instead is just rely on the unique index to report an error. If you
don't want an error, you should catch the error in pl/pgsql as described
here:

http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING

That will perform better and allow you to disregard records without a
unique (member_id, viewed_id).

Regards,
    Jeff Davis