matrixorigin / matrixorigin/matrixone

[Feature Request]: expose transaction-watermark table changes through SQL

Open
#26,615 1 comment 0 reactions 1 assignee Claimed by @Ariznawlll View on GitHub
kind/feature
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Is there an existing issue for the same feature request?

- [x] I have checked the existing issues.

## Is your feature request related to a problem?

Matrixflow Catalog currently discovers DDL performed directly against MatrixOne by repeatedly inventorying every active tenant's databases and tables. At QA scale this produced continuous full scans and substantial SQL, transaction, CPU, I/O, and memory pressure.

The MOI-side problem and reconciliation strategy are tracked in:

- matrixorigin/matrixflow#14373

The separate MatrixOne tombstone-allocation/OOM hotspot is tracked in:

- matrixorigin/matrixone#26465

This issue tracks a generic MatrixOne SQL capability that lets clients consume catalog or ordinary-table changes incrementally. It is an optional acceleration for MOI reconciliation and is intentionally not named after MOI.

## Describe the feature you'd like

Expose a stable transaction watermark and table change stream through table functions:

```sql
SELECT watermark
FROM change_watermark() AS w;

SELECT *
FROM table_changes('database_name', 'table_name', ?, ?) AS c;
-- interval semantics: (after, until]
```

`table_changes` should return change metadata followed by the visible source-table columns:

- `change_type`: `insert` or `delete`
- `commit_ts`
- `table_id`
- `schema_version`
- source columns

For delete records, primary-key columns are populated and non-key columns are `NULL`. Updates are represented by the underlying delete/insert changes.

## Describe implementation you've considered

The proposed implementation reads committed changes between two HLC timestamps using MatrixOne's changes handle and visible-state snapshot recovery.

Initial support scope:

- persistent ordinary tables with an explicit primary key;
- single and composite primary keys;
- cluster/catalog tables whose primary key contains `account_id`;
- tenant filtering for both inserted rows and delete tombstones;
- `mo_catalog.mo_database` and `mo_catalog.mo_tables` for per-tenant catalog discovery.

Explicitly reject table kinds where complete and safe change semantics are not currently available:

- tables without an explicit primary key;
- temporary tables;
- partitioned tables;
- subscription tables;
- views and external tables;
- cluster tables whose primary key does not contain `account_id`.

Database and table names are required to be non-empty string literals so schema planning is deterministic. `after` and `until` may be expressions or placeholders.

For shared physical catalog/cluster tables, the executor opens the physical relation under the system account but filters every result by the caller's tenant ID. Requiring `account_id` in the primary key is necessary to filter delete tombstones safely.

## Documentation, adoption, use case, and migration strategy

Matrixflow can use this capability per tenant as follows:

1. Capture `change_watermark()` before the initial full inventory.
2. Complete the existing full reconciliation.
3. Persist the captured watermark only after reconciliation succeeds.
4. On subsequent runs, capture a new upper watermark and read `(after, until]` changes from `mo_database` and `mo_tables`.
5. Apply the changes idempotently, then persist the upper watermark.
6. Retain a low-frequency full reconciliation for missed events, subscription metadata, and compatibility with MatrixOne versions that do not provide these functions.

Watermarks are opaque HLC strings. Consumer cursors are owned and persisted by the consumer; MatrixOne does not need to persist per-consumer offsets.

## Proposed implementation / PR branch

A working implementation with planner, executor, unit tests, and BVT coverage is available at:

- https://github.com/jiangxinmeng1/matrixone/tree/agent/catalog-incremental-discovery
- https://github.com/jiangxinmeng1/matrixone/compare/main...agent/catalog-incremental-discovery

BVT coverage includes ordinary tables, single/composite primary keys, insert/update/delete, scalar type families, `mo_database`, `mo_tables`, cluster-table tenant isolation for inserts and deletes, and explicit rejection of unsupported table kinds.

Related MOI implementation branch:

- https://github.com/jiangxinmeng1/matrixflow/tree/agent/catalog-incremental-discovery

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.