From isabella.ghiurea@nrc-cnrc.gc.ca Wed Jul 4 19:31:15 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 48E18A0FB34 for ; Wed, 4 Jul 2012 16:31:15 -0300 (ADT) Received: from nrccenhts2.nrc.ca ([132.246.15.89] helo=nrccenhts1.nrc.ca) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SmVI8-00038u-Ln for pgsql-admin@postgresql.org; Wed, 04 Jul 2012 19:31:15 +0000 Received: from NRCWSTHCM1.NRC.CA (132.246.216.66) by nrccenhts2.nrc.ca (132.246.15.89) with Microsoft SMTP Server (TLS) id 8.3.245.1; Wed, 4 Jul 2012 15:30:57 -0400 Received: from [132.246.194.15] (132.246.216.202) by WSTMAIL.NRC.CA (132.246.216.66) with Microsoft SMTP Server (TLS) id 8.3.245.1; Wed, 4 Jul 2012 12:30:56 -0700 Message-ID: <4FF499F0.9030601@nrc-cnrc.gc.ca> Date: Wed, 4 Jul 2012 12:30:56 -0700 From: Isabella Ghiurea Reply-To: isabella.ghiurea@nrc-cnrc.gc.ca Organization: CADC -NRC User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:12.0) Gecko/20120430 Thunderbird/12.0.1 MIME-Version: 1.0 To: "pgsql-admin@postgresql.org" Subject: auto vacuum errors Content-Type: multipart/alternative; boundary="------------090705030301060108070703" X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201207/17 X-Sequence-Number: 39734 --------------090705030301060108070703 Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit HI Gurus I'm seeing a issue with some temp tables in contrib_regression db , the auto vacuum job will not run for this tables due some files/QID's missing how can I fix this ? The temp tables are growing too fast and they don't get empty/clean-up...any tips ? I 'm running PGSQL 8.3.6 on RHES. Isabella -- ----------------------------------------------------------- Isabella A. Ghiurea Isabella.Ghiurea@nrc-cnrc.gc.ca Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html National Research Council of Canada, Herzberg Institute of Astrophysics 5071 West Saanich Road, Victoria BC V9E 2E7, Canada Phone: 250 363-3446 fax: 250 363-0045 --------------090705030301060108070703 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit HI Gurus
I'm seeing a issue with some temp tables in contrib_regression db , the auto vacuum job will not run for this tables  due some files/QID's  missing  how can I fix this  ?
The temp tables are growing too fast and  they don't get empty/clean-up...any tips ? I 'm running PGSQL 8.3.6 on RHES.
Isabella

