cockroachdb / cockroachdb/cockroach

crosscluster/logical: SHOW LOGICAL REPLICATION JOBS reports a stale replicated_time for transactional-mode jobs

Open
#173,331 4 comments 0 reactions 1 assignee Claimed by @DarrylWong View on GitHub
A-cdc A-logical-data-replication C-bug O-agent T-cdc
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

For LDR jobs running with `MODE = 'transactional'`, the `replicated_time` column of `SHOW LOGICAL REPLICATION JOBS` is frozen at the job's `CURSOR` and never advances, even while the job is healthy and actively replicating. An operator reading that column sees a job that appears permanently stalled by however long it has been running.

The underlying replication is correct — only the reported value is wrong.

**To Reproduce**

1. Start a cluster, create a source and destination table.
2. Start a stream: `CREATE LOGICAL REPLICATION STREAM FROM TABLE plain ON 'external://src' INTO TABLE plain WITH MODE = 'transactional', CURSOR = '-1s'`.
3. Write rows on the source; confirm they land on the destination.
4. Wait a minute, then read the column.

Observed on an otherwise unremarkable stream (no conflicts, both rows replicated, job `running`):

```
now 2026-08-12 21:16:06
SHOW ... replicated_time 2026-08-12 21:14:17 <- frozen, 109s behind
SHOW ... replication_start_time 2026-08-12 21:14:17 <- identical to it
system.job_progress.resolved 2026-08-12 21:16:06 <- current, 0.4s lag
```

Read twice, 75 seconds apart: `replicated_time` did not move between reads, while `resolved` advanced with the wall clock.

**Expected behavior**

`replicated_time` tracks the job's frontier, as it does in row/immediate mode.

**Root cause**

There are two records of a replicated time, and the delegate reads the one transactional mode does not write.

Row mode writes both in the same job update — [`logical_replication_job.go:382`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/logical_replication_job.go#L382): the LDR-specific `prog.ReplicatedTime`, plus a generic `Progress_HighWater`, which [`jobs/update.go:284`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/jobs/update.go#L284) mirrors into `ProgressStorage` (`system.job_progress.resolved`, surfaced as `crdb_internal.jobs.high_water_timestamp`).

Transactional mode writes only progress storage, and writes it directly — [`txnmode_dist.go:437`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/txnmode/txnmode_dist.go#L437). Nothing sets `prog.ReplicatedTime` after job creation, where it is seeded with the cursor at [`create_logical_replication_stmt.go:247`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/create_logical_replication_stmt.go#L247) — hence the frozen value, and hence its exact equality with `replication_start_time`.

The delegate reads the proto field: [`show_logical_replication_jobs.go:39`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/sql/delegate/show_logical_replication_jobs.go#L39).

**Scope — display only**

Confirmed unaffected:

- **Source PTS / retention.** The ongoing heartbeat sender carries the real frontier, and [`stream_lifetime.go`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/producer/stream_lifetime.go) only ever pushes the PTS forward, so the one stale value sent by the initial heartbeat at resume is ignored.
- **Restart from checkpoint.** Reads progress storage, falling back to `payload.ReplicationStartTime`.
- **The `replicated_time_seconds` metric.** Fed from progress storage via `jobs.WithResolvedMetric`.
- **Test helpers.** Already read `crdb_internal.jobs.high_water_timestamp`.

**Suggested fix**

Read `high_water_timestamp` in the delegate, which both modes populate:

```diff
- hlc_to_timestamp((crdb_internal.pb_to_json(
- 'cockroach.sql.jobs.jobspb.Progress',
- job_info.progress)->'LogicalReplication'->'replicatedTime'->>'wallTime')::DECIMAL) AS replicated_time%s
+ hlc_to_timestamp(job_progress.high_water_timestamp) AS replicated_time%s
FROM crdb_internal.system_jobs AS job_info
LEFT JOIN table_names
ON job_info.id = table_names.job_id
+LEFT JOIN crdb_internal.jobs AS job_progress
+ON job_info.id = job_progress.job_id
-WHERE job_type = 'LOGICAL REPLICATION'
+WHERE job_info.job_type = 'LOGICAL REPLICATION'
```

Three details, all forced:

1. `high_water_timestamp` lives on `crdb_internal.jobs`, not `crdb_internal.system_jobs`, hence the extra join. `system_jobs` still drives the query because `WITH DETAILS` needs `payload`.
2. `LEFT`, so a job not visible in `crdb_internal.jobs` yields a `NULL` `replicated_time` rather than dropping out of the result.
3. The `WHERE` needs qualifying — both tables expose `job_type`, so the bare reference becomes ambiguous once the join is added.

Verified to return a current timestamp against a live transactional-mode job.

The existing `TestShowLogicalReplicationJobs` uses row-mode jobs, where both fields are written in the same update and are therefore equal, so it stays green. A regression test asserting that a transactional-mode job's `replicated_time` advances past its cursor is missing and should be added — that gap is why this went unnoticed.

**Follow-up**

`prog.ReplicatedTime` is overloaded: before the first checkpoint it means "the cursor to start from", after it means "the frontier". Job creation seeds the proto field but writes nothing to progress storage, so row mode leans on both meanings of the one field — see [`resume_row.go`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/resume_row.go), [`resume_create_table.go`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/resume_create_table.go) (where `ReplicatedTime.IsSet()` is really asking "has this job ever checkpointed?"), and [`distsql_planner.go`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/distsql_planner.go).

Transactional mode already separates the two — cursor from `payload.ReplicationStartTime`, progress from progress storage ([`resume_txn.go:145`](https://github.com/cockroachdb/cockroach/blob/8812064a015d2faf99d3fc7e15880f94042954b0/pkg/crosscluster/logical/resume_txn.go#L145)). Porting row mode to that shape would leave `prog.ReplicatedTime` with no readers and it could be retired. Worth splitting into its own issue if picked up — it touches restart and the create-table gating, so it needs real test coverage.

**Environment**

- CockroachDB `v26.4.0-alpha` (dev build from master)
- Server OS: macOS (single-node, `start-single-node --insecure`)
- Client app: `cockroach sql`

Jira issue: CRDB-66650

Epic CRDB-65552

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.