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 189FE8B9BBF for ; Thu, 17 Feb 2005 18:29:44 +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 98661-04 for ; Thu, 17 Feb 2005 18:29:30 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 618058BA2B9 for ; Thu, 17 Feb 2005 18:29:28 +0000 (GMT) Received: (qmail 36441 invoked by uid 1011); 17 Feb 2005 18:32:48 -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 18.683208 secs); 17 Feb 2005 18:32:48 -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 18.683208 secs Process 36430) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 17 Feb 2005 18:32:28 -0000 Message-ID: <4214D5C3.4070107@t1.unisoftbg.com> Date: Thu, 17 Feb 2005 18:34: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, pgsql-sql-owner@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> Content-Type: multipart/alternative; boundary="------------090204060403040806060505" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.089 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/200 X-Sequence-Number: 20311 --------------090204060403040806060505 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi, Tom Lane wrote: >pginfo writes: > > >>Tom Lane wrote: >> >> >>>Could we see the system columns on these rows? >>> >>> > > > >>01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where >>constname='DOCPLAID'; >> oid | xmin | cmin | xmax | cmax | ctid | constname | fid | >>constvalue >>-------+---------+---------+---------+------+--------+-----------+-----+------------ >> 17916 | 2232893 | 2235861 | 2235861 | 42 | (4,71) | DOCPLAID | 0 | >>SOF_19738 >> 17916 | 2232893 | 2235861 | 2235861 | 41 | (7,62) | DOCPLAID | 0 | >>SOF_19738 >>(2 rows) >> >> > >Given the identical OID and xmin values, it seems certain that these are >the "same" row, ie there was only one insertion event. My bet is that >the one at (7,62) is the original, and that the one at (4,71) is a copy >that was made by VACUUM FULL trying to move the row to compact the >table. So the question is how did both copies get to be marked >simultaneously valid? That should be impossible, unless a disk write >got dropped. Have you had any system crashes during VACUUM FULL >operations recently? > > regards, tom lane > > > > I do not know exact, but it is possible. This is customer server and I do not have any info about server/os crash, but I am 100% sure. I will ask as soon as possible and replay. Also I will check the logs. We start pg witl pg_ctl ...-D... -l /logfile and if we can found the needet info in pg log file I can check it ( I do not nkow what to search in this file). We are running vacuum once per 24 h. The OS is RedHat AS 3. PostgreSQL is 7.4.1 (but we have the same problem on 7.4.5). Will upgrade to 8.0 solve this type of problems ? regards, ivan. --------------090204060403040806060505 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi,

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> writes:
  
Tom Lane wrote:
    
Could we see the system columns on these rows?
      

  
01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where 
constname='DOCPLAID';
  oid  |  xmin   |  cmin   |  xmax   | cmax |  ctid  | constname | fid | 
constvalue
-------+---------+---------+---------+------+--------+-----------+-----+------------
 17916 | 2232893 | 2235861 | 2235861 |   42 | (4,71) | DOCPLAID  |   0 | 
SOF_19738
 17916 | 2232893 | 2235861 | 2235861 |   41 | (7,62) | DOCPLAID  |   0 | 
SOF_19738
(2 rows)
    

Given the identical OID and xmin values, it seems certain that these are
the "same" row, ie there was only one insertion event.  My bet is that
the one at (7,62) is the original, and that the one at (4,71) is a copy
that was made by VACUUM FULL trying to move the row to compact the
table.  So the question is how did both copies get to be marked
simultaneously valid?  That should be impossible, unless a disk write
got dropped.  Have you had any system crashes during VACUUM FULL
operations recently?

			regards, tom lane
I do not know exact, but it is possible.
This is customer server and I do not have any info about server/os crash, but I am 100% sure.
I will ask as soon as possible and replay.
Also I will check the logs.
We start pg witl pg_ctl ...-D... -l  /logfile  and if we can found the needet info in pg log file I can check it ( I do not nkow what to search in this file).
We are running vacuum once per 24 h.
The OS is RedHat AS 3.
PostgreSQL is 7.4.1 (but we have the same problem on 7.4.5).

Will upgrade to 8.0 solve this type of problems ?

regards,
ivan.
--------------090204060403040806060505--