From: pginfo <pginfo@t1.unisoftbg.com>
To: Iain <iain@mst.co.jp>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
Date: Fri, 18 Feb 2005 07:18:06 +0100
Message-ID: <4215889E.1080804@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>
<421574D8.9090000@t1.unisoftbg.com>
<00f001c51583$f19f86b0$7201a8c0@mst1x5r347kymb>
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 <mailto:pginfo@t1.unisoftbg.com>
> To: Tom Lane <mailto:tgl@sss.pgh.pa.us>
> Cc: Richard_D_Levine@raytheon.com
> <mailto:Richard_D_Levine@raytheon.com> ; Michael Glaesemann
> <mailto:grzm@myrealbox.com> ; pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org> ; pgsql-sql-owner@postgresql.org
> <mailto: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: pginfo@t1.unisoftbg.com, iain@mst.co.jp, pgsql-sql-owner@postgresql.org
Subject: Re: pg primary key bug?
In-Reply-To: <4215889E.1080804@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