--- In firebird-support@yahoogroups.com, "Branimir Trumbic"
<seronimo@...> 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;

It will be safe, but running this DML inside a trigger on your table
will almost certainly cause concurrency bottlenecks. It means that
once someone has inserted a new record in the table, no-one else will
be able to until that person commits their transaction.

The usual solution is something like this.

Create SUM_TABLE, but accept it will have multiple records and to get
the real sum, you must select sum(sum_field). You can write a view to
do that.

Place triggers on your main table. On the insert even, add a record
containing the blob size. On the delete, add a record containing -1 *
old blob size. On update, add a record containing the delta.

Now any transaction can get the real sum by summing the values in
SUM_TABLE. This will work, but obviously left in such a state it will
get more an more expensive to sum. The solution is to write a stored
procedure to summarise this table. (select the sum, delete the values,
insert sum as new value). Write a simple sql script to call that
stored procedure, and place an iSQL command to run the script in
scheduled tasks/cron say every 30 minutes.

That is the best of both worlds. You provide complete concurrency, but
querying the sum is significantly cheaper than without the sum_table.

Adam