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