|
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 -----
Sent: Friday, February 18, 2005
1:53 PM
Subject: Re: [SQL] pg primary key
bug?
Hi,
Tom Lane wrote:
pginfo <[EMAIL PROTECTED]> 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
|
