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
duplicatesthen: 1. if scankey == hikey (i.e.      * - new duplicate item) then insert it here; 2. if scankey < hikey