pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Paul B. Anderson <paul@pnlassociates.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Large transaction problem
Date: Thu, 11 Nov 2004 17:14:50 -0500
Message-ID: <4193E45A.7050000@pnlassociates.com> (raw)
In-Reply-To: <11555.1100207255@sss.pgh.pa.us>
References: <4193C282.2040904@pnlassociates.com>
	<11555.1100207255@sss.pgh.pa.us>

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
>
>.
>
>  
>

view thread (9+ messages)  latest in thread

Message-ID: <4193E45A.7050000@pnlassociates.com>
Permalink:  ../4193E45A.7050000@pnlassociates.com/
Also on:    postgresql.org/message-id/4193E45A.7050000@pnlassociates.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: paul@pnlassociates.com, tgl@sss.pgh.pa.us
  Subject: Re: Large transaction problem
  In-Reply-To: <4193E45A.7050000@pnlassociates.com>
  References: <4193C282.2040904@pnlassociates.com> <11555.1100207255@sss.pgh.pa.us> <4193E45A.7050000@pnlassociates.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox