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 018E28B9ECB for ; Tue, 22 Feb 2005 18:14:30 +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 54357-07 for ; Tue, 22 Feb 2005 18:14:25 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id E548C8B9EB6 for ; Tue, 22 Feb 2005 18:14:24 +0000 (GMT) Received: (qmail 99713 invoked by uid 1011); 22 Feb 2005 18:19:11 -0000 Received: from pginfo@t1.unisoftbg.com by unisoft.unisoftbg.com by uid 1009 with qmail-scanner-1.22-st-qms (clamdscan: 0.75. spamassassin: 2.60. Clear:RC:0(192.168.1.201):SA:0(-4.7/5.0):. Processed in 21.928171 secs); 22 Feb 2005 18:19:11 -0000 X-Antivirus-MYDOMAIN-Mail-From: pginfo@t1.unisoftbg.com via unisoft.unisoftbg.com X-Antivirus-MYDOMAIN: 1.22-st-qms (Clear:RC:0(192.168.1.201):SA:0(-4.7/5.0):. Processed in 21.928171 secs Process 99705) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 22 Feb 2005 18:18:48 -0000 Message-ID: <421B69C0.1070404@t1.unisoftbg.com> Date: Tue, 22 Feb 2005 18:20:00 +0100 From: pginfo User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann , pgsql-sql@postgresql.org Subject: Re: pg primary key bug? 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> <10956.1109095448@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------040002020408060306080806" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.289 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL X-Spam-Level: ** X-Archive-Number: 200502/246 X-Sequence-Number: 20357 --------------040002020408060306080806 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >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. > > Ok, it was my idea. We will do it and install the script in ~100 servers and will see the result. > regards, tom lane > > > > regards, ivan. --------------040002020408060306080806 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> 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.
Ok, it was my idea.
We will do it and install the script in ~100 servers and will see the result.
			regards, tom lane
regards,
ivan.
--------------040002020408060306080806--