Re: unlink large objects

Hi

I was vacuuming, but as the owner of the database. When I do that there
are messages that should have clued me in, lke

NOTICE: Skipping "pg_largeobject" --- only table owner can VACUUM it

From now on I will vacuum as user postgres, but I wonder if there is a
need for individual users to be able to vacuum large obects by themselves.
I assume that is problimatic since all large objects are in the same file?

Also, when the disk is nearly full, I am seeing the database crash while
trying to vacuum.
timbr pg> vacuumdb scepp
FATAL 2: ZeroFill(/home/postgres/data/pg_xlog/xlogtemp.7922) failed: No
space left on device
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
connection to server was lost
vacuumdb: vacuum scepp failed
timbr pg> df -k
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda5 298663 232362 50881 82% /
/dev/hda1 17534 2482 14147 15% /boot
/dev/hda7 1111176 1039436 15296 99% /home
timbr pg> vacuumdb scepp
psql: connectDBStart() -- connect() failed: Connection refused
Is the postmaster running locally
and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
vacuumdb: vacuum scepp failed

After this I went to another on of our stations that hasn't been running
very long and tried. Now instead of getting smaller the size went up by
50%!!!

myrtl> df -k
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda5 417743 320216 75958 81% /
/dev/hda1 17534 2482 14147 15% /boot
/dev/hda7 1458400 277884 1106432 20% /home
myrtl> su - postgres
Password:
test> vacuumdb scepp
VACUUM
test> df -k
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda5 417743 320216 75958 81% /
/dev/hda1 17534 2482 14147 15% /boot
/dev/hda7 1458400 419660 964656 30% /home

Looking into the db directory, it seems that the space is in the xlog.
When does this get flushed?

test> du -sk *
4 PG_VERSION
153680 base
616 global
8 pg_hba.conf
4 pg_ident.conf
262468 pg_xlog
4 postgresql.conf
4 postmaster.opts
4 postmaster.pid
24 serverlog

I tried to vacuum again, and the space went down to 23% of disk. But still
worse than before vacuuming.
test> vacuumdb scepp
VACUUM
test> df -k
Filesystem 1k-blocks Used Available Use% Mounted on
/dev/hda5 417743 320216 75958 81% /
/dev/hda1 17534 2482 14147 15% /boot
/dev/hda7 1458400 320804 1063512 23% /home

Any suggestions?

thanks for you help,
Philip

On Fri, 8 Jun 2001, Tom Lane wrote:

> Philip Crotwell <crotwell(at)seis(dot)sc(dot)edu> writes:
> > I poked around in the database directory and found a file named 16948 that
> > is 960Mb or almost all of the space on my partition.
>
> That is pg_largeobject. Vacuuming should recover the lost space; are
> you sure you've vacuumed it?
>
> regards, tom lane
>