From paul@pnlassociates.com Mon Nov 8 22:58:21 2004 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 99C1E3A40EF for ; Mon, 8 Nov 2004 22:58:21 +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 68169-06 for ; Mon, 8 Nov 2004 22:58:14 +0000 (GMT) Received: from mail712.megamailservers.com (mail712.megamailservers.com [69.49.98.22]) by svr1.postgresql.org (Postfix) with ESMTP id F137F3A4077 for ; Mon, 8 Nov 2004 22:58:16 +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 mail712.megamailservers.com (8.12.10/8.12.9) with ESMTP id iA8MwCZe026908 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Mon, 8 Nov 2004 17:58:15 -0500 Message-ID: <418FFA04.8090106@pnlassociates.com> Date: Mon, 08 Nov 2004 17:58:12 -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: pgsql-admin@postgresql.org Subject: Large transaction problem Content-Type: multipart/alternative; boundary="------------080006040806050508010303" 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/66 X-Sequence-Number: 15451 This is a multi-part message in MIME format. --------------080006040806050508010303 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I'm trying to move data from an Oracle database into postgresql 7.4.3. I have a program that is extracting data and writing it to an sql script as a long series of inserts (20,000 at a time). When I run these scripts using psql, I occassionally get a problem that is reported as ERROR: canceling query due to user request And then it won't clear. If I quit psql and start it again, every sql statement on that database gives this error after 30 seconds (approx) of hanging. The only way to clear this is to restart the postgresql backend and then try vacuuming a couple of times. It eventually clears in this manner. What am I doing wrong? I increased the WAL segments from 3 to 30 but this did not get rid of the problem. Is there a limit on the size of a transaction? Thanks. Paul --------------080006040806050508010303 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I'm trying to move data from an Oracle database into postgresql 7.4.3.  I have a program that is extracting data and writing it to an sql script as a long series of inserts (20,000 at a time).  When I run these scripts using psql, I occassionally get a problem that is reported as

ERROR:  canceling query due to user request

And then it won't clear.  If I quit psql and start it again, every sql statement on that database gives this error after 30 seconds (approx) of hanging.  The only way to clear this is to restart the postgresql backend and then try vacuuming a couple of times.  It eventually clears in this manner.

What am I doing wrong?  I increased the WAL segments from 3 to 30 but this did not get rid of the problem.  Is there a limit on the size of a transaction?

Thanks.

