  Hi,

  I got this error vacuuming pg_proc:

ERROR:  _bt_endpoint: leftmost page (20) has not leftmost flag

  It's postgresql 6.5.3 (Linux).

  []'s

Mateus Cordeiro Inssa
---------------------
Linux User: 76186  Kernel: 2.3.34
ICQ (Licq): 15243895
---------------------
mateus@ifnet.com.br
mateus@cwb.fnn.net

Fri Dec 24 09:55:14 EDT 1999

Fri, 24 Dec 1999 09:55:15 -0200 (EDT)
Mateus Cordeiro Inssa <mateus@ifnet.com.br>
Error "vacuum pg_proc"
Mateus Cordeiro Inssa <mateus@ifnet.com.br> writes:
>   I got this error vacuuming pg_proc:
> ERROR:  _bt_endpoint: leftmost page (20) has not leftmost flag

Hmm, I wonder if this could be yet another manifestation of the problems
that btree indexes have with oversized key values.  Do you have any
procedures with long definitions?  "Long" in this context means over
about 4K.  If you're not sure, try
	select proname from pg_proc where length(prosrc) > 4000;

If you do, try breaking them up into smaller procedures.  You might have
to dump and rebuild the database to get rid of the corruption in
pg_proc's index, though.

The reason this is an issue is that btree wants to be able to store at
least two index tuples per disk page, so it has problems with indexing
values over half-a-page-less-overhead.  I'm not sure exactly what the
critical size is, but it is somewhere around 4000 bytes.  And pg_proc
has an index on the prosrc field.

The prosrc index is actually completely unnecessary, so we've removed
it for 7.0.  Work is in progress to fix the tuple-size problem as well,
but that will probably take longer.

			regards, tom lane

Fri, 24 Dec 1999 10:52:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Error "vacuum pg_proc" 
I wrote:
> Mateus Cordeiro Inssa <mateus@ifnet.com.br> writes:
>> I got this error vacuuming pg_proc:
>> ERROR:  _bt_endpoint: leftmost page (20) has not leftmost flag

> Hmm, I wonder if this could be yet another manifestation of the problems
> that btree indexes have with oversized key values.  Do you have any
> procedures with long definitions?  "Long" in this context means over
> about 4K.

I spent some more time looking into this, and found out that actually
the safe upper limit for btree index entries is not ~ BLCKSZ/2, but
~ BLCKSZ/3.  btree needs to be able to insert two items on every page,
but it *also* keeps an extra item (the "high key") on non-rightmost
pages.  So if any item exceeds one-third the available space, you run
a risk of failure depending on what page it ends up on and what else
is on that same page.

It turns out that for an index on a single text column, the maximum
safe text length is 2700 bytes.  So the correct check for dangerous
procedure definitions in 6.5.* is

	select proname from pg_proc where length(prosrc) > 2700;

I have committed a check for dangerously long index entries into both
current sources and REL6_5 branch.  The patch is attached if you want to
add it to your installation right away.  Note that this only defends
against oversize new entries; if you've already got oversize index
entries, you still have trouble waiting to happen...

			regards, tom lane

