Hey Tom,
Here is the query:
DELETE FROM recent_projects WHERE project_id = 3 AND user_id = 139;
And here is the query plan:
Index Scan using pk_recent_projects on recent_projects (cost=0.00..5.81
rows=1 width=6)
Index Cond: ((user_id = 139) AND (project_id = 3))
The table definition is :
CREATE TABLE recent_projects
(
user_id int4 NOT NULL,
project_id int4 NOT NULL,
last_viewed timestamp,
CONSTRAINT pk_recent_projects PRIMARY KEY (user_id, project_id),
CONSTRAINT fk_recent_project_id FOREIGN KEY (project_id)
REFERENCES project (project_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE CASCADE,
CONSTRAINT fk_recent_user_id FOREIGN KEY (user_id)
REFERENCES users (user_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
This is a table with a lot of transactions.
The behavior we noticed is that we do the delete as specified above and then
do a reinsert with a new timestamp and everything else the same (lazy I
know, but not my code). What happens some of the time is that the reinsert
fails and give a duplicate key failure. What has fixed this in the past is
reindexing the table - but we don't want to rely on that forever.
We are also have an issue with processes locking up. We can't kill -9 pid
because postgres ends up restarting the whole cluster. What can we do?
kill -s INT TERM or SIGQUIT don't seem to work either. Should we be root or
logged in as postgres when we try to kill these?
Thanks!
~DjK
From: Tom Lane <tgl@xxxxxxxxxxxxx>
To: "Mr. Dan" <bitsandbytes88@xxxxxxxxxxx>
CC: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] On-line backup Date: Mon, 17 Jul 2006 14:43:30 -0400
"Mr. Dan" <bitsandbytes88@xxxxxxxxxxx> writes:
> ... What happens is that we have a 'hot' table (one with many many
> transactions) that gets inserted and deleted often. About once a month
now
> when we do a select from that table the results of the select do not
match
> the where clause, ex.
> select * from recent_projects
> where user_id = 139
> sometimes produces these results:
> user_id project_id
> 139 3
> 139 1
> 139 17
> 754 11
Hmmm .... that looks sorta familiar. What is the query plan that's used
for this SELECT?
regards, tom lane
