CDC Limitations
When utilizing the Change Data Capture (CDC) API for Ingres, it's crucial to understand several limitations that can impact implementation and functionality. These restrictions are primarily related to table storage formats, object types, logging configurations, and the filtering capabilities of the WHERE clause.
The limitations of CDC API for Ingres are outlined below.
X100 Table Storage Format Not Supported
The CDC API does not support tables that are configured with the X100 table storage format. If your Ingres database utilizes X100 for any tables you intend to monitor with CDC, you will need to re-evaluate your storage strategy or consider alternative methods for capturing changes from these specific tables. The API is designed to work with other standard Ingres table formats and ensuring compatibility is a prerequisite for successful implementation.
Views, Synonyms and Temporary Tables Are Not Supported
The CDC publication API is designed to work directly with base tables. Consequently, it does not support the use of views or synonyms as sources for change data capture. Attempting to create a CDC publication on a view or synonym will result in an error. To capture changes, you must specify the underlying base table(s) that the view or synonym references. This limitation ensures that the CDC mechanism is directly tracking physical data modifications at the lowest level. Changes to the data in the base table, whether implemented via a synonym, procedure or a rule, continue to be captured by the CDC API.
Non-Logged Tables Are Not Supported
A fundamental requirement for the CDC API to function is that the monitored tables must be logged. Ingres logging mechanism is essential for tracking transactional changes, which the CDC API then leverages to identify and extract data modifications. If a table is configured as non-logged, the CDC API will be unable to capture any changes made to it. Users must ensure that all the tables intended for CDC are properly configured for logging within the Ingres database.
WHERE Clause Filter Restrictions
The WHERE clause, used for filtering the data captured by the CDC API, has specific limitations regarding the operators and constructs it supports. Understanding these restrictions is vital for crafting effective and compatible filters.
Supported Operators and Constructs
The following operators and constructs can be used within the WHERE clause filter:
• Logical Operators: AND, OR
• Comparison Operators: =, !=, <>, <, <=, >, >=
• Parentheses: Used for grouping conditions and controlling the order of evaluation.
Update Operation Behavior
The CDC API handles the UPDATE operations with a specific logic, particularly when a WHERE clause filter is applied to the publication. This behavior dictates how an update is translated into CDC events (INSERT or DELETE) based on whether the "before image" and "after image" of a row match the filter criteria.
• DELETE Operation Emission: If the "before image" (the state of the row before the update) satisfies the WHERE clause filter, but the "after image" (the state of the row after the update) does not satisfy the filter, the CDC API will emit a DELETE operation. This signifies that the row, in its updated form, no longer meets the criteria for inclusion in the captured data.
• INSERT Operation Emission: Conversely, if the "after image" of the updated row matches the WHERE clause filter, but the "before image" did not match the filter, the CDC API will emit an INSERT operation. This indicates that the updated row, due to the changes, now satisfies the filter criteria and should be considered newly relevant to the captured data stream.
This behavior for updates ensures that the CDC stream accurately reflects the filtered set of data, even when updates cause rows to enter or exit the defined criteria. Developers need to be aware of this logic when designing applications that consume CDC events, especially when WHERE clauses are used extensively.
DDL Changes:
The CDC API does not capture or stream DDL records, such as create table, alter table, drop table, and rename table operations.
Drop table, rename table, and rename column operations are not supported for tables under capture. It is imperative to shut down CDC sessions before implementing unsupported schema alterations.
Alter Table Support and Limitations:
When a publication table undergoes alteration, the CDC API registers schema changes and streams a new RELATION record that incorporates the table's current schema.
Add Column Operation:
CDC sessions support the addition of new columns to tables under capture. However, data for newly added columns will not be captured. To facilitate the capture of data for newly added columns, CDC sessions must re-register the publication on the altered table and ensure the inclusion of the newly added columns in the publication API.
Alter Column Support for Changing Column Type or Column Length:
CDC sessions support the alteration of column data types on tables under capture. The CDC session will immediately commence streaming data in the new column type format. It is not necessary to shut down CDC sessions to modify column type or column length.
Drop Column Operation:
CDC sessions support the drop column operation on tables under capture. When streaming row data for a table with a dropped column, a dropped column indicator will be included in the column header.