Default Postgres ClickPipe mappings to ReplacingMergeTree while preserving explicit engine overrides #1009

Problem

An ordinary cloud clickpipe create postgres command defaults to ongoing CDC but sends tableEngine: "MergeTree" for simple table mappings. A JSON mapping that omits tableEngine makes the same choice. This produces an append-only destination incompatible with the documented current-state query for Postgres CDC.

Requested behavior: default Postgres table mappings to ReplacingMergeTree, while preserving an explicit user engine choice. Users must still be able to select MergeTree or another supported engine through --table-mapping-json.

How the current default is selected

On tested main df81e46cfec0bb58ea981d17cc6fd2de24442e7d (CLI 0.5.0):

• The simple mapping builder constructs the mapping with ..Default::default().
• The Postgres mapping engine enum marks MergeTree as its Rust default.
• The JSON mapping parser uses that enum default when tableEngine is omitted.

The engine is serialized explicitly in the request; this is not an omitted value left for the service to choose. ClickPipes creates the destination table from that request.

Observed impact

The September 23 live QA run created two pipes from the same disposable Postgres source: a simple mapping using the implicit engine, and a control mapping explicitly selecting ReplacingMergeTree. Actual engines were SharedMergeTree and SharedReplacingMergeTree(_peerdb_version) respectively, both keyed by the source primary key.

All events arrived. The default destination retained the old version of the updated row and the earlier live version of the deleted row. The documented query SELECT ... FROM destination FINAL WHERE _peerdb_is_deleted = 0 failed on that table with SQL error 181: Storage SharedMergeTree doesn't support FINAL. The explicit replacement-engine control returned the exact source rows after every phase.

ClickHouse's Postgres CDC documentation describes versioned ReplacingMergeTree destinations and the FINAL/delete-filter query. Custom latest-version aggregation could reconstruct the correct state from the MergeTree events; this is a default/query-semantics defect, not demonstrated event loss.

Acceptance

• Simple --table-mapping schema.table:target selects ReplacingMergeTree.
• --table-mapping-json without tableEngine also selects ReplacingMergeTree.
• Explicit supported tableEngine values are preserved, including MergeTree, ReplacingMergeTree, and Null.
• Keep the default change scoped to Postgres mappings; do not change unrelated streaming or other-source defaults.
• Update help and README guidance to describe the default, the explicit override, and current-state querying with FINAL and deletion filtering. A raw SELECT is not guaranteed to return deduplicated current state merely because the engine changes.
• Cover both implicit mapping paths and explicit overrides in request-level regression tests. Include meaningful live CDC verification of exact source/destination state after INSERT, UPDATE, and DELETE using the default mapping.

Related #566 exposed per-table engine selection; #594 concerns streaming destination definitions. Neither covers this Postgres default correction.