Strange behavior of TRIGGER

Поиск
Список
Период
Сортировка
От Nurlan Mukhanov
Тема Strange behavior of TRIGGER
Дата
Msg-id 1527ac090710011019k5cd7904ci6bfa6198a6db1914@mail.gmail.com
обсуждение исходный текст
Ответы Re: Strange behavior of TRIGGER  (Jeff Davis <pgsql@j-davis.com>)
Список 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();

В списке pgsql-general по дате отправления:

Предыдущее
От: "Jason L. Buberel"
Дата:
Сообщение: Re: Strange discrepancy in query performance...
Следующее
От: Goboxe
Дата:
Сообщение: Re: Partitioned table limitation