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