Paul
--------------080006040806050508010303-- From tgl@sss.pgh.pa.us Mon Nov 8 23:19:30 2004 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 A04533A4100 for ; Mon, 8 Nov 2004 23:19:30 +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 75517-06 for ; Mon, 8 Nov 2004 23:19:27 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 329BC3A410A for ; Mon, 8 Nov 2004 23:19:29 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iA8NJMOS011722; Mon, 8 Nov 2004 18:19:22 -0500 (EST) To: "Paul B. Anderson" Cc: pgsql-admin@postgresql.org Subject: Re: Large transaction problem In-reply-to: <418FFA04.8090106@pnlassociates.com> References: <418FFA04.8090106@pnlassociates.com> Comments: In-reply-to "Paul B. Anderson" message dated "Mon, 08 Nov 2004 17:58:12 -0500" Date: Mon, 08 Nov 2004 18:19:21 -0500 Message-ID: <11721.1099955961@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/67 X-Sequence-Number: 15452 "Paul B. Anderson" writes: > I'm trying to move data from an Oracle database into postgresql 7.4.3. > I have a program that is extracting data and writing it to an sql script > as a long series of inserts (20,000 at a time). When I run these > scripts using psql, I occassionally get a problem that is reported as > ERROR: canceling query due to user request > And then it won't clear. If I quit psql and start it again, every sql > statement on that database gives this error after 30 seconds (approx) of > hanging. It sounds to me like you are starting the postmaster under finite ulimit settings --- could be either CPU or filesize limits. regards, tom lane From paul@pnlassociates.com Thu Nov 11 19:50:45 2004 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 158393A434D for ; Thu, 11 Nov 2004 19:50:45 +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 17287-05 for ; Thu, 11 Nov 2004 19:50:34 +0000 (GMT) Received: from mail710.megamailservers.com (mail710.megamailservers.com [69.49.98.20]) by svr1.postgresql.org (Postfix) with ESMTP id B65CA3A4171 for ; Thu, 11 Nov 2004 19:50:32 +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 mail710.megamailservers.com (8.12.10/8.12.9) with ESMTP id iABJoPMj007028 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Thu, 11 Nov 2004 14:50:31 -0500 Message-ID: <4193C282.2040904@pnlassociates.com> Date: Thu, 11 Nov 2004 14:50:26 -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: pgsql-admin@postgresql.org Subject: Re: Large transaction problem Content-Type: multipart/alternative; boundary="------------070603020305080602090307" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200411/98 X-Sequence-Number: 15483 This is a multi-part message in MIME format. --------------070603020305080602090307 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I think I've solved this problem. It involved the vacuumdb command rather than the large insert. I found this out when a nightly vacuumdb script reported the problem even though no records had been loaded at all. 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. I'm guessing postgresql had left some internal state set for the database and was trying to complete the vacuum first. Anyway, I set the statement_timeout = 0 and the problem isn't recurring. Paul "Paul B. Anderson" writes: >> Ulimit says > > >> # su - postgres >> -bash-2.05b$ ulimit -a >> core file size (blocks, -c) 0 >> data seg size (kbytes, -d) unlimited >> file size (blocks, -f) unlimited >> max locked memory (kbytes, -l) 4 >> max memory size (kbytes, -m) unlimited >> open files (-n) 1024 >> pipe size (512 bytes, -p) 8 >> stack size (kbytes, -s) 10240 >> cpu time (seconds, -t) unlimited >> max user processes (-u) 3072 >> virtual memory (kbytes, -v) unlimited > > >> None of the actual limits seems to be a problem. I can't seem to change >> max locked memory using ulimit but I'll try increasing stack size. >> Could that be involved? > > No, the 'file size' and 'cpu time' limits are the ones I'd expect to cause this sort of behavior. One possible gotcha is that depending on how you start the postmaster, it might be living under different ulimit settings than what you see interactively. regards, tom lane --------------070603020305080602090307 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I think I've solved this problem.  It involved the vacuumdb command rather than the large insert.  I found this out when a nightly vacuumdb script reported the problem even though no records had been loaded at all.

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.

I'm guessing postgresql had left some internal state set for the database and was trying to complete the vacuum first.   Anyway, I set the statement_timeout = 0 and the problem isn't recurring.

Paul


"Paul B. Anderson" <paul@pnlassociates.com> writes:
> Ulimit says
  

> # su - postgres
> -bash-2.05b$ ulimit -a
> core file size        (blocks, -c) 0
> data seg size         (kbytes, -d) unlimited
> file size             (blocks, -f) unlimited
> max locked memory     (kbytes, -l) 4
> max memory size       (kbytes, -m) unlimited
> open files                    (-n) 1024
> pipe size          (512 bytes, -p) 8
> stack size            (kbytes, -s) 10240
> cpu time             (seconds, -t) unlimited
> max user processes            (-u) 3072
> virtual memory        (kbytes, -v) unlimited
  

> None of the actual limits seems to be a problem.  I can't seem to change 
> max locked memory using ulimit but I'll try increasing stack size.  
> Could that be involved?
  

No, the 'file size' and 'cpu time' limits are the ones I'd expect to
cause this sort of behavior.

One possible gotcha is that depending on how you start the postmaster,
it might be living under different ulimit settings than what you see
interactively.

			regards, tom lane


