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@seis.sc.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
>