Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 82FC99FB5C0 for ; Fri, 18 May 2007 12:45:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53932-03 for ; Fri, 18 May 2007 12:45:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 8680C9FB545 for ; Fri, 18 May 2007 12:45:33 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so446661ugf for ; Fri, 18 May 2007 08:45:32 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=Ld4W/1yQqymS8TgMwvf/yGofoAxg6DDeYbDJ9AvCBysZE3cC+uqO4KkApdb8Jr2tj5hQn5yQlVmGoot8DfQSCl0mkLNexTKzOaYKDesN4xbrfPdPBXY4/UwhNzpDCoAgs671fD/T8/Zc92yE7YbfRhLmzibNPj+SSKpy/W4ddxY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=I4C1XvtVPjsGOGclAy3w4muYEckQzLI0KFJBVxgO+0xI0l35PXrmEyPOA1r12mLU4VrU/oZyDkmhwSc7EzaUNQbZvyRXfYVjclPVpeVl6Wvn+5DcMvVCHLRX8icjQZZ/jfPOZMvl6cuuLq6sNsRwRAp5ThBuMMqVVgG2Bb6+RfA= Received: by 10.67.94.3 with SMTP id w3mr1115459ugl.1179503132319; Fri, 18 May 2007 08:45:32 -0700 (PDT) Received: from ?192.168.1.95? ( [89.251.0.64]) by mx.google.com with ESMTP id i39sm2520904ugd.2007.05.18.08.45.30; Fri, 18 May 2007 08:45:31 -0700 (PDT) Message-ID: <464DCA08.50303@gmail.com> Date: Fri, 18 May 2007 17:45:12 +0200 From: Stephane Bailliez User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 CC: pgsql-admin@postgresql.org Subject: Re: Frequent pg_clog on system References: <464D7B05.4040109@gmail.com> <1179499248.41472.212.camel@soundwave.pgh.priv.collaborativefusion.com> In-Reply-To: <1179499248.41472.212.camel@soundwave.pgh.priv.collaborativefusion.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/192 X-Sequence-Number: 25512 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. >> > > > >