Subject Re: [firebird-support] Compacting the database from a App
Author Helen Borrie
At 07:18 PM 18/10/2010, you wrote:

>On 17/10/2010 05:01, Helen Borrie wrote:
>> gstat -h will tell you some useful things about the state of garbage
>> collection. If your client code is behaving correctly, you should not
>> see enormous differences between the 'Oldest transaction', the 'Oldest
>> Active transaction' and the 'Oldest snapshot'.
> Oldest transaction 10360730
> Oldest active 10360731
> Oldest snapshot 10360731
> Next transaction 10360732
> Bumped transaction 1
>So I believe this should be good (I have pasted the whole data below).

Actually, it is so good it is unbelievable. ;-) I assume you did this whilst you were the only user, having just performed a sweep or gbak. That is a pleasant way to start a day.

>One thing is that between start and stop of my application and starting
>and closing a screen I have an increase of almost 200 count of
>transaction numbers, for eg. previously Oldest transaction was 10360535
>now it is Oldest transaction 10360730. Is this good?

If you are looking at these numbers whilst multiple users are busy, then it is nothing.

> Is there a limit to the counter?

Yes, there is. I just can't remember what it is right now...something in the order of two billion, though.

>If I reach it I believe I need to do a backup and restore

..erm, you should aim to do a backup and restore considerably sooner than when the transaction counter is approaching the limit.

>, but what might happen if the limit is reached what kind of a behavior should me application be prepared for?

The database will become unusable.

>The reason I ask is this, is because users do not tend to have database administrators, they are simple users and I won't be able to keep a tag on them being just a developer so I need to preempt via code and warn them that they need to do this or that.

Preempt via good housekeeping practice. If your sites are completely unsupervised then you need to show a responsible user how to perform and store backups and how to perform a safe restore - and provide very good documentation of it all.

>> Hard COMMIT = good, COMMIT RETAINING is a bad legacy from the old
>> Borland world, that should be avoided.
>As per the header the Attributes is set to "force write", so is this
>fine if we are aiming for a Hard COMMIT? Sorry about my lack of
>knowledge of database tuning.

COMMIT RETAINING is unrelated to Force write. C-r does commit the work but it holds all the resources open for use by the next transaction. This causes garbage to be held back from clearance indefinitely. If you are performing a full controlled shut-down of the database every day then C-r won't impact things so much.