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 D21248B9F8B; Fri, 18 Feb 2005 06:31:48 +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 88553-09; Fri, 18 Feb 2005 06:31:45 +0000 (GMT) Received: from millenium.mst.co.jp (millenium.mst.co.jp [210.251.240.193]) by svr1.postgresql.org (Postfix) with ESMTP id 9D37F8B9B45; Fri, 18 Feb 2005 06:31:43 +0000 (GMT) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id j1I6UtQ01574; Fri, 18 Feb 2005 15:30:55 +0900 Message-ID: <00f001c51583$f19f86b0$7201a8c0@mst1x5r347kymb> From: "Iain" To: "pginfo" Cc: , 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> <421574D8.9090000@t1.unisoftbg.com> Subject: Re: pg primary key bug? Date: Fri, 18 Feb 2005 15:34:43 +0900 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00EC_01C515CF.5E63A790" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.058 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200502/204 X-Sequence-Number: 20315 This is a multi-part message in MIME format. ------=_NextPart_000_00EC_01C515CF.5E63A790 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Hi Ivan, Sorry, I can't remember all you said in earlier posts, but I was wondering, your log file says: > HINT: Rebuild the index with REINDEX. Did you do that, and did it solve the problem? regards Iain ----- Original Message ----- From: pginfo To: Tom Lane Cc: Richard_D_Levine@raytheon.com ; Michael Glaesemann ; pgsql-sql@postgresql.org ; pgsql-sql-owner@postgresql.org Sent: Friday, February 18, 2005 1:53 PM Subject: Re: [SQL] pg primary key bug? Hi, Tom Lane wrote: pginfo writes: Will upgrade to 8.0 solve this type of problems ? 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? About fsync (part from postgresql.conf) : #--------------------------------------------------------------------------- # WRITE AHEAD LOG #--------------------------------------------------------------------------- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds Also part from pg logfile: LOG: statistics collector process (PID 2716) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-11 19:58:26 EET LOG: checkpoint record is at 2/BAC39188 LOG: redo record is at 2/BAC39188; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2221145; next OID: 826607 LOG: database system is ready LOG: recycled transaction log file "00000002000000BA" LOG: recycled transaction log file "00000002000000BB" LOG: recycled transaction log file "00000002000000BC" LOG: recycled transaction log file "00000002000000BD" LOG: recycled transaction log file "00000002000000BE" WARNING: index "a_constants_str_pkey" contains 1449 row versions, but table contains 1422 row versions HINT: Rebuild the index with REINDEX. ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" ERROR: duplicate key violates unique constraint "a_constants_str_pkey" LOG: received smart shutdown request FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command LOG: statistics collector process (PID 2713) exited with exit code 1 LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2005-02-16 08:32:21 EET LOG: checkpoint record is at 2/BFAE09EC LOG: redo record is at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE Note we was informed about the problem on 2005-02-16 and rebooted the box. As I see in log file the last restart was on 2005-02-11 and after it all worked well. The affected table is critical and the most used table in this system and if the insert stop to work stop also the system. I will notice also that in the first case when we found the same problem an this system worked ~80 users. Is it possible pg_dump or reindex table or vacuum analyze to make this problem? We are using it very regular. Will we need to stop using vacuum full at all? regards, ivan. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend ------=_NextPart_000_00EC_01C515CF.5E63A790 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Ivan,
 
Sorry, I can't remember all = you said in=20 earlier posts, but I was wondering, your log file says:
 
> HINT:  Rebuild the index with = REINDEX.
Did you do that, and did it = solve the=20 problem?
 
regards
Iain
----- Original Message ----- =
From:=20 pginfo
Cc: Richard_D_Levine@raytheon.c= om=20 ; Michael=20 Glaesemann ; pgsql-sql@postgresql.org = ; pgsql-sql-owner@postgresql= .org=20
Sent: Friday, February = 18, 2005=20 1:53 PM
Subject: Re: [SQL] pg = primary key=20 bug?

Hi,

Tom Lane wrote:
pginfo "><pginfo@t1.unisoftbg.com><=
/A> writes:
  
