Thread

1. Re: Automatic free space map filling

   Zeugswetter Andreas ADI SD <zeugswettera@spardat.at> — 2006-03-03T16:20:29Z

   > > But what about index clearing?  When do you scan each index?
   > 
   > At the end of each iteration (or earlier, depending on 
   > maintenance_work_mem).  So for each iteration you would need 
   > to scan the indexes.
   > 
   > Maybe we could make maintenance_work_mem be the deciding 
   > factor; after scanning the indexes, do the release/reacquire 
   > locks cycle.
   
   But you could do the indexes first and remember how far you can 
   vacuum the heap later.
   
   So you might as well do each index separately first and remember 
   how far you can go with the heap for each one.
   Then do the heap with a special restriction that comes from what you 
   remembered from the indexes.
   You can now separate the heap vacuum in arbitrarily large transactions,
   since the indexes are already taken care of.
   
   (You only vacuum to the point of the eldest vacuumed index)
   
   Andreas
   
   

2. Re: Automatic free space map filling

   Bruce Momjian <pgman@candle.pha.pa.us> — 2006-03-03T16:26:28Z

   Zeugswetter Andreas DCP SD wrote:
   > 
   > > > But what about index clearing?  When do you scan each index?
   > > 
   > > At the end of each iteration (or earlier, depending on 
   > > maintenance_work_mem).  So for each iteration you would need 
   > > to scan the indexes.
   > > 
   > > Maybe we could make maintenance_work_mem be the deciding 
   > > factor; after scanning the indexes, do the release/reacquire 
   > > locks cycle.
   > 
   > But you could do the indexes first and remember how far you can 
   > vacuum the heap later.
   > 
   > So you might as well do each index separately first and remember 
   > how far you can go with the heap for each one.
   > Then do the heap with a special restriction that comes from what you 
   > remembered from the indexes.
   > You can now separate the heap vacuum in arbitrarily large transactions,
   > since the indexes are already taken care of.
   > 
   > (You only vacuum to the point of the eldest vacuumed index)
   
   I thought you need to know the heap tids to remove from the index, so
   how do you do the index first?
   
   -- 
     Bruce Momjian   http://candle.pha.pa.us
     SRA OSS, Inc.   http://www.sraoss.com
   
     + If your life is a hard drive, Christ can be your backup. +
   
   

3. Re: Automatic free space map filling

   Chris Browne <cbbrowne@acm.org> — 2006-03-04T02:53:47Z

   Centuries ago, Nostradamus foresaw when ZeugswetterA@spardat.at ("Zeugswetter Andreas DCP SD") would write:
   >> > But what about index clearing?  When do you scan each index?
   >> 
   >> At the end of each iteration (or earlier, depending on 
   >> maintenance_work_mem).  So for each iteration you would need 
   >> to scan the indexes.
   >> 
   >> Maybe we could make maintenance_work_mem be the deciding 
   >> factor; after scanning the indexes, do the release/reacquire 
   >> locks cycle.
   >
   > But you could do the indexes first and remember how far you can 
   > vacuum the heap later.
   
   But the indexes _can't_ be done first; you _first_ need to know which
   tuples are dead, which requires looking at the table itself.
   -- 
   select 'cbbrowne' || '@' || 'gmail.com';
   http://linuxdatabases.info/info/languages.html
   Pound for pound, the amoeba is the most vicious animal on earth.
   
   

4. Deleting loid from the database

   Md.Abdul Aziz <aziz@students.iiit.net> — 2006-03-04T06:38:52Z

   Hi,
    	I am a presently working on a module which enhances postgre to 
   store audio files,while storing the aduido file in the databese i used 
   liod,now the problem is i am able to unlink but still the data is 
   present in the postgre database.can some one suggest me how to delete (not 
   unlink) large objects from the postgre databse.
   
   Thanking you,
   Md.Abdul Aziz
   
   

5. Re: Deleting loid from the database

   Michael Fuhr <mike@fuhr.org> — 2006-03-04T07:20:47Z

   On Sat, Mar 04, 2006 at 12:08:52PM +0530, Md.Abdul Aziz wrote:
   > 	I am a presently working on a module which enhances postgre to 
   > store audio files,while storing the aduido file in the databese i used 
   > liod,now the problem is i am able to unlink but still the data is 
   > present in the postgre database.can some one suggest me how to delete (not 
   > unlink) large objects from the postgre databse.
   
   "VACUUM FULL pg_largeobject" might be what you're looking for, but
   if you're going to reload the data then an ordinary VACUUM (without
   FULL) will free the space for re-use by PostgreSQL without shrinking
   the file (unless the table has no live tuples, in which case the
   file size will be zeroed).
   
   -- 
   Michael Fuhr
   
   

6. Re: Deleting loid from the database

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-03-05T13:54:02Z

   contrib/vacuumlo perhaps?
   
   Michael Fuhr wrote:
   > On Sat, Mar 04, 2006 at 12:08:52PM +0530, Md.Abdul Aziz wrote:
   >> 	I am a presently working on a module which enhances postgre to 
   >> store audio files,while storing the aduido file in the databese i used 
   >> liod,now the problem is i am able to unlink but still the data is 
   >> present in the postgre database.can some one suggest me how to delete (not 
   >> unlink) large objects from the postgre databse.
   > 
   > "VACUUM FULL pg_largeobject" might be what you're looking for, but
   > if you're going to reload the data then an ordinary VACUUM (without
   > FULL) will free the space for re-use by PostgreSQL without shrinking
   > the file (unless the table has no live tuples, in which case the
   > file size will be zeroed).
   > 
   
   

7. Re: Deleting loid from the database

   Michael Fuhr <mike@fuhr.org> — 2006-03-05T16:30:26Z

   On Sun, Mar 05, 2006 at 09:54:02PM +0800, Christopher Kings-Lynne wrote:
   > contrib/vacuumlo perhaps?
   
   vacuumlo only calls lo_unlink(); the data still exists in pg_largeobject
   due to MVCC.
   
   -- 
   Michael Fuhr