Re: Frequent pg_clog on system

Hi Brian,

Brian A. Seklecki wrote:
> Anything in /var/log/messages at this time? Try turning up the
> debugging for transaction logging?
>
Will do.
FWIW forgot to mention, chance of memory issue is low, memory was
entirely changed a couple of days ago while upgrading from 1GB to 4GB

The first time it appeared, I had 02E6, some 600K inserts and the
pg_clog changed, some queries to identify corrupted data and it changed
again. Server went down apparently while querying on the console.

Nothing special in /var/log/messages but here's some messages from
/var/log/postgresql...

LOG: autovacuum: processing database "template1"
LOG: incomplete startup packet
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: autovacuum: processing database "template1"
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: autovacuum: processing database "template1"
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: autovacuum: processing database "template1"
ERROR: could not access status of transaction 778793573
DETAIL: could not open file "pg_clog/02E6": No such file or directory
LOG: unexpected EOF on client connection
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: autovacuum: processing database "template1"
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: autovacuum: processing database "template1"
LOG: autovacuum: processing database "sandbox"
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
LOG: autovacuum: processing database "template1"
ERROR: could not access status of transaction 1088621376
DETAIL: could not open file "pg_clog/040E": No such file or directory
ERROR: could not access status of transaction 3523034880
DETAIL: could not open file "pg_clog/0D1F": No such file or directory
ERROR: could not access status of transaction 3523034880
DETAIL: could not open file "pg_clog/0D1F": No such file or directory
LOG: autovacuum: processing database "sandbox"
ERROR: could not access status of transaction 3523034880
DETAIL: could not open file "pg_clog/0D1F": No such file or directory
ERROR: could not access status of transaction 3523034880
DETAIL: could not open file "pg_clog/0D1F": No such file or directory
ERROR: could not access status of transaction 3523034880
DETAIL: could not open file "pg_clog/0D1F": No such file or directory
LOG: autovacuum: processing database "postgres"
LOG: autovacuum: processing database "events"
LOG: server process (PID 32045) was terminated by signal 11
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupte
d shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupte
d shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupte
d shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-05-18 02:23:32 UTC
LOG: checkpoint record is at A/D5A9184
LOG: redo record is at A/D5A9184; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 37572637; next OID: 50006
LOG: next MultiXactId: 236697; next MultiXactOffset: 485354
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at A/D5A91C8
LOG: redo is not required
FATAL: the database system is starting up
LOG: database system is ready
LOG: transaction ID wrap limit is 1103367124, limited by database "events"
LOG: server process (PID 32071) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-05-18 14:08:57 UTC
LOG: checkpoint record is at A/D5A91C8
LOG: redo record is at A/D5A91C8; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 37572637; next OID: 50006
LOG: next MultiXactId: 236697; next MultiXactOffset: 485354
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at A/D5A920C

> ~BAS
>
> On Fri, 2007-05-18 at 12:08 +0200, Stephane Bailliez wrote:
>
>> n after a sql query performs a long scan of table A or B.
>> That's very weird since each time it seems to happen after performing
>> more or less the same query several times (trial and error narrowing
>> them data) and suddenly I have this error happening and I'm cooked
>> (fwiw, there is no insert/update happening at all during this period,
>> only select and a single user)
>>
>> Anyone has any idea what could be the cause of this ? It's a bit too
>> recurrent to me and it's a definite annoyance.
>>
>
>
>
>