Dmitry thanks for answers. I understand fine.

But I made a new test:

After deleting cod=2, I make a backup with garbage collect set and do a
'select count(*) from test' to ensure all rows are touched.

Then:

Insert into test(cod, name) values (2, 'Small 2');



Again, it does not reuse the old space. I try disconnect all, reconnect and
FB does not use old space.

I agree with you with my first test but with this I does not see why it does
not reuse space.

Many thanks!







Hello, Fabiano!

Monday, May 21, 2012, 9:08:04 PM, you wrote:

F> I have a question about how Firebird internally updates a record.

it uses versioning.

F> Insert into test(cod, name) values (1, 'Big description 1');
F> Insert into test(cod, name) values (2, 'Big description 2');
F> Insert into test(cod, name) values (3, 'Big description 3');

well, 3 records in table.

F> Delete from teste where cod=2;

here engine created new version named as "delete stub" for one of the
records. It keeps "old" record because it can be still visible and
required for running snapshot transactions, even if they are not
exist now.

F> Whell, next I insert a new record:
F> Insert into test(cod, name) values (2, 'Small 2');

4-th record.

F> After that I reinspect the FDB file and it not REUSE the old record that
F> contains "Big description 2". It stays here and a NEW record was create
to
F> hold the new record 2 that are SMALL than old record.

yes. deleted or updated records are "new versions", and "old versions"
can be considered garbage and removed from pages only when engine
will be sure that these "old versions" are not needed for any
running transactions.
Old versions will be cleared during select or update statement that
will "touch" this records.

F> Why? Why Firebird does not reutilize that space?

see below.

F> I tested with only one connection in a test database with only this
table.

versioning works always, there is no "single-user mode" in the
Firebird engine.
please read
http://www.firebirdsql.org/en/multi-version-concurrency-control/
http://en.wikipedia.org/wiki/Multiversion_concurrency_control
http://blogs.teamb.com/craigstuntz/2005/02/18/2699/

http://blogs.teamb.com/craigstuntz/2004/06/22/587/

etc.

--
Dmitry Kuzmenko, www.ib-aid.com





[Non-text portions of this message have been removed]