Subject Re: Releasing of Database pages forcibly
Author Adam
> We are facing some issues with the release of database pages after
> delete all records in the table.
> Since there are no temporary tables in Firebird, like ORACLE, we are
> inserting updating data in the normal tables and then deleting when
> process is completed.

Well it can't, some other transaction could potentially be interested
in the old data. If you are indeed the only transaction interested in
the table, it may be cheaper to drop and recreate the table.

> However, there seems to be some issue with the
> Firebird Server 1.5.3 as it does not release the pages after all the
> records are deleted. When we check the RDB$PAGES table it still
> pages against these tables with 0 records. As a result, the
> operations on these tables take a performance hit as if data still
> persists.

At some stage after the delete, no transaction is interested in the
data so it can be garbage collected. The next transaction that looks
at the record will clean it up if it can. That appears to be your
problem here.

You have a few options. A simple select count(*) query (in a
different transaction after the delete has committed and after any
transaction that may have been interested in the data). This count
will collect the garbage.

Other options are the backup tool (gbak) performs garbage collection
while it runs by default, which means a nightly backup can actually
solve a lot of problems.

> Is there anyway in which we can release / de-allocate these pages
to the
> operating system. If this is a genuine issue with this version,
> you please suggest any other technique to circumvent this problem?

Firebird 2 has an improved index structure that makes garbage
collection significantly more efficient, but that is not yet
considered stable for production use.