From Kevin.Johnson@noaa.gov Fri Feb 19 14:33:48 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8C2DE6336D1 for ; Fri, 19 Feb 2010 10:33:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30901-09 for ; Fri, 19 Feb 2010 14:33:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mmp3.nems.noaa.gov (mmp3.nems.noaa.gov [140.90.121.158]) by mail.postgresql.org (Postfix) with ESMTP id D8991632550 for ; Fri, 19 Feb 2010 10:33:37 -0400 (AST) Received: from [192.168.22.18] (ip68-105-187-85.dc.dc.cox.net [68.105.187.85]) by mmp3.nems.noaa.gov (Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005)) with ESMTPSA id <0KY300HU7F3ZDK10@mmp3.nems.noaa.gov> for pgsql-admin@postgresql.org; Fri, 19 Feb 2010 09:33:36 -0500 (EST) Date: Fri, 19 Feb 2010 09:33:42 -0500 From: Kevin Johnson Subject: pg_subtrans To: pgsql-admin@postgresql.org Message-id: <4B7EA146.5060501@noaa.gov> MIME-version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_2Adr+XMvOwjo5jYUU2Kv8Q)" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/147 X-Sequence-Number: 33750 This is a multi-part message in MIME format. --Boundary_(ID_2Adr+XMvOwjo5jYUU2Kv8Q) Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT Hello, I am attempting to get information on the pg_subtrans directory. I found this morning that one had a large number of files queued in it. It filled the disk partition on which pgdata resides. After moving, linking and restarting as a temporary solution, the engine properly worked off these files. So, I'm trying now to get to the root cause of why it filled in the first place -- however I'm not sure when postgres engine uses this directory. I've searched the documentation and mailing list archive, but can't find much. Can anyone give me a link or some more information? Thank you in advance for your time! Kevin -- --Boundary_(ID_2Adr+XMvOwjo5jYUU2Kv8Q) Content-type: text/html; charset=ISO-8859-1 Content-transfer-encoding: 7BIT Hello,

I am attempting to get information on the pg_subtrans directory. I found this morning that one had a large number of files queued in it. It filled the disk partition on which pgdata resides. After moving, linking and restarting as a temporary solution, the engine properly worked off these files. So, I'm trying now to get to the root cause of why it filled in the first place -- however I'm not sure when postgres engine uses this directory. I've searched the documentation and mailing list archive, but can't find much. Can anyone give me a link or some more information?

Thank you in advance for your time!

Kevin

--Boundary_(ID_2Adr+XMvOwjo5jYUU2Kv8Q)-- From tgl@sss.pgh.pa.us Fri Feb 19 14:40:26 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 482CA63396E for ; Fri, 19 Feb 2010 10:40:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32108-10 for ; Fri, 19 Feb 2010 14:40:15 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id B57B3633782 for ; Fri, 19 Feb 2010 10:40:15 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o1JEeCZg002641; Fri, 19 Feb 2010 09:40:12 -0500 (EST) To: Kevin Johnson cc: pgsql-admin@postgresql.org Subject: Re: pg_subtrans In-reply-to: <4B7EA146.5060501@noaa.gov> References: <4B7EA146.5060501@noaa.gov> Comments: In-reply-to Kevin Johnson message dated "Fri, 19 Feb 2010 09:33:42 -0500" Date: Fri, 19 Feb 2010 09:40:12 -0500 Message-ID: <2640.1266590412@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 201002/148 X-Sequence-Number: 33751 Kevin Johnson writes: > I am attempting to get information on the pg_subtrans directory. I > found this morning that one had a large number of files queued in it. > It filled the disk partition on which pgdata resides. After moving, > linking and restarting as a temporary solution, the engine properly > worked off these files. So, I'm trying now to get to the root cause of > why it filled in the first place -- however I'm not sure when postgres > engine uses this directory. I've searched the documentation and mailing > list archive, but can't find much. Can anyone give me a link or some > more information? I don't recall the exact horizon for pg_subtrans offhand, but I'm pretty sure that it's determined by your oldest open transaction. Look around for idle-in-transaction sessions. regards, tom lane