Hi,
Using this query plan, an extra uid shows up in this example. We are in the
process of upgrading from v810 to v814. Does anyone see anything wrong with
this query plan that might be causing a problem?
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))
... 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
Tom writes..
Hmmm .... that looks sorta familiar. What is the query plan that's used
for this SELECT?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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.
