Subject Re: Transaction status
Author alex_vnru
--- In ib-support@y..., "Ann W. Harrison" <aharrison@i...> wrote:
> Yes, sweep changes the state of rolled back transactions after
> it has removed their updates from the database.

Sorry if I re-post, I have a problems with Yahoo since previous
maintenance period here.

Hmm, this removes one of the three elefants on which my idea
stand... I'm very interested in external log maintenance. Can't say FB
database is frequently corrupted but if this occures repairing is a
roulette play. Of course for important production databases I make a
daily backups and check their restorability automatically. And for 7
years of experience with IB/FB I only 2 times was forced to rollback
to yesterday's backup but... Maybe someone can improve next idea.
To have possibility of external logging for rollforward from
previous backup using triggers and UDFs:

1. Invent system variable TRANSACTION_ID accessible within SP and
2. Invent triggers on events "transaction commit" and "transaction
rollback" which can do nothing except UDF(TRANSACTION_ID) call without
any reaction on exceptions which can occure.

Having this we can supply 2 independent streams from server to
external world - one with changes marked by transaction number and
another with state of transactions finishes marked by their number. We
can superpose this streams only when it is needed, not an "on-line".
This provides very wide and flexible possibilities to build any type
of external log managers - directly by UDFs on the same computer, via
special application, informed by UDFs on another computer, even full
on-line replicator. And absolutly without performance degradation in
server itself, all quality and errors are on conscience of person who
develop own schema based on this atomic features. UDF can just inform
log manager and return, data manipulations in it should not be
syncronized with processes in database.

I know there are events. But they are too universal, firstly both
transaction and operation dependent, for this purpose operation part
is redundant, secondary they are too environment dependent and
reqiures additional connections, that causes many problems, with
firewalls for example, OS nuances etc. Too many functionality in one
element, I want more simply one. I can provide specific on needed for
me level of dumbness and reliability in UDF. If I can't, it is my
problem, not FB. And there is'nt event for rollback.
Yes, such a complex mechanism is liable to more problems then server
and database itself, but I lose data only when BOTH database and log
are broken. There was third part of idea - if problems in my external
logging subsystem causes loss of some events, give me possibility to
read transaction statuses by their IDs on shutdowned database to
resync log. But changing status from rolled back to commited by sweep
significantly narrows such implementation.

Forgive me for long post in awkward English, hope I was clear

Best regards, Alexander V.Nevsky.