Re: pg primary key bug? - Mailing list pgsql-sql

Hi Tom,<br /><br /> Tom Lane wrote:<br /><blockquote cite="mid3293.1109026294@sss.pgh.pa.us" type="cite"><pre
wrap="">pginfo<a class="moz-txt-link-rfc2396E"
href="mailto:pginfo@t1.unisoftbg.com"><pginfo@t1.unisoftbg.com></a>writes: </pre><blockquote type="cite"><pre
wrap="">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.   </pre></blockquote><pre wrap="">
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? </pre></blockquote> No.<br /><blockquote
cite="mid3293.1109026294@sss.pgh.pa.us"type="cite"><pre wrap="">
 
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?</pre></blockquote> In this table we store the last value for the ID of part from other tables.<br /> For
eachtable we have one constant in this table. We are using the table as sequence.<br /> For Example if we nee to insert
thenext record in some table we make:<br /> select constvalue from a_constants_str where constname ='...' for
update;<br/> increase the value and make <br /> update a_constants_str set   constvalue= (new value) where...<br /><br
/>It is not so easy as I described, but in general this is the case.<br /> Al this is connected with replications and
datasyncronisation and so on.<br /><br /><blockquote cite="mid3293.1109026294@sss.pgh.pa.us" type="cite"><pre wrap="">
Doyou do UPDATEs on it?</pre></blockquote> Yes, see the description.<br /><blockquote
cite="mid3293.1109026294@sss.pgh.pa.us"type="cite"><pre wrap="">  DELETEs?</pre></blockquote> No, never.<br
/><blockquotecite="mid3293.1109026294@sss.pgh.pa.us" type="cite"><pre wrap="">  SELECT FOR UPDATE?</pre></blockquote>
Yes.<br/><blockquote cite="mid3293.1109026294@sss.pgh.pa.us" type="cite"><pre wrap="">
 
Do you do VACUUM, or VACUUM FULL, or both?</pre></blockquote> Only vacuum full analyze once per day.<br /> Also once
per4h 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
solutionand stop to make pg_dump.<br /><br /> We have > 200 relative big installs for this ERP and all are using
pg.<br/> For the last 3 y. we have one time data corruption and in this 3 cases problem with pkey's.<br /> All the time
pgis working relative well .<br /><br /> regards,<br /> ivan.<br /><blockquote cite="mid3293.1109026294@sss.pgh.pa.us"
type="cite"><prewrap="">
 
        regards, tom lane

 </pre></blockquote><br />