Strange behavior of TRIGGER

From: "Nurlan Mukhanov" <nurike(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange behavior of TRIGGER
Date: 2007-10-01 17:19:15
Message-ID: 1527ac090710011019k5cd7904ci6bfa6198a6db1914@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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();

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gauthier, Dave 2007-10-01 17:26:45 Upgrading PG
Previous Message Josh Trutwin 2007-10-01 17:14:36 Find clusters containing a schema?