Thread: Re: [JDBC] unlink large objects

Philip Crotwell <crotwell@seis.sc.edu> writes:
> 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.

Good point.  More generally, it seems like it might be a good idea to
allow the owner of an individual database to vacuum all the system
catalogs in it, even if he's not the superuser.  Comments anyone?

> Also, when the disk is nearly full, I am seeing the database crash while
> trying to vacuum. 

From WAL log overflow, no doubt.  If you look in the pgsql-patches
archives from the last week or two, there is a patch to alleviate the
growth of the log.
        regards, tom lane

> Philip Crotwell <crotwell@seis.sc.edu> writes:
> > 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.
> 
> Good point.  More generally, it seems like it might be a good idea to
> allow the owner of an individual database to vacuum all the system
> catalogs in it, even if he's not the superuser.  Comments anyone?

Seems db owner should be able to do whatever they want to the non-global
system tables.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

On Sat, 9 Jun 2001, Bruce Momjian wrote:

> > Philip Crotwell <crotwell@seis.sc.edu> writes:
> > > 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.
> > 
> > Good point.  More generally, it seems like it might be a good idea to
> > allow the owner of an individual database to vacuum all the system
> > catalogs in it, even if he's not the superuser.  Comments anyone?
> 
> Seems db owner should be able to do whatever they want to the non-global
> system tables.

I would suppose that this has already been thought of, but it would be
really nice, IMHO, if the "vacuuming" could be done continuously by a low
priority backend thread, like garbabge collection in java. Easier said
then done I am sure, but this would mean that it could be done by the
backend without having to worry about ownership of system tables.

On a similar idea, has there been any thought to allowing regular backend
processess to run at lower priority? Either by adopting the callers
priority if it is local, or by having some way to set a priority. I have
some db cleanup tasks that I would like to run as low level background
tasks, but the backend does all the work at the default top priority.

Just some thoughts,
thanks,
Philip

Philip Crotwell <crotwell@seis.sc.edu> writes:
> On a similar idea, has there been any thought to allowing regular backend
> processess to run at lower priority?

People suggest that from time to time, but it's not an easy thing to do.
The problem is priority inversion: low-priority process acquires a lock,
then some high-priority process starts to run and wants that lock.
Presto, high-priority process is now a low-priority waiter.

Detecting priority inversion situations would be difficult, and doing
anything about them would be even more difficult...
        regards, tom lane