Duplicate key violation on UPDATE - Mailing list pgsql-general

Hi all,

I have recently turned up the postgresql logging facilities on a
rather database intensive application in the hope of finding bugs and
tuning queries. We're using 8.0.8, though thinking of moving to 8.3 as
the new HOT functionality looks like it would be useful for us given
the high UPDATE frequency in some of our tables. Anyhow, the
problem...

The table is question is defined as:

CREATE SEQUENCE NimrodGridRun_id ;
create table NimrodGridRun(
    agent_id    INTEGER DEFAULT nextval('NimrodGridRun_id') primary key,
    service_id  INTEGER not null references NimrodGridService,
    compute_id  INTEGER not null references NimrodComputeResource,
    executable  varchar(255) not null,
    arguments   TEXT not null,
    exe_type    char(1) not null check (exe_type in ('A', 'P', 'R', 'S')),
    control varchar(8) not null default 'start'
            check (control in ('nostart', 'start', 'stop')),
    status  varchar(8) not null default 'pending'
            check (status in ('pending', 'active',
                'queued', 'failed', 'done', 'stopping', 'apending')),
    actuator_id INTEGER references NimrodGridActuator
            on delete set null,
    job_ident   varchar(255),
    error_info  TEXT not null default '',
    more_info   TEXT not null default '',
    active_time timestamp,
    lastcheck   timestamp,
    tag         INTEGER not null default 0 references NimrodJobAgentTag,
    run_once    char(1) not null default 'F'
        check (run_once in ('F', 'T')),
    tmpcleaned  char(1) not null default 'F'
        check (tmpcleaned in ('F', 'T')),
    host        varchar(255),
    workdir     varchar(255),
    duration    real not null,
    idletime    real not null,
    finishby    varchar(8)
        check (finishby in ('agent', 'asched', 't5misc', 'actuator',
                    'dbserver', 'fserver')),
    finish_reason varchar(255),
    create_time timestamp not null default CURRENT_TIMESTAMP,
    start_time  timestamp,
    submit_time timestamp,
    finish_time timestamp,
    lastheartbeat timestamp,
    checkcount INTEGER not null default 0
) WITH OIDS;


I came across this error in the logs and can't understand what might
be causing it.
PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev
- LOG:  statement: BEGIN
PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev
- LOG:  statement:
                                UPDATE NimrodGridRun
                                SET control = 'stop'
                                WHERE status = 'done'
                                AND tag = '9'
                    AND compute_id = 2
                                AND finish_time < CURRENT_TIMESTAMP -
INTERVAL '1 minute'
PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev
- ERROR:  duplicate key viola
tes unique constraint "nimrodgridrun_pkey"
PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev
- STATEMENT:
                                UPDATE NimrodGridRun
                                SET control = 'stop'
                                WHERE status = 'done'
                                AND tag = '9'
                    AND compute_id = 2
                                AND finish_time < CURRENT_TIMESTAMP -
INTERVAL '1 minute'
PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev
- LOG:  statement: ROLLBACK

Why would we be getting a duplicate key violation on the primary key
of this table when we aren't doing anything in the UPDATE (that I can
tell) to change it?

Cheers,
-Blair

--
In science one tries to tell people, in such a way
as to be understood by everyone, something that
no one ever knew before. But in poetry, it's the
exact opposite.
 - Paul Dirac