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 794CA9FA4A8 for ; Wed, 19 Jul 2006 12:51:46 -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 33918-06 for ; Wed, 19 Jul 2006 12:51:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc2-s22.bay0.hotmail.com (bay0-omc2-s22.bay0.hotmail.com [65.54.246.158]) by postgresql.org (Postfix) with ESMTP id 142E39FA47F for ; Wed, 19 Jul 2006 12:51:37 -0300 (ADT) Received: from hotmail.com ([64.4.38.43]) by bay0-omc2-s22.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 19 Jul 2006 08:51:37 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 19 Jul 2006 08:51:36 -0700 Message-ID: Received: from 64.4.38.200 by by116fd.bay116.hotmail.msn.com with HTTP; Wed, 19 Jul 2006 15:51:31 GMT X-Originating-IP: [66.160.12.211] X-Originating-Email: [bitsandbytes88@hotmail.com] X-Sender: bitsandbytes88@hotmail.com In-Reply-To: <4954.1153161810@sss.pgh.pa.us> From: "Mr. Dan" To: tgl@sss.pgh.pa.us Cc: pgsql-admin@postgresql.org Subject: Re: On-line backup Date: Wed, 19 Jul 2006 11:51:31 -0400 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 19 Jul 2006 15:51:36.0678 (UTC) FILETIME=[37632860:01C6AB4B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/158 X-Sequence-Number: 22404 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 >To: "Mr. Dan" >CC: pgsql-admin@postgresql.org >Subject: Re: [ADMIN] On-line backup Date: Mon, 17 Jul 2006 14:43:30 -0400 > >"Mr. Dan" 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