-- 
-----------------------------------------------------------
Isabella A. Ghiurea 

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html 
National Research Council of Canada, Herzberg Institute of Astrophysics 
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045
--------------090705030301060108070703-- From tgl@sss.pgh.pa.us Thu Jul 5 04:18:18 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 92761E7DA88 for ; Thu, 5 Jul 2012 01:18:18 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SmdWD-00049f-Ko for pgsql-admin@postgresql.org; Thu, 05 Jul 2012 04:18:18 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q654I4KP009817; Thu, 5 Jul 2012 00:18:04 -0400 (EDT) From: Tom Lane To: isabella.ghiurea@nrc-cnrc.gc.ca cc: "pgsql-admin@postgresql.org" Subject: Re: auto vacuum errors In-reply-to: <4FF499F0.9030601@nrc-cnrc.gc.ca> References: <4FF499F0.9030601@nrc-cnrc.gc.ca> Comments: In-reply-to Isabella Ghiurea message dated "Wed, 04 Jul 2012 12:30:56 -0700" Date: Thu, 05 Jul 2012 00:18:03 -0400 Message-ID: <9816.1341461883@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/18 X-Sequence-Number: 39735 Isabella Ghiurea writes: > I'm seeing a issue with some temp tables in contrib_regression db , the > auto vacuum job will not run for this tables due some files/QID's > missing how can I fix this ? Um ... in general, autovacuum just silently ignores temp tables, because it doesn't have any way to access their contents. (One of the reasons that temp tables are fast is that the backend owning a temp table keeps its data in local buffers, not in shared buffers that another process could access.) I wouldn't expect any "missing file" complaints though. What exactly were you seeing? regards, tom lane From isabella.ghiurea@nrc-cnrc.gc.ca Thu Jul 5 14:55:59 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D739A1AC5F02 for ; Thu, 5 Jul 2012 11:55:59 -0300 (ADT) Received: from nrccenhts1.nrc.ca ([132.246.15.88]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SmnTI-0004m5-M0 for pgsql-admin@postgresql.org; Thu, 05 Jul 2012 14:55:58 +0000 Received: from NRCWSTHCM1.NRC.CA (132.246.216.66) by nrccenhts1.nrc.ca (132.246.15.88) with Microsoft SMTP Server (TLS) id 8.3.245.1; Thu, 5 Jul 2012 10:55:41 -0400 Received: from [132.246.194.15] (132.246.216.202) by WSTMAIL.NRC.CA (132.246.216.66) with Microsoft SMTP Server (TLS) id 8.3.245.1; Thu, 5 Jul 2012 07:55:41 -0700 Message-ID: <4FF5AAEB.6040103@nrc-cnrc.gc.ca> Date: Thu, 5 Jul 2012 07:55:39 -0700 From: Isabella Ghiurea Reply-To: isabella.ghiurea@nrc-cnrc.gc.ca Organization: CADC -NRC User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:12.0) Gecko/20120430 Thunderbird/12.0.1 MIME-Version: 1.0 To: Tom Lane CC: "pgsql-admin@postgresql.org" Subject: Re: auto vacuum errors References: <4FF499F0.9030601@nrc-cnrc.gc.ca> <9816.1341461883@sss.pgh.pa.us> In-Reply-To: <9816.1341461883@sss.pgh.pa.us> Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201207/22 X-Sequence-Number: 39739 Hi Tom , Here is my error , the missing files were removed by mistake , I have another similar db cfg with this tables:spherrtmp% and no issues. I 'm thinking to restore this tables content from this second db , will this eliminate the auto vacuum error ? 07:45:59.770 PDT,,,31112,,4ff5a8a7.7988,5,,2012-07-05 07:45:59 PDT,19/2479,0,ERROR,58P01,"could not open relation 26384/247389/248165: No such file or directory",,,,,"automatic vacuum of table ""contrib_regression.public.spheretmp5""",,, 2012-07-05 07:45:59.770 PDT,,,31112,,4ff5a8a7.7988,6,,2012-07-05 07:45:59 PDT,19/2481,0,ERROR,58P01,"could not open relation 26384/247389/248174: No such file or directory",,,,,"automatic vacuum of table ""contrib_regression.public.spheretmp6""",,, On 07/04/2012 09:18 PM, Tom Lane wrote: > Isabella Ghiurea writes: >> I'm seeing a issue with some temp tables in contrib_regression db , the >> auto vacuum job will not run for this tables due some files/QID's >> missing how can I fix this ? > Um ... in general, autovacuum just silently ignores temp tables, because > it doesn't have any way to access their contents. (One of the reasons > that temp tables are fast is that the backend owning a temp table keeps > its data in local buffers, not in shared buffers that another process > could access.) I wouldn't expect any "missing file" complaints though. > What exactly were you seeing? > > regards, tom lane -- ----------------------------------------------------------- Isabella A. Ghiurea Isabella.Ghiurea@nrc-cnrc.gc.ca Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html National Research Council of Canada, Herzberg Institute of Astrophysics 5071 West Saanich Road, Victoria BC V9E 2E7, Canada Phone: 250 363-3446 fax: 250 363-0045 From tgl@sss.pgh.pa.us Thu Jul 5 16:03:36 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 297F392B8E6 for ; Thu, 5 Jul 2012 13:03:36 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SmoWk-0006oa-Fg for pgsql-admin@postgresql.org; Thu, 05 Jul 2012 16:03:35 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q65G3L5f026923; Thu, 5 Jul 2012 12:03:21 -0400 (EDT) From: Tom Lane To: isabella.ghiurea@nrc-cnrc.gc.ca cc: "pgsql-admin@postgresql.org" Subject: Re: auto vacuum errors In-reply-to: <4FF5AAEB.6040103@nrc-cnrc.gc.ca> References: <4FF499F0.9030601@nrc-cnrc.gc.ca> <9816.1341461883@sss.pgh.pa.us> <4FF5AAEB.6040103@nrc-cnrc.gc.ca> Comments: In-reply-to Isabella Ghiurea message dated "Thu, 05 Jul 2012 07:55:39 -0700" Date: Thu, 05 Jul 2012 12:03:21 -0400 Message-ID: <26922.1341504201@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/23 X-Sequence-Number: 39740 Isabella Ghiurea writes: > Here is my error , the missing files were removed by mistake , I have > another similar db cfg with this tables:spherrtmp% and no issues. > I 'm thinking to restore this tables content from this second db , > will this eliminate the auto vacuum error ? What you ought to do is drop the tables that are reported as causing the error. Or the whole database --- are you actually doing something useful in contrib_regression, or is that just left over from testing? regards, tom lane