Will upgrade to 8.0 solve =
this type of problems ?
    

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?
About fsync (part from postgresql.conf)=20 = :


#-----------------------------------------------------------= ----------------
#=20 WRITE AHEAD=20 = LOG
#-----------------------------------------------------------------= ----------

#=20 - Settings -

#fsync =3D=20 = true           &nb= sp;      =20 # turns forced synchronization on or off
#wal_sync_method =3D=20 fsync        # the default varies = across=20 = platforms:
          = ;            =          =20 # fsync, fdatasync, open_sync, or open_datasync
#wal_buffers =3D=20 = 8            =    =20 # min 4, 8KB each

# - Checkpoints -

#checkpoint_segments = =3D=20 3        # in logfile segments, min = 1, 16MB=20 each
#checkpoint_timeout =3D = 300       # range=20 30-3600, in seconds
#checkpoint_warning =3D=20 30        # 0 is off, in=20 seconds


Also part from pg logfile:

LOG:  = statistics=20 collector process (PID 2716) exited with exit code 1
LOG:  = shutting=20 down
LOG:  database system is shut down
LOG:  could = not create=20 IPv6 socket: Address family not supported by protocol
LOG:  = database=20 system was shut down at 2005-02-11 19:58:26 EET
LOG:  = checkpoint=20 record is at 2/BAC39188
LOG:  redo record is at 2/BAC39188; = undo=20 record is at 0/0; shutdown TRUE
LOG:  next transaction ID: = 2221145;=20 next OID: 826607
LOG:  database system is ready
LOG:  = recycled=20 transaction log file "00000002000000BA"
LOG:  recycled = transaction log=20 file "00000002000000BB"
LOG:  recycled transaction log file=20 "00000002000000BC"
LOG:  recycled transaction log file=20 "00000002000000BD"
LOG:  recycled transaction log file=20 "00000002000000BE"
WARNING:  index "a_constants_str_pkey" = contains=20 1449 row versions, but table contains 1422 row versions
HINT:  = Rebuild=20 the index with REINDEX.
ERROR:  duplicate key violates unique=20 constraint "a_constants_str_pkey"
ERROR:  duplicate key = violates=20 unique constraint "a_constants_str_pkey"
ERROR:  duplicate key = violates unique constraint "a_constants_str_pkey"
ERROR:  = duplicate=20 key violates unique constraint "a_constants_str_pkey"
ERROR: =20 duplicate key violates unique constraint=20 "a_constants_str_pkey"
ERROR:  duplicate key violates unique=20 constraint "a_constants_str_pkey"
ERROR:  duplicate key = violates=20 unique constraint "a_constants_str_pkey"
ERROR:  duplicate key = violates unique constraint "a_constants_str_pkey"
ERROR:  = duplicate=20 key violates unique constraint "a_constants_str_pkey"
ERROR: =20 duplicate key violates unique constraint=20 "a_constants_str_pkey"
ERROR:  duplicate key violates unique=20 constraint "a_constants_str_pkey"
LOG:  received smart = shutdown=20 request
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
FATAL:  terminating connection due to administrator=20 command
LOG:  statistics collector process (PID 2713) exited = with exit=20 code 1
LOG:  shutting down
LOG:  database system is = shut=20 down
LOG:  could not create IPv6 socket: Address family not = supported=20 by protocol
LOG:  database system was shut down at 2005-02-16 = 08:32:21=20 EET
LOG:  checkpoint record is at 2/BFAE09EC
LOG:  = redo record=20 is at 2/BFAE09EC; undo record is at 0/0; shutdown TRUE


Note = we was=20 informed about the problem on  2005-02-16 and rebooted the = box.
As I=20 see in log file the last restart was on 2005-02-11 and after it all = worked=20 well.
The affected table is critical  and the most used table = in this=20 system and if the insert stop to work stop also the system.
I will = notice=20 also that in the first case when we found the same problem an this = system=20 worked ~80 users.

Is it possible pg_dump or reindex table or = vacuum=20 analyze to make this problem?
We are using it very = regular.

Will we=20 need to stop using vacuum full at = all?

regards,
ivan.


			regards, tom lane

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

------=_NextPart_000_00EC_01C515CF.5E63A790--