[GENERAL] PostgreSQL's vacuumdb fails to allocate memory for

[Sven Willenberger]

On Wed, 2005-06-29 at 11:21 -0400, Tom Lane wrote:
> Sven Willenberger <sven at dmv.com> writes:
> > ERROR:  out of memory
> > DETAIL:  Failed on request of size 536870910.
> 
> That's a server-side failure ...
> 
> > Again, if I log in as myself and try to run
> > the command vaccumdb -a -z it fails; if I su to root and repeat it works
> > fine. I am trying to narrow this down to a PostgreSQL issue vs FreeBSD
> > issue.
> 
> That's fairly hard to believe, assuming that you are talking to the same
> server in both cases (I have seen trouble reports that turned out to be
> because the complainant was accidentally using two different servers...)
> The ulimit the backend is running under couldn't change depending on
> where the client is su'd to.
> 
> Is it possible that you've got per-user configuration settings that
> affect this, like a different maintenance_mem value for the root user?
> 
> 			regards, tom lane
> 
I have done some more tests and tried to keep the results of vacuumdb
distinct from connecting to the backend (psql -U pgsql ...) and running
vaccum analyze. Apparently the hopping back and forth from both methods
interfered with my original interpretations of what appeared to be
happening. Anyway, here is what I see:
First test psql connection version:
psql then vacuum analyze => works fine whether the current unix user is
root or plain user. (ran this a couple times via new psql connections to
verify).
Then quit psql and move to command line
vacuumdb => whether running as su -l pgsql -c "vacuumdb -a -z" (or
specifying a dbname instead of all) or directly as a user the out of
memory error occurs.
If I then connect via psql to the backend and try to run vacuum analyze
I receive an out of memory error.

This last connection to psql after a failed vacuumdb was confabulating
my interpretations earlier of the error being based on unix user. top
shows:
  PID USERNAME  PRI NICE   SIZE    RES STATE  C   TIME   WCPU    CPU
COMMAND
 6754 pgsql       4    0   602M 88688K sbwait 0   0:03  0.00%  0.00%
postgres
until I disconnect the psql session. I can then psql again and the same
error happens (out of memory) and top shows the same again. At this
point I am not sure if it is a memory issue of vacuumdb, vacuum itself,
or the FreeBSD memory management system. Again, if enough time passes
(or some other events) since I last try vacuumdb, then running vacuum
[verbose][analyze] via a psql connection works fine.

Sven