X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 677DC9FA5E9 for ; Mon, 24 Jul 2006 11:46:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 25929-03 for ; Mon, 24 Jul 2006 11:46:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc2-s26.bay0.hotmail.com (bay0-omc2-s26.bay0.hotmail.com [65.54.246.162]) by postgresql.org (Postfix) with ESMTP id C8A0A9FA50D for ; Mon, 24 Jul 2006 11:46:19 -0300 (ADT) Received: from hotmail.com ([64.4.38.46]) by bay0-omc2-s26.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 24 Jul 2006 07:46:17 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 24 Jul 2006 07:46:13 -0700 Message-ID: Received: from 64.4.38.200 by by116fd.bay116.hotmail.msn.com with HTTP; Mon, 24 Jul 2006 14:46:10 GMT X-Originating-IP: [66.160.12.211] X-Originating-Email: [bitsandbytes88@hotmail.com] X-Sender: bitsandbytes88@hotmail.com From: "Mr. Dan" To: pgsql-admin@postgresql.org Subject: btree index - incorrect results Date: Mon, 24 Jul 2006 10:46:10 -0400 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 24 Jul 2006 14:46:13.0455 (UTC) FILETIME=[E90795F0:01C6AF2F] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.907 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: * X-Archive-Number: 200607/190 X-Sequence-Number: 22436 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.