Branimir Trumbic wrote:

>> It is possible to retrieve the PageSize and AllocatedPages. I don't
>> know how, but if you run IB_SQL and look at the "Characteristics"
>> pane after connecting to a database you can see these values. You
>> might consider refusing to insert new records after
>> PageSize*AllocatedPages exceeds some value, although this is pretty
> crude.
>> It is probably better to occasionally count the number of records in
>> a few "important" tables, and use this as the limiting criteria.
>>
>> --
>> Aage J.
>>
>
>
> After long discussion me and my colleagues accepted solution with one
> SUM field in separate table. This SUM field will contain sums of all
> important blob fields in database (probably 4-5 of them). User will
> not be able to temper with this field as he has no database access
> (our server hosting).
> SUM field will be updated (trough triggers) with SQL expression which
> (I hope) qurantee safe locking):
>
> UPDATE SUM_TABLE SET SUM_FIELD=SUM_FIELD+AddedSize;
>
> Normally, database will be used by few users (5-10) so concurrency
> will not be issue.
>
> I'm just wondering is this better option then to sum all blob fields
> before user inserts new data. This SQL works pretty fast for one table
> with couple hundred blob fields filled with data:
>
> SELECT sum(octet_length(BLOB_FIELD)) FROM MY_TABLE
>

If the last SELECT is fast enough, then ok. But beware of longer
response times for larger tables.

If you only want to check the size of the blobs, I would probably
chose the UPDATE.

Still, I might prefer PageSize*AllocatedPages (using total size of
database). Even though this will also count pages with just
(uncollected) garbage (record versions no longer required).


--
Aage J.