--------------070603020305080602090307-- From tgl@sss.pgh.pa.us Thu Nov 11 21:07:52 2004 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 75BA43A44DB for ; Thu, 11 Nov 2004 21:07:52 +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 40931-08 for ; Thu, 11 Nov 2004 21:07:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E79B63A4404 for ; Thu, 11 Nov 2004 21:07:39 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iABL7ZRF011556; Thu, 11 Nov 2004 16:07:35 -0500 (EST) To: "Paul B. Anderson" Cc: pgsql-admin@postgresql.org Subject: Re: Large transaction problem In-reply-to: <4193C282.2040904@pnlassociates.com> References: <4193C282.2040904@pnlassociates.com> Comments: In-reply-to "Paul B. Anderson" message dated "Thu, 11 Nov 2004 14:50:26 -0500" Date: Thu, 11 Nov 2004 16:07:35 -0500 Message-ID: <11555.1100207255@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/99 X-Sequence-Number: 15484 "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 From paul@pnlassociates.com Thu Nov 11 22:14:59 2004 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-- From tgl@sss.pgh.pa.us Thu Nov 11 22:49:05 2004 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 691DF3A3C24 for ; Thu, 11 Nov 2004 22:49:05 +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 76642-09 for ; Thu, 11 Nov 2004 22:48:54 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 71F973A4669 for ; Thu, 11 Nov 2004 22:48:56 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iABMmqIM012505; Thu, 11 Nov 2004 17:48:52 -0500 (EST) To: "Paul B. Anderson" Cc: pgsql-admin@postgresql.org 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> Comments: In-reply-to "Paul B. Anderson" message dated "Thu, 11 Nov 2004 17:14:50 -0500" Date: Thu, 11 Nov 2004 17:48:52 -0500 Message-ID: <12504.1100213332@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/101 X-Sequence-Number: 15486 "Paul B. Anderson" writes: > 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. This all sounds exactly like an overly enthusiastic statement_timeout. What you are doing to "clear the problem" is merely to populate the disk cache sufficiently that your query finishes before timing out. regards, tom lane From ajs@crankycanuck.ca Fri Nov 12 14:34:01 2004 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 7B1023A469E for ; Fri, 12 Nov 2004 14:34:01 +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 16942-09 for ; Fri, 12 Nov 2004 14:33:48 +0000 (GMT) Received: from smtp101.rog.mail.re2.yahoo.com (smtp101.rog.mail.re2.yahoo.com [206.190.36.79]) by svr1.postgresql.org (Postfix) with SMTP id C60B53A4686 for ; Fri, 12 Nov 2004 14:33:50 +0000 (GMT) Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@65.49.125.184 with login) by smtp101.rog.mail.re2.yahoo.com with SMTP; 12 Nov 2004 14:33:51 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id B863F404C; Fri, 12 Nov 2004 09:33:49 -0500 (EST) Date: Fri, 12 Nov 2004 09:33:49 -0500 From: Andrew Sullivan To: pgsql-admin@postgresql.org Subject: Re: Large transaction problem Message-ID: <20041112143349.GA32479@phlogiston.dyndns.org> Mail-Followup-To: pgsql-admin@postgresql.org References: <4193C282.2040904@pnlassociates.com> <11555.1100207255@sss.pgh.pa.us> <4193E45A.7050000@pnlassociates.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4193E45A.7050000@pnlassociates.com> User-Agent: Mutt/1.5.6+20040722i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/103 X-Sequence-Number: 15488 On Thu, Nov 11, 2004 at 05:14:50PM -0500, Paul B. Anderson wrote: > > /usr/bin/vacuumdb --all --full --analyze ^^^^^ You do realise that takes an exclusive lock, right? Why are you doing VACUUM FULL? If your FSM is set right, you really shouldn't need it for most cases. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner From paul@pnlassociates.com Fri Nov 12 15:30:48 2004 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 50A913A46C5 for ; Fri, 12 Nov 2004 15:30:48 +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 35085-06 for ; Fri, 12 Nov 2004 15:30:34 +0000 (GMT) Received: from mail713.megamailservers.com (mail713.megamailservers.com [69.49.98.23]) by svr1.postgresql.org (Postfix) with ESMTP id 699B83A469E for ; Fri, 12 Nov 2004 15:30:32 +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 mail713.megamailservers.com (8.12.10/8.12.9) with ESMTP id iACFUSKK006910 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Fri, 12 Nov 2004 10:30:31 -0500 Message-ID: <4194D713.3000402@pnlassociates.com> Date: Fri, 12 Nov 2004 10:30:27 -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: pgsql-admin@postgresql.org Subject: Re: Large transaction problem References: <4193C282.2040904@pnlassociates.com> <11555.1100207255@sss.pgh.pa.us> <4193E45A.7050000@pnlassociates.com> <20041112143349.GA32479@phlogiston.dyndns.org> In-Reply-To: <20041112143349.GA32479@phlogiston.dyndns.org> Content-Type: multipart/alternative; boundary="------------030207030404060001060208" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200411/104 X-Sequence-Number: 15489 This is a multi-part message in MIME format. --------------030207030404060001060208 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit When the vacuum full timed out, could the exclusive lock have been left set, leaving the database in an unusable state? BTW, thanks. I removed the --full option. Paul Andrew Sullivan wrote: >On Thu, Nov 11, 2004 at 05:14:50PM -0500, Paul B. Anderson wrote: > > >> /usr/bin/vacuumdb --all --full --analyze >> >> > ^^^^^ > >You do realise that takes an exclusive lock, right? Why are you >doing VACUUM FULL? If your FSM is set right, you really shouldn't >need it for most cases. > >A > > > --------------030207030404060001060208 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit When the vacuum full timed out, could the exclusive lock have been left set, leaving the database in an unusable state?

