Hi Ann & Vlad,

Thank you for your responses. It would seem we have had a couple misconceptions about temp tables, thanks for clearing that up Vlad.

Further investigation has revealed our problem is solved by either of the following:

1. Firebird 2.5 - The offending proc executes fast and without error, with default config. Noted Firebird 2.1.3 still has the problem.

2. Removing population of a memo (blob) field. (Nothing to do with indexes as far as we can tell.)

As the former option is not immediately possible, I think we will just drop the blob.
I noted the sum octet_length of all these blobs was only ~ 1Mb for this test so I'm not sure how it was causing the error.


The following points still seem odd:


- Your explanations point to the number of indexes we are creating - but we've only got about 10 total new, 2-3 per table, mostly on integer columns for FK refs.

- The error occurs when we are populating the temp tables with insert / update statements - no metadata updates are made here. Yet the error sounds like a failure to add or alter an index.


The issue is "solved" for the time being, but additional ideas would be welcomed.

Thanks,

Ken Coffman
Communicare Development Team
Communicare Systems Pty Ltd
Tel: 08 6212 6900 Fax: 08 6212 6980
ken.coffman@...<mailto:ken.coffman@...>
www.communicaresystems.com.au<http://www.communicaresystems.com.au/>

Communicare Systems advises that this email account is not continuously monitored. Please forward any Helpdesk enquiries to our monitored address helpdesk@...<mailto:helpdesk@...> and note that urgent matters must be advised by telephone. This message, and any attachments, are intended only for the use of the individual or entity to which it is addressed and may contain information that is confidential. Any unauthorised use, disclosure, reproduction or distribution of this message and its attachments is prohibited. This message is forwarded from an individual and does not necessarily represent the views of Communicare Systems. If you have received this message in error kindly notify the sender and then delete the contents.

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent: Friday, 25 February 2011 11:50 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] "index root page is full" error populating temp table



On Thu, Feb 24, 2011 at 9:46 PM, Ken Coffman <
ken.coffman@...<mailto:ken.coffman%40communicaresystems.com.au>> wrote:

>
> DB Page Size:
>
> 4096
>
> Unfortunately with larger customer databases we have found the first stored
> proc fails after a minute with this error:
>
> ISC ERROR MESSAGE:
> unsuccessful metadata update
> cannot add index, index root page is full.
>
> The point at which this occurs seems to vary between test platforms.
>

The solution is to increase your page size.

Firebird has a long standing limit that the definitions of all indexes for a

table must fit on a single database page. The actual limit on the number
of indexes depends not just on the page size, but also on teh complexity
of the index definitions.

The limit goes back to 1984 (Orwellian) and increases in the maximum
allowed page size have sort of hidden it. When I asked about adding more
index root pages to tables in the late 80's, the response was tha no one
should ever need more indexes than can be described in 4000 bytes.

Now the limit is closer to 16,000 bytes.

Good luck,

Ann

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]