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@LinxNet.com         | http://www.uk.pgp.net/pgpnet/pks-commands.html
http://jimsun.LinxNet.com    |

Fri,  2 Apr 2004 12:02:22 -0500 (EST)
jseymour@LinxNet.com (Jim Seymour)
Re: Problems Vacuum'ing
On Fri, Apr 02, 2004 at 12:02:22PM -0500, Jim Seymour wrote:

[...]

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

Ok, so the WebObjects app keeps an idle open transaction?  (i.e. it
issues a BEGIN as soon as the previous transaction is finished.) I'm not
sure I read the code correctly -- ISTM it would only matter when you try
to vacuum a shared table, which this is not ...

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)

Fri, 2 Apr 2004 14:28:58 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Problems Vacuum'ing