Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 8A9279FC0DB for ; Fri, 18 May 2007 07:08:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 19795-05 for ; Fri, 18 May 2007 07:08:24 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id D64129FBFCE for ; Fri, 18 May 2007 07:08:24 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so414462ugf for ; Fri, 18 May 2007 03:08:23 -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:to:subject:content-type:content-transfer-encoding; b=buba/N35RUuowYdMzDKoBJEQlt1lq09HUXNTBgfUUOyHiITop23xUVllgPttjqnM/dqJVGk9eg8sUoV7HyDUEGZUspHlxnLlguExlq28B7gggkUDkUQpYXOFu0zXPIPEDy7aoZu2UKO+cL/hh9EyWNXLg+YX/X8Jxrx6qz9+y3k= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding; b=Nc/8k6ECPBLCUvUputbLY6/4NoFcIhXa7uVSWN6KeopkXKZZc+3grt6mnhHpSbgiJ15edpU1h/x8Utpsewoq2v6QlckOVU+/r5zcCo01kkXA9OjEaUjiGBJVFA+46VLDS7nsWHdDKAwtXlN/ihTfhRTJERgBLnHLfWVHmuHD+JA= Received: by 10.66.220.12 with SMTP id s12mr960718ugg.1179482903404; Fri, 18 May 2007 03:08:23 -0700 (PDT) Received: from ?192.168.1.95? ( [89.251.0.64]) by mx.google.com with ESMTP id e23sm2010557ugd.2007.05.18.03.08.22; Fri, 18 May 2007 03:08:22 -0700 (PDT) Message-ID: <464D7B05.4040109@gmail.com> Date: Fri, 18 May 2007 12:08:05 +0200 From: Stephane Bailliez User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Frequent pg_clog on system 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/179 X-Sequence-Number: 25499 Hi all, I'm using postgresql 8.1.8 on Ubuntu 6.10, it's mainly a sandbox/playground machine to crunch data and do some stats analysis. System info: 2.6.17-11-server (SMP) Intel(R) Pentium(R) 4 CPU 3.00GHz 4GB RAM 500GB disk Size of the db is ~7GB with 16 tables, 2 tables A and B holding respectively 4GB (6M rows) and 2GB(3M rows) of data I do at this time about 600K insert / day in one go. In some occasion there is a lot of duplicate inserts (the data I get, is sometimes duplicated and I rely on unique constraint) Since I started to play with it, I got too many times the pg_clog error such as: ERROR: could not access status of transaction 778793573 DETAIL: could not open file "pg_clog/02E6": No such file or directory Each time the pg_clog file is way beyond the existing files I have on my system (in this case, the 'latest file' is 0023) It seems to happen 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. Cheers, -- stephane