X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A67C18B9CE7 for ; Tue, 22 Feb 2005 18:04:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51419-02 for ; Tue, 22 Feb 2005 18:04:10 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 882388B9EC4 for ; Tue, 22 Feb 2005 18:04:09 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1MI48ZV010957; Tue, 22 Feb 2005 13:04:09 -0500 (EST) To: pginfo Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann , pgsql-sql@postgresql.org Subject: Re: pg primary key bug? In-reply-to: <421B6113.4080706@t1.unisoftbg.com> References: <4214B466.20908@t1.unisoftbg.com> <8125.1108661189@sss.pgh.pa.us> <4214C94F.2050806@t1.unisoftbg.com> <9340.1108664352@sss.pgh.pa.us> <4214D5C3.4070107@t1.unisoftbg.com> <9785.1108666900@sss.pgh.pa.us> <421992FE.3030108@t1.unisoftbg.com> <3293.1109026294@sss.pgh.pa.us> <421AC251.7060806@t1.unisoftbg.com> <7012.1109061770@sss.pgh.pa.us> <421AFC7F.406@t1.unisoftbg.com> <10243.1109090250@sss.pgh.pa.us> <421B6113.4080706@t1.unisoftbg.com> Comments: In-reply-to pginfo message dated "Tue, 22 Feb 2005 17:42:59 +0100" Date: Tue, 22 Feb 2005 13:04:08 -0500 Message-ID: <10956.1109095448@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/245 X-Sequence-Number: 20356 pginfo writes: > I will to ask if it will be possible to start some querys (I do not know > the query) exactly before running vacuum full and to save the results in > some log file. If it is possible, we will be able to post the results to > the list in case of ne problem and to have some start point for > reproducing the problem. Well, you should definitely turn on log_statement across the whole installation so that you have a complete record of all SQL commands being issued. Make sure the log includes timestamps and PIDs. I would suggest adding a simple probe for duplicate records to the vacuum script. Maybe something like set enable_indexscan to off; select constname,fid,count(*) from a_constants_str group by constname,fid having count(*) > 1; (The indexscan off bit is just paranoia --- I think that an indexscan might mask the presence of multiple copies of what's supposedly a unique key.) Do this just before and just after the vacuum full command. That will at least nail down whether vacuum full is creating the dups, and once we see it happen the trace of the day's SQL commands may give some ideas where to look. regards, tom lane