BTW, thanks.  I removed the --full option. 

Paul


Andrew Sullivan wrote:
On Thu, Nov 11, 2004 at 05:14:50PM -0500, Paul B. Anderson wrote:
  
 /usr/bin/vacuumdb --all --full --analyze
    
                            ^^^^^

You do realise that takes an exclusive lock, right?  Why are you
doing VACUUM FULL?  If your FSM is set right, you really shouldn't
need it for most cases.

A

  
--------------030207030404060001060208-- From ajs@crankycanuck.ca Fri Nov 12 15:57:39 2004 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 B24C53A472F for ; Fri, 12 Nov 2004 15:57:39 +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 42534-08 for ; Fri, 12 Nov 2004 15:57:26 +0000 (GMT) Received: from smtp100.rog.mail.re2.yahoo.com (smtp100.rog.mail.re2.yahoo.com [206.190.36.78]) by svr1.postgresql.org (Postfix) with SMTP id B3EE53A46AE for ; Fri, 12 Nov 2004 15:57:29 +0000 (GMT) Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@65.49.125.184 with login) by smtp100.rog.mail.re2.yahoo.com with SMTP; 12 Nov 2004 15:57:29 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 23B03404D; Fri, 12 Nov 2004 10:57:28 -0500 (EST) Date: Fri, 12 Nov 2004 10:57:28 -0500 From: Andrew Sullivan To: pgsql-admin@postgresql.org Subject: Re: Large transaction problem Message-ID: <20041112155728.GB32479@phlogiston.dyndns.org> Mail-Followup-To: pgsql-admin@postgresql.org References: <4193C282.2040904@pnlassociates.com> <11555.1100207255@sss.pgh.pa.us> <4193E45A.7050000@pnlassociates.com> <20041112143349.GA32479@phlogiston.dyndns.org> <4194D713.3000402@pnlassociates.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4194D713.3000402@pnlassociates.com> User-Agent: Mutt/1.5.6+20040722i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/105 X-Sequence-Number: 15490 On Fri, Nov 12, 2004 at 10:30:27AM -0500, Paul B. Anderson wrote: > When the vacuum full timed out, could the exclusive lock have been left > set, leaving the database in an unusable state? No, but cancelling a VACUUM FULL takes a long time, in my experience, and so the lock hangs around for quite a while. > BTW, thanks. I removed the --full option. My bet is that will help matters. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland