Обсуждение: duplicate key triggers possible?

Hello everyone,
I was wondering if it is possible to do this...

Everytime an attempted insert results in a "duplicate key" error I want to
incriment an integer field of the key already in the database.

...so, like this...

                                  Table "events"
 Attribute |           Type           |             Modifier
-----------+--------------------------+-----------------------------------
 event     | integer            | not null default nextval('eventid'::text)
 time      | timestamp with time zone | not null default now()
 rtime     | timestamp with time zone |
 type      | integer                  | not null default 0 PRIMARY KEY
 count     | integer                  | not null default 1


test=> INSERT INTO events (type) VALUES ('10');
INSERT 35533 1
test=> select * from events;
 event |          time          | rtime | type | count
-------+------------------------+-------+------+-------
     1 | 2001-11-26 10:42:35-07 |       |   10 |     1

test=> INSERT INTO events (type) VALUES ('10');
ERROR:  Cannot insert a duplicate key into unique index events_pkey
test=> select * from events;
 event |          time          | rtime | type | count
-------+------------------------+-------+------+-------
     1 | 2001-11-26 10:42:35-07 |       |   10 |     2



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

..but, this doesn't run because of the PRIMARY KEY constraint. Any ideas
on how to get this working?

Thanks.

--------------------[-- burra@colorado.edu --]--------------------------

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