Re: pg primary key bug?

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(at)t1(dot)unisoftbg(dot)com>
> To: Tom Lane <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: Richard_D_Levine(at)raytheon(dot)com
> <mailto:Richard_D_Levine(at)raytheon(dot)com> ; Michael Glaesemann
> <mailto:grzm(at)myrealbox(dot)com> ; pgsql-sql(at)postgresql(dot)org
> <mailto:pgsql-sql(at)postgresql(dot)org> ; pgsql-sql-owner(at)postgresql(dot)org
> <mailto:pgsql-sql-owner(at)postgresql(dot)org>
> Sent: Friday, February 18, 2005 1:53 PM
> Subject: Re: [SQL] pg primary key bug?
>
> Hi,
>
> Tom Lane wrote:
>
>>pginfo <pginfo(at)t1(dot)unisoftbg(dot)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
>>
>>
>>
>>
>