Postgresql – Trigger update does not seem to finish

performanceplpgsqlpostgresqlquery-performancetrigger

I'm new to PostgreSQL triggers & functions and such.

I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update the already existing keyword.

With the trigger below, inserting works fine, updating however takes very long – and I've always canceled it after a few minutes so I'm not sure what it would output.

Table: keywords

   Column   |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('keywords_id_seq'::regclass)
 keyword    | character varying(255)      | not null
 views      | integer                     | not null
 visits     | integer                     | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null


CREATE FUNCTION update_if_exists() RETURNS trigger AS $keyword$ DECLARE result INTEGER;
BEGIN
    -- Check whether the keyword already exists.
    result = (SELECT count(keyword) FROM keywords WHERE keyword = new.keyword);

    -- If we've got a match run an update statement.
    IF result = 1 THEN
        UPDATE keywords
        SET
            views       = views + new.views,
            visits      = visits + new.visits
        WHERE
            keyword     = new.keyword;

        RETURN NULL;
    END IF;

    RETURN new;
END; $keyword$ LANGUAGE plpgsql;


CREATE TRIGGER id BEFORE INSERT OR UPDATE ON keywords
    FOR EACH ROW EXECUTE PROCEDURE update_if_exists();

Best Answer

You caused an endless loop by making the trigger BEFORE INSERT OR UPDATE . Use instead:

CREATE FUNCTION update_if_exists()
  RETURNS trigger AS
$keyword$
BEGIN

-- Check whether the keyword alrady exists.
UPDATE keywords
SET    views  = views + NEW.views
      ,visits = visits + NEW.visits
WHERE  keyword = NEW.keyword;

IF FOUND THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;

END
$keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT ON keywords     -- not OR UPDATE!
FOR EACH ROW EXECUTE PROCEDURE update_if_exists();
Related Question