Ann W. Harrison wrote:

>At 02:29 AM 5/27/2004, Jonathan Neve wrote:
>
>
>>Hmm. Well, I guess you must be right. Jim said the same thing. But what
>>specifically makes it impossible?
>>
>>
>
>The larger problem is that different applications need different types of
>versioning. The one you're describing is very basic - allows you to see
>what was happening at 10:30 last Tuesday. That's OK for read-only
>access, but suppose you want to fix something that happened last
>Tuesday? Do you create an alternate future? For some applications - CAD,
>CVS, etc., that's what you want. For others - accounting for example -
>alternate futures are unwise.
>
>

Well, it seems to me that changing history is going a bit far! :-)
The idea I was suggesting was to be able to view previous states of a
certain record. Obviously, changing these previous states would be very
problematic. Perhaps this would be useful for certain
people/applications, but here, I agree that it would be difficult or
perhaps impossible to do this sort of thing in the engine. But for
read-only access, it seems feasable.

>>It seems to me that if we make the syntax very general (no automatic
>>restore of back versions of any such thing), it should be possible. If
>>the old versions are simply made available for reading, then what could
>>be the problem?
>>
>>
>
>Read-only access to back versions is probably safe, but in fact, in the
>current implementation of Firebird, it presents problems. There is no way
>to determine a consistent view of old data. Firebird constructs a
>consistent view for active transactions by keeping a list of "unsafe"
>transactions, those that have rolled back and those that are still
>active. The fact that one transaction number is higher than another
>doesn't mean that the first transaction can see the work of the
>second. The second could still be active. To get a consistent picture
>of past versions, you need to know the order of commits. That information
>is not available. Creating and maintaining it would have a significant
>impact on system performance.
>
>

I see, this is certainly a problem I hadn't thought about. However, am I
right in understanding that this is only a problem if we request to see
the state of data at a specific point in time? Wouldn't it be possible
to simply view all previous(non-rolled back) versions of a record. That
way, we only need to know which the transactions rolled back, and not
the order of the commits.

However, would it be possible to determine that certain updates, across
several tables, were done in the same transaction, and thus to
reconstitute the state of things within a certain transaction? Or will
we run into the problem you were describing?

>No doubt you've seen threads about the performance impact of long-running
>transactions. When the number of back versions increases, indexes and data
>locality suffer. The indexes fill with pointers to old versions, so index
>selectivity goes down and index size goes up. Back versions of records
>stop fitting on page with their most recent copy, so any operation, read or
>write, requires reading multiple pages.
>
>

Very interesting.

>I understand that you've presented this as a feature request, but what
>would you use it for?
>
>

Well, the idea would simply be to enable my users to have a backlog of
all previous versions (up to a certain point) of certain important
documents (invoices, and such like). That way, they could view old
versions, in case something got lost or deleted. I have already had two
cases where a customer calls me because they accidentally deleted a
record. So I had to get a backup copy (which, fortunately contained the
record, it might not have), and create a quick application to pump the
record (as well as the detail records in several other tables) into the
main database. This sort of thing is no fun. Also, there is a risk of
losing data, since a backup is never quite up to date. Whereas is we
kept the old record versions, we could simply restore the record with a
few simple SQL commands.

Thanks for considering my request and answering my post! :-)

Jonathan Neve.


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