From: Mr. Dan <bitsandbytes88@hotmail.com>
To: pgsql-admin@postgresql.org
Subject: btree index - incorrect results
Date: Mon, 24 Jul 2006 10:46:10 -0400
Message-ID: <BAY116-F3606EDEA3BEB51825DA2E8D1650@phx.gbl> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bitsandbytes88@hotmail.com
Subject: Re: btree index - incorrect results
In-Reply-To: <BAY116-F3606EDEA3BEB51825DA2E8D1650@phx.gbl>
References: <BAY116-F3606EDEA3BEB51825DA2E8D1650@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox