Re: duplicate key triggers possible?

Burra <burra(at)colorado(dot)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