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 0DCC38B9EC3 for ; Tue, 22 Feb 2005 17:37:33 +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 42742-03 for ; Tue, 22 Feb 2005 17:37:30 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 073F38B9EA9 for ; Tue, 22 Feb 2005 17:37:24 +0000 (GMT) Received: (qmail 99038 invoked by uid 1011); 22 Feb 2005 17:42:09 -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.193084 secs); 22 Feb 2005 17:42:09 -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.193084 secs Process 99028) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 22 Feb 2005 17:41:47 -0000 Message-ID: <421B6113.4080706@t1.unisoftbg.com> Date: Tue, 22 Feb 2005 17:42:59 +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> Content-Type: multipart/alternative; boundary="------------010002060307010701030407" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.069 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL X-Spam-Level: ** X-Archive-Number: 200502/244 X-Sequence-Number: 20355 --------------010002060307010701030407 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >pginfo writes: > > >>You idea was that we have "vacuum full" + update or select for update in >>the same time. >>I think it is not the case, because we start vacuum full at 1:00 AM and >>no one is working in this time. >> >> > >Hmm. AFAICT the duplicate row copies could only be produced by vacuum >full, so that's certainly part of the issue. But if vacuum full in >isolation were broken, we'd surely know it; so there must be some other >contributing factor involved that your setup is exercising but other >people are (mostly) not doing. > >I agree with the plan to use plain vacuum for awhile and see if that >makes the problem go away. I think it would have to, but maybe I'm >all wet about that. > > Ok, we can still using vacuum full on some installs (with risk to make problems to customes). 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. My idea is some one more familiar with pg to send this querys (if it exists) and we will install it in vacuum scripts. >In the meantime I would suggest seeing if you can distill your >application down into a test case that other people can run to reproduce >the problem. It doesn't matter if the test doesn't make the bug happen >very often, but we have to see the problem happening before we have much >hope of fixing it. > > > >>Will vacuum full generate this problem if we have locked table in this >>time? (It is possible to have locked table in theory) >> >> > >No, that's hardly likely. vacuum full deals with locks all the time. > > > >>Can you describe more detailed the idea of problem with "vacuum full" + >>"update" and can some one make patch if this problem exists in theory >>(if I understand you right)? >> >> > >I have no idea what the actual failure mechanism might be. > > regards, tom lane > > regards, ivan. > > > --------------010002060307010701030407 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> writes:
  
You idea was that we have "vacuum full" + update or select for update in 
the same time.
I think it is not the case, because we start vacuum full at 1:00 AM and 
no one is working in this time.
    

Hmm.  AFAICT the duplicate row copies could only be produced by vacuum
full, so that's certainly part of the issue.  But if vacuum full in
isolation were broken, we'd surely know it; so there must be some other
contributing factor involved that your setup is exercising but other
people are (mostly) not doing.

I agree with the plan to use plain vacuum for awhile and see if that
makes the problem go away.  I think it would have to, but maybe I'm
all wet about that.
Ok, we can still using vacuum full on some installs (with risk to make problems to customes).
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. My idea is some one more familiar with pg to send this querys (if it exists) and we will install it in vacuum scripts.

In the meantime I would suggest seeing if you can distill your
application down into a test case that other people can run to reproduce
the problem.  It doesn't matter if the test doesn't make the bug happen
very often, but we have to see the problem happening before we have much
hope of fixing it.

  
Will vacuum full generate this problem if we have locked table in this 
time? (It is possible to have locked table in theory)
    

No, that's hardly likely.  vacuum full deals with locks all the time.

  
Can you describe more detailed the idea of  problem with "vacuum full" + 
"update" and can some one make patch if this problem exists in theory 
(if I  understand you right)?
    

I have no idea what the actual failure mechanism might be.

			regards, tom lane
regards,
ivan.

--------------010002060307010701030407--