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

<br /><br /> Tom Lane wrote:<br /><blockquote cite="mid7012.1109061770@sss.pgh.pa.us" type="cite"><pre wrap="">pginfo
<aclass="moz-txt-link-rfc2396E" href="mailto:pginfo@t1.unisoftbg.com"><pginfo@t1.unisoftbg.com></a> writes:
</pre><blockquotetype="cite"><pre wrap="">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...   </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">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.   </pre></blockquote><pre wrap="">
"Connected"?  What exactly is hiding under that last comment? </pre></blockquote> We are using separate table for
sequencesand not sequences from pg direct, because this is built in application method for making <br /> replication
anddata syncro.<br /> I wish only to clarify the reason of using the table and to describe the groud for so many
updatesand select for updates.<br /> Sorry for my bad english ):.<br /><blockquote
cite="mid7012.1109061770@sss.pgh.pa.us"type="cite"><pre wrap="">
 
One way I could take your report is that you've found a weird
interaction between SELECT FOR UPDATE and VACUUM FULL that no one else
has seen before.  Another way is that you're using some nonstandard
backend extension that has nasty bugs in it.

It is interesting that you say this system has been working well for
years and only recently have you seen problems.</pre></blockquote> Yes, exact.<br /><blockquote
cite="mid7012.1109061770@sss.pgh.pa.us"type="cite"><pre wrap="">  To me the obvious
 
question is "what have you changed recently?"</pre></blockquote> If I know !<br /> In general we do not make any global
changesconnected to database access method.<br /> We are using  jdbc (jdbc driver from pg)  + jboss (java based
applicationserver) + connection pool (biult in jboss).<br /> We are using jdbc with Statement.executeBatch(...) and
alsodirect with Statement.executeUpdate(...) .<br /> We are using exact the same ide with oracle without any problem (
oraclehave anoder problems and I prefer pg).<br /><blockquote cite="mid7012.1109061770@sss.pgh.pa.us" type="cite"><pre
wrap=""> It might not be a bogus
 
change in itself, but it could have triggered a bug at lower levels.

It's certainly possible that you have your finger on a backend bug,
but if so there's not nearly enough information here for anyone to
find and fix it.</pre></blockquote> I am sure (not 100%) that it is bug. That is the reason to report the problem.<br
/><br/><blockquote cite="mid7012.1109061770@sss.pgh.pa.us" type="cite"><pre wrap="">  You need to be thinking in terms
ofhow to reproduce
 
the problem so that it can be studied and fixed.</pre></blockquote> You idea was that we have "vacuum full" + update or
selectfor update in the same time.<br /> I think it is not the case, because we start vacuum full at 1:00 AM and no one
isworking in this time.<br /><br /> Will vacuum full generate this problem if we have locked table in this time? (It is
possibleto have locked table in theory)<br /><br /> At this time we do not have info about how to reproduce the
problem.<br/> As the first step we will stop using "vacum full" (if needet we will stop using vacuum analyze too) to
tryto collect more info.<br /><br /><br /><blockquote cite="mid7012.1109061770@sss.pgh.pa.us" type="cite"><pre wrap="">
"How can I avoid this
 
problem" is exactly the wrong question to be asking, because even if
avoiding it is all that concerns you, no one can answer with any
confidence until we understand what the failure mechanism is. </pre></blockquote> Can we set some log parameters to
collectthe needet data?<br /> Can you describe more detailed the idea of  problem with "vacuum full" + "update" and can
someone make patch if this problem exists in theory (if I  understand you right)?<br /> We can start using this patch
andsee if the problem will be again .<br /><br /> If you have anoder Idea we are ready to collect the needet data.<br
/><blockquotecite="mid7012.1109061770@sss.pgh.pa.us" type="cite"><pre wrap="">        regards, tom lane
 

 </pre></blockquote> regards,<br /> ivan.<br />