Description
When the target PostgreSQL version is below 15 (e.g., PostgreSQL 12), PeerDB falls back from MERGE to UPSERT+DELETE mode during the normalize stage. This fallback path has a bug in pgx batch parameter binding, causing all CDC mirrors to fail with:
Normalization Error: failed to normalize records: error executing normalize statement for table itmsdb.gw_device_model: error preprocessing batch (build): mismatched param and argument count
Environment
PeerDB version: stable-v0.36.26
Source PostgreSQL: 12.x
Target PostgreSQL: 12.x (confirmed < 15)
Deployment: Docker Compose, self-hosted
Steps to Reproduce
Create a source peer pointing to PG 12 and a destination peer pointing to PG 12
Create a CDC mirror with do_initial_copy = true
Wait for initial copy to complete
Make a DML change on the source table
Observe the normalize stage fails with mismatched param and argument count
Relevant Log Output
The following two WARN messages appear immediately before the error:
{"level":"WARN","msg":"Postgres version is not high enough to support MERGE, falling back to UPSERT+DELETE"}
{"level":"WARN","msg":"TOAST columns will not be updated properly, use REPLICA IDENTITY FULL or upgrade Postgres"}
{"level":"ERROR","msg":"error executing normalize statement","statement":"WITH src_rank AS (...) INSERT INTO ... ON CONFLICT ... DO UPDATE SET ...","error":"error preprocessing batch (build): mismatched param and argument count"}
The generated normalize SQL contains 3 parameter placeholders ($1, $2, $3), but the pgx batch execution fails with parameter count mismatch.
Root Cause Analysis
PeerDB checks if the target PG version supports MERGE (requires PG 15+)
For PG < 15, it falls back to generating separate INSERT...ON CONFLICT and DELETE statements
These statements are placed in a pgx Batch for execution
The pgx extended_query_builder reports mismatched param and argument count during batch preprocessing
This suggests the pgx statement cache holds a stale StatementDescription (from a previously prepared statement with a different parameter count), or the batch parameter binding logic is incorrect for the UPSERT+DELETE path
Attempted Workarounds
Pause/Resume Mirror: Does not resolve the issue
Restart flow-worker: Does not resolve the issue (error persists on every normalize attempt)
pgx connection parameters: PeerDB's CREATE PEER does not accept pgx connection string parameters like default_query_exec_mode=exec or statement_cache_capacity=0, which are known to work around similar pgx issues (as documented in ClickHouse Managed Postgres FAQ)
Downgrading PeerDB: Older versions also use UPSERT+DELETE approach, same bug likely exists
Table structure verification: Source and target table structures are 100% identical (verified column names, types, ordinal positions)
Impact
This makes PeerDB completely unusable for PG-to-PG CDC when the target is PostgreSQL 12 or 13 or 14. Since many production environments cannot upgrade PostgreSQL, this is a significant compatibility gap.
Suggested Fixes
Fix pgx batch parameter binding in the UPSERT+DELETE fallback path
Allow pgx connection parameters to be passed through CREATE PEER (e.g., default_query_exec_mode=exec, statement_cache_capacity=0)
Implement UPSERT+DELETE without pgx Batch — execute statements individually instead of batching, which avoids the parameter cache issue
Document the PG 15+ requirement for PG-to-PG CDC targets if the fallback path cannot be fixed
Additional Context
Source and target table structures are identical (17 columns, same types and order)
The error occurs on the very first normalize attempt after initial copy
Multiple tables exhibit the same behavior
Full sync (initial copy) completes successfully; only CDC normalize fails