From: Iain <iain@mst.co.jp>
To: pginfo <pginfo@t1.unisoftbg.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
Date: Fri, 18 Feb 2005 15:34:43 +0900
Message-ID: <00f001c51583$f19f86b0$7201a8c0@mst1x5r347kymb> (raw)
References: <OFEF67063C.F61988F3-ON05256F9D.005A91D6@ftw.us.ray.com>
<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>
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 <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp, pginfo@t1.unisoftbg.com, pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
In-Reply-To: <00f001c51583$f19f86b0$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox