Re: [HACKERS] Error "vacuum pg_proc"

I wrote:
> Mateus Cordeiro Inssa <mateus(at)ifnet(dot)com(dot)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