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 CB5D98B9DFA for ; Tue, 22 Feb 2005 06:20:06 +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 28018-03 for ; Tue, 22 Feb 2005 06:20:01 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 07CF48B9D21 for ; Tue, 22 Feb 2005 06:19:59 +0000 (GMT) Received: (qmail 90944 invoked by uid 1011); 22 Feb 2005 06:24:37 -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 20.195063 secs); 22 Feb 2005 06:24:37 -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 20.195063 secs Process 90932) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 22 Feb 2005 06:24:16 -0000 Message-ID: <421AC251.7060806@t1.unisoftbg.com> Date: Tue, 22 Feb 2005 06:25:37 +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> Content-Type: multipart/alternative; boundary="------------070702070606060006040602" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.07 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/231 X-Sequence-Number: 20342 --------------070702070606060006040602 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi Tom, Tom Lane wrote: >pginfo writes: > > >>sorry, but we have the case number 3 in with the same problem. >>Also this time we do not find any linux box crash nor pg stop or restart. >> >> > >Hmm, well there's *something* mighty curious about the history of this >table. The xmin values span a range of almost 400,000 transactions and >yet the oids are twenty-three consecutive values. Is this the only >table you've inserted into during the last 400K transactions? > > No. >It's also odd that there's so much empty space (only 37 rows in 60 >pages). It's hard to see how that could be unless there were many >updates on the table, but judging from the name and contents of the >table I can hardly see a reason for lots of updates. How is this table >used exactly? > In this table we store the last value for the ID of part from other tables. For each table we have one constant in this table. We are using the table as sequence. For Example if we nee to insert the next record in some table we make: select constvalue from a_constants_str where constname ='...' for update; increase the value and make update a_constants_str set constvalue= (new value) where... It is not so easy as I described, but in general this is the case. Al this is connected with replications and data syncronisation and so on. > Do you do UPDATEs on it? > Yes, see the description. > DELETEs? > No, never. > SELECT FOR UPDATE? > Yes. >Do you do VACUUM, or VACUUM FULL, or both? > Only vacuum full analyze once per day. Also once per 4h pg_dump (for pg 7.x we do not have any chance to restore data without full dump. With 8.0 we will test the ne solution and stop to make pg_dump. We have > 200 relative big installs for this ERP and all are using pg. For the last 3 y. we have one time data corruption and in this 3 cases problem with pkey's. All the time pg is working relative well . regards, ivan. > > regards, tom lane > > > > --------------070702070606060006040602 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Tom,

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> writes:
  
sorry, but we have the case number 3 in with the same problem.
Also this time we do not find any linux box crash nor pg stop or restart.
    

Hmm, well there's *something* mighty curious about the history of this
table.  The xmin values span a range of almost 400,000 transactions and
yet the oids are twenty-three consecutive values.  Is this the only
table you've inserted into during the last 400K transactions?
No.
It's also odd that there's so much empty space (only 37 rows in 60
pages).  It's hard to see how that could be unless there were many
updates on the table, but judging from the name and contents of the
table I can hardly see a reason for lots of updates.  How is this table
used exactly?
In this table we store the last value for the ID of part from other tables.
For each table we have one constant in this table. We are using the table as sequence.
For Example if we nee to insert the next record in some table we make:
select constvalue from a_constants_str where constname ='...' for update;
increase the value and make
update a_constants_str set   constvalue= (new value) where...

It is not so easy as I described, but in general this is the case.
Al this is connected with replications and data syncronisation and so on.

  Do you do UPDATEs on it?
Yes, see the description.
  DELETEs?
No, never.
  SELECT FOR UPDATE?
Yes.
Do you do VACUUM, or VACUUM FULL, or both?
Only vacuum full analyze once per day.
Also once per 4h pg_dump (for pg 7.x we do not have any chance to restore data without full dump. With 8.0 we will test the ne solution and stop to make pg_dump.

We have > 200 relative big installs for this ERP and all are using pg.
For the last 3 y. we have one time data corruption and in this 3 cases problem with pkey's.
All the time pg is working relative well .

regards,
ivan.

			regards, tom lane

--------------070702070606060006040602--