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

Hi,<br /><br /> Tom Lane wrote:<br /><blockquote cite="mid9785.1108666900@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="">Willupgrade to 8.0 solve this type of problems ?   </pre></blockquote><pre wrap="">
 
The problem is probably not Postgres' fault at all.  I'm wondering about
disks with write cacheing enabled.  And you didn't turn off fsync,
I trust? </pre></blockquote> About fsync (part from postgresql.conf) :<br /><br /><br />
#---------------------------------------------------------------------------<br/> # WRITE AHEAD LOG<br />
#---------------------------------------------------------------------------<br/><br /> # - Settings -<br /><br />
#fsync= true                   # turns forced synchronization on or off<br /> #wal_sync_method = fsync        # the
defaultvaries across platforms:<br />                                 # fsync, fdatasync, open_sync, or
open_datasync<br/> #wal_buffers = 8                # min 4, 8KB each<br /><br /> # - Checkpoints -<br /><br />
#checkpoint_segments= 3        # in logfile segments, min 1, 16MB each<br /> #checkpoint_timeout = 300       # range
30-3600,in seconds<br /> #checkpoint_warning = 30        # 0 is off, in seconds<br /><br /><br /> Also part from pg
logfile:<br/><br /> LOG:  statistics collector process (PID 2716) exited with exit code 1<br /> LOG:  shutting down<br
/>LOG:  database system is shut down<br /> LOG:  could not create IPv6 socket: Address family not supported by
protocol<br/> LOG:  database system was shut down at 2005-02-11 19:58:26 EET<br /> LOG:  checkpoint record is at
2/BAC39188<br/> LOG:  redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE<br /> LOG:  next transaction
ID:2221145; next OID: 826607<br /> LOG:  database system is ready<br /> LOG:  recycled transaction log file
"00000002000000BA"<br/> LOG:  recycled transaction log file "00000002000000BB"<br /> LOG:  recycled transaction log
file"00000002000000BC"<br /> LOG:  recycled transaction log file "00000002000000BD"<br /> LOG:  recycled transaction
logfile "00000002000000BE"<br /> WARNING:  index "a_constants_str_pkey" contains 1449 row versions, but table contains
1422row versions<br /> HINT:  Rebuild the index with REINDEX.<br /> ERROR:  duplicate key violates unique constraint
"a_constants_str_pkey"<br/> ERROR:  duplicate key violates unique constraint "a_constants_str_pkey"<br /> ERROR: 
duplicatekey violates unique constraint "a_constants_str_pkey"<br /> ERROR:  duplicate key violates unique constraint
"a_constants_str_pkey"<br/> ERROR:  duplicate key violates unique constraint "a_constants_str_pkey"<br /> ERROR: 
duplicatekey violates unique constraint "a_constants_str_pkey"<br /> ERROR:  duplicate key violates unique constraint
"a_constants_str_pkey"<br/> ERROR:  duplicate key violates unique constraint "a_constants_str_pkey"<br /> ERROR: 
duplicatekey violates unique constraint "a_constants_str_pkey"<br /> ERROR:  duplicate key violates unique constraint
"a_constants_str_pkey"<br/> ERROR:  duplicate key violates unique constraint "a_constants_str_pkey"<br /> LOG: 
receivedsmart shutdown request<br /> FATAL:  terminating connection due to administrator command<br /> FATAL: 
terminatingconnection due to administrator command<br /> FATAL:  terminating connection due to administrator command<br
/>FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to administrator
command<br/> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to
administratorcommand<br /> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating
connectiondue to administrator command<br /> FATAL:  terminating connection due to administrator command<br /> FATAL: 
terminatingconnection due to administrator command<br /> FATAL:  terminating connection due to administrator command<br
/>FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to administrator
command<br/> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to
administratorcommand<br /> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating
connectiondue to administrator command<br /> FATAL:  terminating connection due to administrator command<br /> FATAL: 
terminatingconnection due to administrator command<br /> FATAL:  terminating connection due to administrator command<br
/>FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to administrator
command<br/> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating connection due to
administratorcommand<br /> FATAL:  terminating connection due to administrator command<br /> FATAL:  terminating
connectiondue to administrator command<br /> FATAL:  terminating connection due to administrator command<br /> FATAL: 
terminatingconnection due to administrator command<br /> FATAL:  terminating connection due to administrator command<br
/>LOG:  statistics collector process (PID 2713) exited with exit code 1<br /> LOG:  shutting down<br /> LOG:  database
systemis shut down<br /> LOG:  could not create IPv6 socket: Address family not supported by protocol<br /> LOG: 
databasesystem was shut down at 2005-02-16 08:32:21 EET<br /> LOG:  checkpoint record is at 2/BFAE09EC<br /> LOG:  redo
recordis at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE<br /><br /><br /> Note we was informed about the problem
on 2005-02-16 and rebooted the box.<br /> As I see in log file the last restart was on 2005-02-11 and after it all
workedwell.<br /> The affected table is critical  and the most used table in this system and if the insert stop to work
stopalso the system.<br /> I will notice also that in the first case when we found the same problem an this system
worked~80 users. <br /><br /> Is it possible pg_dump or reindex table or vacuum analyze to make this problem?<br /> We
areusing it very regular.<br /><br /> Will we need to stop using vacuum full at all?<br /><br /> regards,<br />
ivan.<br/><br /><br /><blockquote cite="mid9785.1108666900@sss.pgh.pa.us" type="cite"><pre wrap="">        regards, tom
lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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