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 7ACED8B9EE5 for ; Fri, 18 Feb 2005 07:12:40 +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 02658-04 for ; Fri, 18 Feb 2005 07:12:35 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 2EF778B9FB2 for ; Fri, 18 Feb 2005 07:12:33 +0000 (GMT) Received: (qmail 42862 invoked by uid 1011); 18 Feb 2005 07:16:02 -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 16.965087 secs); 18 Feb 2005 07:16:02 -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 16.965087 secs Process 42854) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 18 Feb 2005 07:15:44 -0000 Message-ID: <4215889E.1080804@t1.unisoftbg.com> Date: Fri, 18 Feb 2005 07:18:06 +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: Iain Cc: 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> <4214D5C3.4070107@t1.unisoftbg.com> <9785.1108666900@sss.pgh.pa.us> <421574D8.9090000@t1.unisoftbg.com> <00f001c51583$f19f86b0$7201a8c0@mst1x5r347kymb> Content-Type: multipart/alternative; boundary="------------010500090003090102060202" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.106 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL X-Spam-Level: ** X-Archive-Number: 200502/205 X-Sequence-Number: 20316 --------------010500090003090102060202 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi Iain, Iain wrote: > 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? > No it do not solve the problem. I sendet the log only to show that we do not have any server crash nor pg restart. regards, ivan. > 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 >> >> >> >> > --------------010500090003090102060202 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Iain,

Iain wrote:
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?
 
No it do not  solve the problem.
I sendet the log only to show that we do not have any server crash nor pg restart.

regards,
ivan.
regards
Iain
----- Original Message -----
From: pginfo
Sent: Friday, February 18, 2005 1:53 PM
Subject: Re: [SQL] pg primary key bug?

Hi,

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> 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


--------------010500090003090102060202--