Re: duplicate key triggers possible?

Burra <burra@colorado.edu> writes:
> ...right now I have a trigger set up "BEFORE INSERT" to ...

> CREATE FUNCTION duplicate_count () RETURNS OPAQUE AS '
>     DECLARE
>       current_count integer;
>     BEGIN
>         -- Select count from events
>         SELECT INTO current_count count from events where type=NEW.type;
>         IF current_count ISNULL THEN
>           RETURN NEW;
>         END IF;
>         UPDATE events SET count=(count+1) where type=NEW.type;
>         RETURN NEW;
>     END;
> ' LANGUAGE 'plpgsql';

> CREATE TRIGGER insert_duplicate_count BEFORE INSERT ON events FOR EACH ROW
> EXECUTE PROCEDURE duplicate_count();

It might work if you returned NULL, not NEW, at the end (to suppress the
INSERT attempt).  Slightly better is to turn the logic around: try the
UPDATE, and then allow the INSERT to proceed if you observe that the
UPDATE updated zero rows.  This avoids the extra SELECT.

In either case though I suspect you will have headaches with concurrency
issues --- what if two backends run this code at about the same time?

            regards, tom lane