X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 89E7C3A454B for ; Thu, 11 Nov 2004 22:14:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65284-04 for ; Thu, 11 Nov 2004 22:14:55 +0000 (GMT) Received: from mail711.megamailservers.com (mail711.megamailservers.com [69.49.98.21]) by svr1.postgresql.org (Postfix) with ESMTP id 992013A461B for ; Thu, 11 Nov 2004 22:14:54 +0000 (GMT) X-Authenticated-User: paul.pnlassociates.com Received: from [69.3.2.106] (h-69-3-2-106.mclnva23.covad.net [69.3.2.106]) (authenticated bits=0) by mail711.megamailservers.com (8.12.10/8.12.9) with ESMTP id iABMEoWN009179 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO); Thu, 11 Nov 2004 17:14:53 -0500 Message-ID: <4193E45A.7050000@pnlassociates.com> Date: Thu, 11 Nov 2004 17:14:50 -0500 From: "Paul B. Anderson" User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: Large transaction problem References: <4193C282.2040904@pnlassociates.com> <11555.1100207255@sss.pgh.pa.us> In-Reply-To: <11555.1100207255@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------090703070609020000080104" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200411/100 X-Sequence-Number: 15485 This is a multi-part message in MIME format. --------------090703070609020000080104 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit After I restart postgresql, I execute vacuum several times in sql until it succeeds. Then, everything else works OK again. After I clear the problem, select count(*) from archive takes a second but, before the problem is cleared, it takes about 30 seconds and then gives the canceled by user response. The vacuum was run using the vacuumdb command rather than from psql. It was in a cron script running under user postgres. There was a .pgpass file. The command was /usr/bin/vacuumdb --all --full --analyze This is postgresql on Red Hat Enterprise Linux 3 (ES) from RPMs postgresql-7.4.3-2PGDG, etc. Thanks. Paul Tom Lane wrote: >"Paul B. Anderson" writes: > > >>My postgresql.conf file had a 10 second timeout and the large database >>required more than 10 seconds for the vacuum. It seems that this left >>postmaster and/or the particular database in a state where any SQL >>against that database gave the same error response about being canceled >>by the user. >> >> > >Hmm, I couldn't duplicate this. I thought maybe the vacuum wasn't >releasing some lock after it failed, but there's no sign of such a >problem. > >Are you sure it isn't just that all your queries were running into the >timeout? > > regards, tom lane > >. > > > --------------090703070609020000080104 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit After I restart postgresql, I execute vacuum several times in sql until it succeeds.  Then, everything else works OK again.

After I clear the problem,

  select count(*) from archive

takes a second but, before the problem is cleared, it takes about 30 seconds and then gives the canceled by user response.

The vacuum was run using the vacuumdb command rather than from psql.  It was in a cron script running under user postgres.  There was a .pgpass file.  The command was  

  /usr/bin/vacuumdb --all --full --analyze

This is postgresql on Red Hat Enterprise Linux 3 (ES) from RPMs postgresql-7.4.3-2PGDG, etc.

Thanks.

Paul



Tom Lane wrote:
"Paul B. Anderson" <paul@pnlassociates.com> writes:
  
My postgresql.conf file had a 10 second timeout and the large database 
required more than 10 seconds for the vacuum.  It seems that this left 
postmaster and/or the particular database in a state where any SQL 
against that database gave the same error response about being canceled 
by the user.
    

Hmm, I couldn't duplicate this.  I thought maybe the vacuum wasn't
releasing some lock after it failed, but there's no sign of such a
problem.

Are you sure it isn't just that all your queries were running into the
timeout?

			regards, tom lane

.

  
--------------090703070609020000080104--