From: | jseymour(at)LinxNet(dot)com (Jim Seymour) |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Problems Vacuum'ing |
Date: | 2004-04-02 17:02:22 |
Message-ID: | 20040402170222.0C7834307@jimsun.LinxNet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I had written:
>
[snip]
>
> The problem is that attempts to vacuum these tables resulted in "NNN
> dead row versions cannot be removed yet." Went through a lot of
> analysis (e.g.: "Any hanging txns?") and trying different things with
> folks on the #PostgreSQL IRC channel, all to no avail.
[snip]
Okay, the mystery is *partially* solved. In IRC, when this was
brought up again this morning due to my post to -hackers, two things
happened:
I did a vacuumdb on one of the tables and, much to my surprise,
what wouldn't vacuum before I left work last nite *did* work this
morning. And...
JanniCash discovered this in utils/time/tqual.c (I believe):
* OldestXmin is a cutoff XID (obtained from GetOldestXmin()). Tuples
* deleted by XIDs >= OldestXmin are deemed "recently dead"; they might
* still be visible to some open transaction, so we can't remove them,
* even if we see that the deleting transaction has committed.
So the next thing I did was run a bunch of updates, quit the script,
then ran a while loop from the (Unix) command-line, trying to vacuum
the one table every 30 seconds. To summarize...
Fri Apr 2 08:54:54 EST 2004
INFO: "ethers": found 0 removable, 1834 nonremovable row
versions in 93 pages
DETAIL: 1466 dead row versions cannot be removed yet.
...
Fri Apr 2 08:58:56 EST 2004
INFO: "ethers": found 1466 removable, 368 nonremovable row
versions in 93 pages
DETAIL: 0 dead row versions cannot be removed yet.
Which is all well-and-good (tho, my ignorance readily conceded, four
minutes seems a mite... long), *except*: If I shut-down the
WebObjects application which, again, never accesses the db in
question, much-less any of its tables, this "time-out" doesn't seem
to apply. (I tried it.)
Any explanation for this behaviour?
Thanks,
Jim
--
Jim Seymour | PGP Public Key available at:
jseymour(at)LinxNet(dot)com | http://www.uk.pgp.net/pgpnet/pks-commands.html
http://jimsun.LinxNet.com |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2004-04-02 17:21:12 | Re: GiST future |
Previous Message | Joe Conway | 2004-04-02 17:00:44 | Re: Inconsistent behavior on Array & Is Null? |