Priebe, Joern (GE Indust, Security) wrote:

>
> [...]
> BEGIN
> DELETE FROM TESTTABLE WHERE CREATETIME < cast( 'now' as TimeStamp );
> END
>
> and if I keep inserting records in this TESTTABLE with a CREATETIME from
> now, all records will immediately be deleted (or to keep it in other
> words: will get marked as deleted, thus take more space).

Right. Deleting a record leaves the old record in place and adds a
stub record marked as deleted. The stub record is about 25 bytes.

>
> My INSERT statements are commited each time, but still - the database
> size does not stay constant.

When your application commits the insert, all the changes made by
the triggers are also committed. Triggers run in the same transaction
as the operation that fires them.

> So is my Trigger commiting its command automatically, or do
> I need to add a COMMIT WORK in the second line?

The work done by the trigger is committed when the operation that
caused it to fire is committed. You can't commit in a trigger - that
would really mess up transaction semantics - suppose the transaction
was supposed to insert one record and modify another to make the
database consistent. If the insert had a trigger that committed
it, half the change would be visible before the second half was
started. Very bad.

>
> Or am I forced to perform a SELECT on my deleted records, to
> free up the space for new records? Why is a sweep not enough to
> do this?
>

What Ann said was that three things had to happen before the space
was released.

First, the transaction that did the delete must commit. We've
just gone through that, and yes, if you commit the transaction
that did the insert, that commits the deletes.

Second, all concurrent transaction must also finish. Any
transaction that was running before the transaction that
did the insert commits must finish - commit or rollback
doesn't matter. If somebody starts an IBExplorer transaction
in the morning and leaves it running all day, no space will
be released that day.

And third, some other transaction, started after all those
other transactions are gone, must attempt to read the record
you deleted. Either sweep or backup will read every record
in the database. That's one reason why it's a good idea to
run either sweep or backup or both every night when things
are quiet.


Regards,


Ann