Subject Re: Back to Point in Time
Author paulruizendaal
> >Second:
> >There is a feature in oracle(hippopotamus, who tries to fly as
> >Firebird) Point-In-Time, you know. (db level transactions)
> >I don't need it but there is a
> >goverment specifications that captured from oracle features
> >documents. I think it's implementation is much easy in FB because
> >of MGA architecture.

> We've looked at this maybe a half dozen times over the history of
> Interbase. The problem is that data versioning is highly
> application specific and that most, if not all, models require that
> the versioning data be visible. This precludes an automatic, under-
> the-covers solution. Another problem, probably more serious, is
> the "simple" solution stops the garbage collection process, rapidly
> rendering a volatile database unusable.
> Data versioning requires a much coarse level of granularity that
> individual updates, and visibility needs to be under application
> control. The multi-generational architecture isn't the way to
> implement it.

Hi Jim,

Just a few thoughts on this topic from a Fyracle perspective.

Flashback queries (which is what Ali refers to, I think) were
introduced into Oracle 9iR2 (which is 2003 if I'm not mistaken). It
only functions with Oracle's current version of MVCC, not with their
earlier rollback segment architecture.

Flashback queries refer to a SCN, or System Change Number. I think
these are somewhat similar to our transaction numbers. Oracle stores
the current SCN in a time table at five minute intervals. If you ask
for a flashback query by timestamp, the time is resolved to the
nearest SCN using this table. The mapping is maintained for the last
5 days.

The manual warns that using flasbacks will consume large amounts of
space and the DBA must set limits for this; actual storage time is
less than 5 days if space runs out (obviously). In order to limit
space consumption the flashback feature can be enabled by individual
table and blobs can be excluded.

The manual also warns that if there are many SCN's between the
present and now that queries will be very slow, especially if no
index is available and a full table scan is needed. This suggest that
Oracle must work its way through many diffs in this case.

Just sherlocking on the manual, I'd say that Oracle has implemented
flashback queries exactly in the way that Ali suggests.

However, I tend to agree with you that it is not a good idea; all the
more so since I don't see a serious use case for flashback queries.
Perhaps that is a lack of imagination on my part.

For the marketing check list (Ali's government client seems to work
with such lists), the following could be done, entirely on the client

- the application opens a separate connection handle. On this handle
a new transaction is started every five minutes, holding on to each
handle for a certain period, 5 days if need be.
- if the application needs a flashback query, it selects the right
transaction handle and executes the query in this context

This works even with FB1.0. It would bloat the database (as it does
in Oracle) and make no sense, but it would get the check mark in the
pundit evaluation list.