From: pginfo <pginfo@t1.unisoftbg.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann <grzm@myrealbox.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
Date: Fri, 18 Feb 2005 05:53:44 +0100
Message-ID: <421574D8.9090000@t1.unisoftbg.com> (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>
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: pginfo@t1.unisoftbg.com, tgl@sss.pgh.pa.us, grzm@myrealbox.com, pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
In-Reply-To: <421574D8.9090000@t1.unisoftbg.com>
* 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