*** src/backend/access/nbtree/nbtinsert.c.orig	Wed Sep  1 13:54:00 1999
--- src/backend/access/nbtree/nbtinsert.c	Sun Dec 26 15:44:15 1999
***************
*** 268,273 ****
--- 268,285 ----
  										 * consistent */
  
  	/*
+ 	 * Check whether the item can fit on a btree page at all.
+ 	 * (Eventually, we ought to try to apply TOAST methods if not.)
+ 	 * We actually need to be able to fit three items on every page,
+ 	 * so restrict any one item to 1/3 the per-page available space.
+ 	 * Note that at this point, itemsz doesn't include the ItemId.
+ 	 */
+ 	if (itemsz > (PageGetPageSize(page)-sizeof(PageHeaderData)-MAXALIGN(sizeof(BTPageOpaqueData)))/3 - sizeof(ItemIdData))
+ 		elog(ERROR, "btree: index item size %d exceeds maximum %d",
+ 			 itemsz,
+ 			 (PageGetPageSize(page)-sizeof(PageHeaderData)-MAXALIGN(sizeof(BTPageOpaqueData)))/3 - sizeof(ItemIdData));
+ 
+ 	/*
  	 * If we have to insert item on the leftmost page which is the first
  	 * page in the chain of duplicates then: 1. if scankey == hikey (i.e.
  	 * - new duplicate item) then insert it here; 2. if scankey < hikey

Sun, 26 Dec 1999 16:20:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Error "vacuum pg_proc" 
> I spent some more time looking into this, and found out that actually
> the safe upper limit for btree index entries is not ~ BLCKSZ/2, but
> ~ BLCKSZ/3.  btree needs to be able to insert two items on every page,
> but it *also* keeps an extra item (the "high key") on non-rightmost
> pages.  So if any item exceeds one-third the available space, you run
> a risk of failure depending on what page it ends up on and what else
> is on that same page.
> 
> It turns out that for an index on a single text column, the maximum
> safe text length is 2700 bytes.  So the correct check for dangerous
> procedure definitions in 6.5.* is

This is another argument to try and get long tuples into 7.0.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Sun, 26 Dec 1999 21:19:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error "vacuum pg_proc"
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> It turns out that for an index on a single text column, the maximum
>> safe text length is 2700 bytes.

> This is another argument to try and get long tuples into 7.0.

I think Jan might have enough on his plate already without trying to
TOAST the index code along with the plain-table code.  But if he can
get it done, great!

One thing this does bring up is that the maximum safe tuple length is
dependent on the index or table type.  The toaster's API had better
be designed accordingly...

			regards, tom lane

Sun, 26 Dec 1999 22:22:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Error "vacuum pg_proc" 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> It turns out that for an index on a single text column, the maximum
> >> safe text length is 2700 bytes.
> 
> > This is another argument to try and get long tuples into 7.0.
> 
> I think Jan might have enough on his plate already without trying to
> TOAST the index code along with the plain-table code.  But if he can
> get it done, great!
> 
> One thing this does bring up is that the maximum safe tuple length is
> dependent on the index or table type.  The toaster's API had better
> be designed accordingly...

In talking to Jan, the index code will make use of the toast entries
automatically.  He said the heap_insert will do any toasting, and after
that the tuple already has any toast pointers, and that gets inserted
into the index.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Sun, 26 Dec 1999 22:36:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error "vacuum pg_proc"
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> One thing this does bring up is that the maximum safe tuple length is
>> dependent on the index or table type.  The toaster's API had better
>> be designed accordingly...

> In talking to Jan, the index code will make use of the toast entries
> automatically.  He said the heap_insert will do any toasting, and after
> that the tuple already has any toast pointers, and that gets inserted
> into the index.

If that's his plan, then it's broken by design.  Toasting a complete
tuple cannot be the basis for toasting index entries related to the
tuple, because (a) the index entries will typically use only some of the
fields appearing in the tuple; (b) index entries have different length
limits than tuples do; (c) indexes might be created after the original
table is.  Heck, index *types* might be created after the original table
is.  If index toasting is dependent on toasting of the main table, the
only way it can work is to toast every varlena attribute down to a
prechosen maximum length that Jan hopes will satisfy every index type,
now or hereafter --- no matter whether the column in question has or
ever will have an index of any type.

And that'll still crash and burn for multicolumn indexes.

I thought the plan was to toast indexes independently of the main
table, ie, an index would have its own toast-table and its own
storage of oversize attributes --- where the *index* decides what
is oversize, not the main table.

If main tables and indexes point at the same toast-table entries,
I think VACUUM will become rather an interesting problem, too...
although maybe that could be worked around if VACUUM destroys indexes
and rebuilds them from scratch.

			regards, tom lane

Sun, 26 Dec 1999 23:14:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Error "vacuum pg_proc" 
I wrote:
> And that'll still crash and burn for multicolumn indexes.

Not to mention functional indexes, which typically store values that
don't appear in the referenced tuple at all.

Basically, indexes have to have their own toasters.  There's no other
way.

			regards, tom lane

Sun, 26 Dec 1999 23:19:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Error "vacuum pg_proc" 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> One thing this does bring up is that the maximum safe tuple length is
> >> dependent on the index or table type.  The toaster's API had better
> >> be designed accordingly...
> 
> > In talking to Jan, the index code will make use of the toast entries
> > automatically.  He said the heap_insert will do any toasting, and after
> > that the tuple already has any toast pointers, and that gets inserted
> > into the index.
> 
> If that's his plan, then it's broken by design.  Toasting a complete
> tuple cannot be the basis for toasting index entries related to the
> tuple, because (a) the index entries will typically use only some of the
> fields appearing in the tuple; (b) index entries have different length
> limits than tuples do; (c) indexes might be created after the original

Yes, I see your point.  This could be quite complicated.  He is
targeting BLKSZ and indexes are BLKSZ/3.  He could toast any single
attribute of size >= BLKSZ/3, but as you mentioned multi-column
indexes would be a problem.  I wonder if he could create the TOAST
entries and modify the heap tuple during index creation?

Wow, this clearly is going to be messy.  Maybe he is going to have to
have a separate TOAST table for the index.  If he got fancy, he could
use the heap TOAST table as needed, and have a secondary index TOAST for
cases the tuple needs further TOASTS for indexes.

It shows we haven't thought through all this yet.




-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 27 Dec 1999 00:05:55 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Error "vacuum pg_proc"
Tom Lane writes:
 > Mateus Cordeiro Inssa <mateus@ifnet.com.br> writes:
 > >   I got this error vacuuming pg_proc:
 > > ERROR:  _bt_endpoint: leftmost page (20) has not leftmost flag
 > 
 > Hmm, I wonder if this could be yet another manifestation of the problems
 > that btree indexes have with oversized key values.  Do you have any
 > procedures with long definitions?  "Long" in this context means over
 > about 4K.  If you're not sure, try
 > 	select proname from pg_proc where length(prosrc) > 4000;

  Yes, I have some functions from 3k to 5k.

 > If you do, try breaking them up into smaller procedures.  You might have
 > to dump and rebuild the database to get rid of the corruption in
 > pg_proc's index, though.

  Ok.

 > The prosrc index is actually completely unnecessary, so we've removed
 > it for 7.0.  Work is in progress to fix the tuple-size problem as well,
 > but that will probably take longer.

  Oh, I would ask why there was this index. I had problems with it
since version 6.4.

  I'd like to suggest the creation of a new command: ALTER FUNCTION. I 
use pltcl to program in the server, so, no need for checking the
function code. The problems with pg_proc always occurred to me when
changing functions: DROP/CREATE. This command would do just an update on
prosrc field (that doesn't have index anymore).

  []'s

Mateus Cordeiro Inssa
---------------------
Linux User: 76186  Kernel: 2.3.34
ICQ (Licq): 15243895
---------------------
mateus@ifnet.com.br
mateus@cwb.fnn.net

Mon Dec 27 11:15:41 EDT 1999
.

Mon, 27 Dec 1999 11:15:43 -0200 (EDT)
Mateus Cordeiro Inssa <mateus@ifnet.com.br>
Re: [HACKERS] Error "vacuum pg_proc" 
Tom Lane wrote:

> I wrote:
> > And that'll still crash and burn for multicolumn indexes.
>
> Not to mention functional indexes, which typically store values that
> don't appear in the referenced tuple at all.
>
> Basically, indexes have to have their own toasters.  There's no other
> way.

     You're right.

     I think it's best to delay index toasting until we have some
     experience with normal, main tuple attribute toasting. It'd be
     nice if the solution had covered huge values to be indexed
     automatically (what it doesn't any more). But I think most
     people can live with a database, that cannot index huge
     values, but is capable to store and retrieve them for now.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #




Mon, 03 Jan 2000 19:25:06 +0100
Jan Wieck <wieck@debis.com>
Index toasting (was: Re: [HACKERS] Error "vacuum pg_proc")
At 07:25 PM 1/3/00 +0100, Jan Wieck wrote:

>     I think it's best to delay index toasting until we have some
>     experience with normal, main tuple attribute toasting. It'd be
>     nice if the solution had covered huge values to be indexed
>     automatically (what it doesn't any more). But I think most
>     people can live with a database, that cannot index huge
>     values, but is capable to store and retrieve them for now.

>From my personal POV, I would certainly agree with this.  The stuff
I'm working on is probably pretty typical, using an integer key to
identify rows which contain large chunks of text, photographs, etc.
I'm perfectly happy not being able to index the big chunks, and
suspect a large percentage of users would feel the same.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 03 Jan 2000 16:51:42 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Index toasting (was: Re: [HACKERS] Error "vacuum pg_proc")