Thread

1. AW: update on TOAST status'

   Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2000-07-11T14:17:02Z

   >     It appears that brtee indices (at least) can keep  references
   >     to  old  toast  values  that  survive  a  VACUUM! Seems these
   >     references live in nodes actually not  referring  to  a  heap
   >     tuple   any   more,   but   used  during  tree  traversal  in
   >     comparisions.  As if an index tuple delete from a  btree  not
   >     necessarily  causes  the  index  value  to disappear from the
   >     btree completely.  It'll never be returned by an index  scan,
   >     but the value is still there somewhere.
   
   Would it be possible to actually delete those entries during vacuum ?
   I guess that would be an overall win, no ?
   
   Andreas
   
   

2. Re: AW: update on TOAST status'

   Bruce Momjian <pgman@candle.pha.pa.us> — 2000-07-11T14:49:20Z

   [ Charset ISO-8859-1 unsupported, converting... ]
   > 
   > >     It appears that brtee indices (at least) can keep  references
   > >     to  old  toast  values  that  survive  a  VACUUM! Seems these
   > >     references live in nodes actually not  referring  to  a  heap
   > >     tuple   any   more,   but   used  during  tree  traversal  in
   > >     comparisions.  As if an index tuple delete from a  btree  not
   > >     necessarily  causes  the  index  value  to disappear from the
   > >     btree completely.  It'll never be returned by an index  scan,
   > >     but the value is still there somewhere.
   > 
   > Would it be possible to actually delete those entries during vacuum ?
   > I guess that would be an overall win, no ?
   
   Seems that is the only good solution, or somehow link vacuum of TOAST
   tables to index so these TOAST values are not removed.
   
   -- 
     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, Pennsylvania 19026
   
   

3. Re: AW: update on TOAST status'

   Jan Wieck <janwieck@t-online.de> — 2000-07-11T16:49:58Z

   Zeugswetter Andreas SB wrote:
   >
   > >     It appears that brtee indices (at least) can keep  references
   > >     to  old  toast  values  that  survive  a  VACUUM! Seems these
   > >     references live in nodes actually not  referring  to  a  heap
   > >     tuple   any   more,   but   used  during  tree  traversal  in
   > >     comparisions.  As if an index tuple delete from a  btree  not
   > >     necessarily  causes  the  index  value  to disappear from the
   > >     btree completely.  It'll never be returned by an index  scan,
   > >     but the value is still there somewhere.
   >
   > Would it be possible to actually delete those entries during vacuum ?
   > I guess that would be an overall win, no ?
   
       Seems  I explained it a little confusing or am confused by it
       myself.
   
       Either way, VACUUM does DELETE those from  the  indices!  But
       btree  is a Balanced Tree, and ISTM that it sometimes decides
       to keep a deleted node just to have  trees  balanced  and  to
       decide  on it whether to go left or right. An index scan will
       never return those nodes, but exactly at the time btree needs
       to decide left/right, it calls the type specific CMP function
       and that in turn invokes the toast fetch.
   
       A pure btree does not have the need for it, but we're using a
       high  concurrency  optimized  version called nbtree. That one
       seems to do so.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #================================================== JanWieck@Yahoo.com #