Getting Started with Change Views
Go Up to Change Views
Contents
ODS Platform Updates
- Change Views requires underlying modification of InterBase ODS, so existing databases must be backed up and restored to the ODS version that supports this feature. Due to the new ODS format, the restored database will not be attachable by older InterBase editions.
Migration Issues and Dependencies
- A subscription defined with FOR ROW (..., DELETE) returns a row with column values that existed before the row was deleted. Exercise caution in reporting applications on existing data to not have such a subscription bound to the transaction. Otherwise, the resultset will include data that no longer exists.
Requirements and Constraints
Requirements
- It is required to use the "odd-numbered" SQL DATA TYPES (SQLVAR variable, sqltype) when working with change views. This allows the SQL indicator variable in the SQLVAR to receive status on the returned column data.
- It is required to use transaction SNAPSHOT isolation level when selecting from a change view. Any isolation level can be used when modifying data in a change view.
- InterBase tables can have up to 256 record format versions. When a subscription references a table, this results in a new record format for the table. When issuing a large number of CREATE SUBSCRIPTION statements, disable autocommit DDL operation so that a single record format can be constructed from multiple table references from many subscriptions.
Constraints
- TRANSACTION ID AS PROXY FOR SUBSCRIPTION TIMESTAMP
- The feature relies heavily on transaction ID as a proxy for last observed timestamp of a changed data view and assuring that changed data results are not resent in duplicate. Currently, database backup does not save transaction IDs of committed data and restores a database with next transaction ID reset to zero. A 32-bit transaction ID space can be and has been exhausted at customer sites, which necessitates a database backup and restore.
- This project assumes conversion to 64-bit (more likely 48-bit) transaction IDs with the assumption they could not be exhausted in a reasonable time frame. For example, a database with a 4KB page size could run continuously in excess of 10,000 tps for 100 years with a 48-bit transaction ID. In addition to supporting changed data views, it also ensures that an InterBase database never has to be shutdown for backup and restore because transaction IDs have been exhausted.
Backup/Restore Considerations
- A logical backup/restore will backup and restore all subscription definitions. But, data that tracks subscribers using those subscriptions will not be backed up or restored. Subscribers need to initiate/activate their subscriptions on new (or restored) databases.
Deferred Constraint Checking
The sequence or order of changes in the source database is not captured by changed data views. This statement is true with regard to changes across different tables as well as within a single table. It would be possible to approximate the sequence by exposing a pseudo-column RECORD_VERSION or TRANSACTION_ID and sorting a changed data resultset on that column. However, this does not guarantee the correct order of changes in all use cases. The order of changes can be important for satisfying constraint checking when using one or more subscriptions as a sync mechanism. InterBase only supports IMMEDIATE constraint checking and it may be a requirement to support DEFERRED constraint checking to allow subscription changes to be applied to a destination database with various constraints in place.
Trigger Inactivation
InterBase allows a named trigger to be declared inactive. It may be that triggered actions should be prevented when subscribed changes are applied to a destination database; this is an application-dependent decision. But the scope of trigger inactivity as described here should be restricted to the single database session. InterBase makes the trigger inactive across all database sessions for the database. The same requirement of limited scope of operation would be true of DEFERRED constraint if it were to be implemented.
Database Restore from a Backup
If the database is restored from a logical backup file or physical dump file, subscribers may retrieve subscription changes that they have already received. There is metadata RDB$SUBSCRIPTIONS.RDB$CHECK_OUT_TIMESTAMP that a subscriber could save to help with the issue of duplicate change processing.