Incorrect comparison result on a specific table – data exists but reported as different

[cobolbaby]

Hi team,

We encountered an issue during data comparison:

One specific table always shows differences in the comparison result, but after checking the actual records manually, the data does exist and looks consistent.

Important notes:

• This problem only happens on one particular table
• Other tables are compared correctly
• Manual queries confirm that the records are present
• But pgcompare reports differences for this table

So far we are not sure whether this is related to:

• or comparison logic itself

bash-4.4$ java -jar pgcompare.jar compare -t pca_aft_check_log
Picked up _JAVA_OPTIONS: 
[2026-02-06 01:39:40] [INFO   ] [main                    ] Starting - rid: 1770341980777 
[2026-02-06 01:39:40] [INFO   ] [main                    ] Version: 0.5.0.0 
[2026-02-06 01:39:40] [INFO   ] [main                    ] Batch Number: 0 
[2026-02-06 01:39:40] [INFO   ] [main                    ] Connecting to repository database 
[2026-02-06 01:39:41] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:41] [INFO   ] [preflight-util          ] Performing Preflight checks for source 
[2026-02-06 01:39:41] [INFO   ] [preflight-util          ] Performing Preflight checks for target 
[2026-02-06 01:39:41] [INFO   ] [main                    ] Parameters:  
[2026-02-06 01:39:41] [INFO   ] [main                    ]   action=compare 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   batch=0 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   batch-commit-size=2000 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   batch-fetch-size=2000 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   batch-progress-report-size=1000000 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   column-hash-method=database 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   config=/opt/pgcompare/pgcompare.properties 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   config-file=/opt/pgcompare/pgcompare.properties 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   database-sort=true 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   fix=false 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   float-cast=notation 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   float-scale=3 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   genReport=false 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   home=/opt/pgcompare 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   isCheck=false 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   loader-threads=2 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   log-destination=stdout 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   log-level=INFO 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   message-queue-size=100 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   number-cast=notation 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   observer-throttle=true 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   observer-throttle-size=2000000 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   observer-vacuum=true 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   project=1 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-dbname=pgcompare 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-host=****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-port==****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-schema=pgcompare 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-sslmode=disable 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   repo-user=postgres 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   reportFileName= 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-dbname=bdc 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-host==****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-port==****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-schema=fis 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-sslmode=disable 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-type=postgres 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-user=postgres 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   source-warehouse=compute_wh 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   stage-table-parallel=0 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   standard-number-format=0000000000000000000000.0000000000000000000000 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   table=pca_aft_check_log 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-dbname=bdc_test 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-host==****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-port==****** 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-schema=fis 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-sslmode=disable 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-type=postgres 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-user=postgres 
[2026-02-06 01:39:41] [INFO   ] [main                    ]   target-warehouse=compute_wh 
[2026-02-06 01:39:41] [INFO   ] [main                    ] Connecting to source and target databases 
[2026-02-06 01:39:41] [INFO   ] [connection              ] Successfully connected to postgres database (source) 
[2026-02-06 01:39:41] [INFO   ] [connection              ] Successfully connected to postgres database (target) 
[2026-02-06 01:39:41] [INFO   ] [compare-ctrl            ] Recheck Out of Sync: false 
[2026-02-06 01:39:41] [INFO   ] [compare-ctrl            ] Retrieving tables for project 1, batch 0, table filter: pca_aft_check_log 
[2026-02-06 01:39:41] [INFO   ] [table-ctrl              ] --- START RECONCILIATION FOR TABLE: PCA_AFT_CHECK_LOG --- 
[2026-02-06 01:39:41] [INFO   ] [table-ctrl              ] Clearing data compare findings 
[2026-02-06 01:39:42] [INFO   ] [repo-ctrl               ] Data comparison results deleted for table 1061, batch 1 
[2026-02-06 01:39:42] [INFO   ] [column-metadata-builder ] (source) Building column expressions for fis.pca_aft_check_log 
[2026-02-06 01:39:42] [INFO   ] [column-metadata-builder ] (target) Building column expressions for fis.pca_aft_check_log 
[2026-02-06 01:39:42] [INFO   ] [compare-ctrl            ] (source) Columns: badgeno,cdt,fixno,inputtype,ispass,mcbsno,pdline,refid,remark,snoid,testcount,wc 
[2026-02-06 01:39:42] [INFO   ] [compare-ctrl            ] (target) Columns: badgeno,cdt,fixno,inputtype,ispass,mcbsno,pdline,refid,remark,snoid,testcount,wc 
[2026-02-06 01:39:42] [INFO   ] [compare-ctrl            ] (source) PK Columns: id 
[2026-02-06 01:39:42] [INFO   ] [compare-ctrl            ] (target) PK Columns: id 
[2026-02-06 01:39:42] [INFO   ] [sql-syntax-srv          ] (source) Compare SQL: SELECT lower(md5(coalesce(trim(to_char(id,'0.9999999999EEEE')),' '))) AS pk_hash, '{'||'"id": ' || id||'}' as pk,lower(md5(coalesce(case when length(coalesce(trim(badgeno::text),''))=0 then ' ' else trim(badgeno::text) end,' ')||coalesce(to_char(cdt,'MMDDYYYYHH24MISS'),' ')||coalesce(case when length(coalesce(trim(fixno::text),''))=0 then ' ' else trim(fixno::text) end,' ')||coalesce(case when length(coalesce(trim(inputtype::text),''))=0 then ' ' else trim(inputtype::text) end,' ')||coalesce(case when length(coalesce(trim(ispass::text),''))=0 then ' ' else trim(ispass::text) end,' ')||coalesce(case when length(coalesce(trim(mcbsno::text),''))=0 then ' ' else trim(mcbsno::text) end,' ')||coalesce(case when length(coalesce(trim(pdline::text),''))=0 then ' ' else trim(pdline::text) end,' ')||coalesce(trim(to_char(refid,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(remark::text),''))=0 then ' ' else trim(remark::text) end,' ')||coalesce(trim(to_char(snoid,'0.9999999999EEEE')),' ')||coalesce(trim(to_char(testcount,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(wc::text),''))=0 then ' ' else trim(wc::text) end,' '))) AS column_hash FROM fis.pca_aft_check_log WHERE 1=1 
[2026-02-06 01:39:42] [INFO   ] [sql-syntax-srv          ] (target) Compare SQL: SELECT lower(md5(coalesce(trim(to_char(id,'0.9999999999EEEE')),' '))) AS pk_hash, '{'||'"id": ' || id||'}' as pk,lower(md5(coalesce(case when length(coalesce(trim(badgeno::text),''))=0 then ' ' else trim(badgeno::text) end,' ')||coalesce(to_char(cdt,'MMDDYYYYHH24MISS'),' ')||coalesce(case when length(coalesce(trim(fixno::text),''))=0 then ' ' else trim(fixno::text) end,' ')||coalesce(case when length(coalesce(trim(inputtype::text),''))=0 then ' ' else trim(inputtype::text) end,' ')||coalesce(case when length(coalesce(trim(ispass::text),''))=0 then ' ' else trim(ispass::text) end,' ')||coalesce(case when length(coalesce(trim(mcbsno::text),''))=0 then ' ' else trim(mcbsno::text) end,' ')||coalesce(case when length(coalesce(trim(pdline::text),''))=0 then ' ' else trim(pdline::text) end,' ')||coalesce(trim(to_char(refid,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(remark::text),''))=0 then ' ' else trim(remark::text) end,' ')||coalesce(trim(to_char(snoid,'0.9999999999EEEE')),' ')||coalesce(trim(to_char(testcount,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(wc::text),''))=0 then ' ' else trim(wc::text) end,' '))) AS column_hash FROM fis.pca_aft_check_log WHERE 1=1 
[2026-02-06 01:39:42] [INFO   ] [thread-manager          ] Starting compare hash threads 
[2026-02-06 01:39:42] [INFO   ] [staging-operations      ] Staging table created: dc_source_1061_0 for location: source, table: 1061, thread: 0 
[2026-02-06 01:39:42] [INFO   ] [staging-operations      ] Staging table created: dc_target_1061_0 for location: target, table: 1061, thread: 0 
[2026-02-06 01:39:42] [INFO   ] [observer-c170-t0        ] Starting reconcile observer 
[2026-02-06 01:39:42] [INFO   ] [observer-c170-t0        ] Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [compare-source-1061-t0  ] (source) Start database reconcile thread 
[2026-02-06 01:39:42] [INFO   ] [compare-target-1061-t0  ] (target) Start database reconcile thread 
[2026-02-06 01:39:42] [INFO   ] [compare-source-1061-t0  ] (source) Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [compare-target-1061-t0  ] (target) Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [loader-source-t0-i1     ] Start repository loader thread 
[2026-02-06 01:39:42] [INFO   ] [loader-source-t0-i2     ] Start repository loader thread 
[2026-02-06 01:39:42] [INFO   ] [loader-target-t0-i1     ] Start repository loader thread 
[2026-02-06 01:39:42] [INFO   ] [loader-target-t0-i2     ] Start repository loader thread 
[2026-02-06 01:39:42] [INFO   ] [loader-source-t0-i2     ] Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [loader-target-t0-i1     ] Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [loader-source-t0-i1     ] Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [loader-target-t0-i2     ] Connecting to repository database 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [compare-target-1061-t0  ] (target) Connecting to database 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [compare-source-1061-t0  ] (source) Connecting to database 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (target) 
[2026-02-06 01:39:42] [INFO   ] [connection              ] Successfully connected to postgres database (source) 
[2026-02-06 01:39:42] [INFO   ] [compare-target-1061-t0  ] (target) Loaded 10,000 rows 
[2026-02-06 01:39:42] [INFO   ] [compare-target-1061-t0  ] (target) Wait for Observer 
[2026-02-06 01:39:44] [INFO   ] [thread-manager          ] Waiting for compare threads to complete 





[2026-02-06 01:42:32] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 10,000 rows 
[2026-02-06 01:42:32] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:42:32] [INFO   ] [compare-target-1061-t0  ] (target) Cleared by Observer 
[2026-02-06 01:42:32] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:42:33] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:34] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:36] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 1,000,000 rows 
[2026-02-06 01:42:36] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:37] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:38] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:38] [INFO   ] [compare-target-1061-t0  ] (target) Complete. Total rows loaded: 244,198 
[2026-02-06 01:42:40] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 2,000,000 rows 
[2026-02-06 01:42:40] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:42:42] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:42:42] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:43] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:45] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:46] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 3,000,000 rows 
[2026-02-06 01:42:46] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:47] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:49] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:50] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 4,000,000 rows 
[2026-02-06 01:42:50] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:42:52] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:42:53] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:54] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:55] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 5,000,000 rows 
[2026-02-06 01:42:55] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:57] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:42:58] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:00] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:01] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 6,000,000 rows 
[2026-02-06 01:43:01] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:43:02] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:43:03] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:06] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 7,000,000 rows 
[2026-02-06 01:43:07] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:08] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 8,000,000 rows 
[2026-02-06 01:43:08] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:43:13] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:43:14] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:16] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:17] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 9,000,000 rows 
[2026-02-06 01:43:17] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:19] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:20] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:21] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 10,000,000 rows 
[2026-02-06 01:43:21] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:43:25] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:43:26] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:27] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:28] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:29] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 11,000,000 rows 
[2026-02-06 01:43:30] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:31] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:32] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:33] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 12,000,000 rows 
[2026-02-06 01:43:33] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:43:40] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:43:41] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:43] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:44] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 13,000,000 rows 
[2026-02-06 01:43:44] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:45] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:46] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:48] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 14,000,000 rows 
[2026-02-06 01:43:48] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:43:48] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:43:48] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:50] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:51] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:52] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 15,000,000 rows 
[2026-02-06 01:43:52] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:53] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:55] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:56] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:43:57] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 16,000,000 rows 
[2026-02-06 01:43:57] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:44:07] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:44:07] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:08] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:10] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:11] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 17,000,000 rows 
[2026-02-06 01:44:11] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:12] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:14] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:15] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 18,000,000 rows 
[2026-02-06 01:44:15] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:44:16] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:44:16] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:18] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:19] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:20] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 19,000,000 rows 
[2026-02-06 01:44:20] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:22] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:23] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:24] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 20,000,000 rows 
[2026-02-06 01:44:24] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:44:27] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:44:27] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:28] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:29] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:31] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 21,000,000 rows 
[2026-02-06 01:44:31] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:32] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:34] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:35] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 22,000,000 rows 
[2026-02-06 01:44:35] [INFO   ] [compare-source-1061-t0  ] (source) Wait for Observer 
[2026-02-06 01:44:38] [INFO   ] [compare-source-1061-t0  ] (source) Cleared by Observer 
[2026-02-06 01:44:38] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:39] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:41] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:42] [INFO   ] [compare-source-1061-t0  ] (source) Loaded 23,000,000 rows 
[2026-02-06 01:44:42] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:43] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for Queue space 
[2026-02-06 01:44:44] [INFO   ] [compare-source-1061-t0  ] (source) Complete. Total rows loaded: 23,648,465 
[2026-02-06 01:44:44] [INFO   ] [compare-source-1061-t0  ] (source) Waiting for message queue to empty 
[2026-02-06 01:44:46] [INFO   ] [thread-manager          ] Waiting for reconcile threads to complete 
[2026-02-06 01:44:47] [INFO   ] [loader-source-t0-i1     ] Loader thread complete. 
[2026-02-06 01:44:47] [INFO   ] [loader-source-t0-i2     ] Loader thread complete. 
[2026-02-06 01:44:47] [INFO   ] [loader-target-t0-i2     ] Loader thread complete. 
[2026-02-06 01:44:47] [INFO   ] [loader-target-t0-i1     ] Loader thread complete. 
[2026-02-06 01:45:02] [INFO   ] [observer-c170-t0        ] Matched 82,685 rows 
[2026-02-06 01:45:13] [INFO   ] [observer-c170-t0        ] Staging table cleanup 
[2026-02-06 01:46:25] [INFO   ] [staging-operations      ] Findings loaded from staging table dc_source_1061_0 to main table for location: source 
[2026-02-06 01:46:39] [INFO   ] [staging-operations      ] Findings loaded from staging table dc_target_1061_0 to main table for location: target 
[2026-02-06 01:46:42] [INFO   ] [thread-manager          ] All reconciliation threads completed 
[2026-02-06 01:46:42] [INFO   ] [result-processor        ] Starting result summarization 
[2026-02-06 02:04:08] [INFO   ] [result-processor        ] Result summarization completed successfully 
[2026-02-06 02:04:08] [INFO   ] [compare-ctrl            ] Reconciliation Complete: Table = pca_aft_check_log; Status = out-of-sync; Equal = 82,685; Not Equal = 0; Missing Source = 163,315; Missing Target = 23,494,466 
[2026-02-06 02:04:08] [INFO   ] [repo-ctrl               ] Table history completed for table 1061, batch 1 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ] Summary:  
[2026-02-06 02:04:08] [INFO   ] [report-ctrl             ] Summary calculated: 23740466 total rows, 23657781 out of sync rows, 1467 seconds elapsed 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]     Table Summaries: 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]     Table: pca_aft_check_log 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Status:          out-of-sync 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Elapsed Time:    1465 seconds 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Total Rows:      23,740,466 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Equal Rows:      82,685 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Not Equal Rows:  0 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Missing Source:  163,315 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Missing Target:  23,494,466 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Throughput:      16,205 rows/second 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]   Job Summary: 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Tables Processed: 1 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Total Rows: 23,740,466 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Out of Sync Rows: 23,657,781 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Elapsed Time: 1,467 seconds 
[2026-02-06 02:04:08] [INFO   ] [display-operations      ]         Throughput: 16,183 rows/second 
[2026-02-06 02:04:08] [INFO   ] [compare-ctrl            ] Comparison operation completed successfully 
[2026-02-06 02:04:08] [INFO   ] [main                    ] Shutting down 

• pgcompare db

WITH mr AS (SELECT max(rid) rid FROM pgcompare.dc_result)
SELECT compare_start, table_name, status, equal_cnt+not_equal_cnt+missing_source_cnt+missing_target_cnt  AS total_cnt,
       equal_cnt, not_equal_cnt, missing_source_cnt, missing_target_cnt
FROM pgcompare.dc_result r
         JOIN mr ON (mr.rid = r.rid)
ORDER BY compare_start;

"compare_start"	"table_name"	"status"	"total_cnt"	"equal_cnt"	"not_equal_cnt"	"missing_source_cnt"	"missing_target_cnt"
"2026-02-06 09:39:42.250113+08"	"pca_aft_check_log"	"out-of-sync"	23740466	82685	0	163315	23494466

select count(1) from pgcompare.dc_target;

"count"
163315

select * from pgcompare.dc_target limit 10;

"tid"	"table_name"	"batch_nbr"	"pk"	"pk_hash"	"column_hash"	"compare_result"	"thread_nbr"
1061	"pca_aft_check_log"	1	"{""id"": 201328370}"	"bc14751034baff756b4da99363248f65"	"05272698b63c81879492f15abab3ec56"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201294865}"	"c6f5977834fe34e6d81aa0ce2f97371e"	"790e2c407dd1c8e0765904d8459da457"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201181023}"	"fb4ccbd1e359b52c59fd3083171afa6d"	"9f63c7b85f4081f313e56f34dba977cd"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201268371}"	"1d0c32fe99d97f95d177b4b1172a403e"	"eec47d5f31a7ddab717a9c4fc2cb3fd4"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201211553}"	"2a3d497642ad5ae3f3324c98c935236b"	"77587b425d8dc7bb263148b2228ad35c"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201210435}"	"7aee50990e9a63166aaadbc87135fd88"	"a64257bcbe4940397068e9fee6bd3e26"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201367046}"	"fb728b452bceb492eed95ba54a940eb3"	"1c5e881743e1f40740a6bdfd325c712a"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201192993}"	"42ff91c8fdd09a8a071fced52d6cdc5b"	"d3b8168c308b0011ba18f5e66e5824bd"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201191696}"	"cbadc1470f9cbefa285d095363c0936e"	"9738971f08fee646b6c32e28a60a12e8"	"m"	0
1061	"pca_aft_check_log"	1	"{""id"": 201353429}"	"b17c3e51f43c14b83c5eb483e9723dc9"	"5bf53d50a11bb05c53293d96390cf6be"	"m"	0

• target db

not missing

select * from fis.pca_aft_check_log where id = 201328370;

"id"	"pdline"	"wc"	"mcbsno"	"ispass"	"remark"	"badgeno"	"cdt"	"snoid"	"testcount"	"fixno"	"inputtype"	"refid"
201328370	"P22"	"24"	"IV61GNAN99"	"1"	"CT262040W2"	"IEC141200"	"2026-02-04 12:44:43.953"	32068992	1		"M"	0

• source db

select * from fis.pca_aft_check_log where id = 201328370;

"id"	"pdline"	"wc"	"mcbsno"	"ispass"	"remark"	"badgeno"	"cdt"	"snoid"	"testcount"	"fixno"	"inputtype"	"refid"
201328370	"P22"	"24"	"IV61GNAN99"	"1"	"CT262040W2"	"IEC141200"	"2026-02-04 12:44:43.953"	32068992	1		"M"	0

[cobolbaby]

@cbrianpace Could you please respond to the issue? I really can’t figure out what’s causing the comparison results to be incorrect.

[cbrianpace]

Can you attaché the DDL for both the source and target table?

[cbrianpace]

Also, for the job above, consider turning off loader threads (setting loader-threads=0). If you need the parallel loading, then you will want to increase message-queue-size to 1000 or even higher. Right now the compare is spending most of its time "Waiting for Queue space".

Looking back at the log in more detail, it appears that the target is only loading 244,198 rows while the source is loading 23,648,465 rows:
[2026-02-06 01:42:38] [INFO ] [compare-target-1061-t0 ] (target) Complete. Total rows loaded: 244,198
[2026-02-06 01:44:44] [INFO ] [compare-source-1061-t0 ] (source) Complete. Total rows loaded: 23,648,465

So it looks like the query is returning different number of rows from the fis.pca_aft_check_log table. By chance is there in row level security settings on the target?

[cbrianpace]

Still awaiting requested material.

[cbrianpace]

Any update?

[cobolbaby]

The source and target table structures should theoretically be exactly the same, and I have verified this again.

I tried running compare many times, and each time it reported various differences, sometimes quite a large number. However, when I sampled and verified them manually, most of them turned out to be false positives. I’m not sure what is causing these false positives—it’s really strange.

When I run check to validate those reported differences, it seems to take up to 1000 records from the result set and compare them again. I noticed that many of those records are then reported as equal after the check.

Now I’m completely confused. 😅

The following are the results from the two most recent runs — one from compare and the other from check.

[cobolbaby]

Here’s what I’ve found so far—just sharing my observations.

I picked a record from dc_source where compare_status = 'm'. Then I searched for this record on both the source and target sides. The data exists on both sides, and the raw records look correct. And when I apply the compare SQL to generate the column hash, the resulting hashes are the same.

• pgcompare db

• source db

• target db

[cobolbaby]

In the latest test, I set loader-threads to 0, and no other configurations were changed.

In this test, after sampling and verification, the results were correct. It seems that introducing load-thread caused some unexpected issues.

bash-4.4$ java -jar pgcompare.jar compare -t pca_aft_check_log
Picked up _JAVA_OPTIONS: 
[2026-03-15 14:09:44] [INFO   ] [signal-handler          ] Signal handlers registered: SIGINT (graceful), SIGTERM (immediate), SIGHUP (reload) 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Starting - rid: 1773583783929 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Version: 0.6.0.0 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Batch Number: 0 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Connecting to repository database 
[2026-03-15 14:09:44] [INFO   ] Snowflake JDBC Driver 4.0.1 registered successfully 
[2026-03-15 14:09:44] [INFO   ] Initializing Snowflake JDBC Driver... 
[2026-03-15 14:09:44] [INFO   ] Arrow result format enabled successfully 
[2026-03-15 14:09:44] [INFO   ] Snowflake JDBC Driver initialization complete 
[2026-03-15 14:09:44] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-03-15 14:09:44] [INFO   ] [preflight-util          ] Performing Preflight checks for source 
[2026-03-15 14:09:44] [INFO   ] [preflight-util          ] Performing Preflight checks for target 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Parameters:  
[2026-03-15 14:09:44] [INFO   ] [main                    ]   action=compare 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   batch=0 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   batch-commit-size=2000 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   batch-fetch-size=2000 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   batch-progress-report-size=1000000 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   column-hash-method=database 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   config=/etc/pgcompare/pgcompare.properties 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   config-file=/etc/pgcompare/pgcompare.properties 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   database-sort=true 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   fix=false 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   float-cast=notation 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   float-scale=3 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   genReport=false 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   home=/opt/pgcompare 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   isCheck=false 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   loader-threads=0 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   log-destination=stdout 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   log-level=INFO 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   mappingFile= 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   message-queue-size=100 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   mode=all 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   number-cast=notation 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   observer-throttle=true 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   observer-throttle-size=2000000 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   observer-vacuum=true 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   overwrite=false 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   project=1 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-dbname=pgcompare 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-host=10.3.205.99 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-port=5499 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-schema=pgcompare 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-sslmode=disable 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   repo-user=postgres 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   reportFileName= 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-dbname=bdc 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-host=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-port=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-schema=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-sslmode=disable 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-type=postgres 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-user=postgres 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   source-warehouse=compute_wh 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   stage-table-parallel=0 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   standard-number-format=0000000000000000000000.0000000000000000000000 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   table=pca_aft_check_log 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-dbname=bdc_test 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-host=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-port=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-schema=****** 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-sslmode=disable 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-type=postgres 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-user=postgres 
[2026-03-15 14:09:44] [INFO   ] [main                    ]   target-warehouse=compute_wh 
[2026-03-15 14:09:44] [INFO   ] [main                    ] Connecting to source and target databases 
[2026-03-15 14:09:44] [INFO   ] [connection              ] Successfully connected to postgres database (source) 
[2026-03-15 14:09:44] [INFO   ] [connection              ] Successfully connected to postgres database (target) 
[2026-03-15 14:09:45] [INFO   ] [standalone-job          ] Started standalone job 631a83d3-462e-4191-b277-923d83ed0813: type=compare, pid=1, batch=0 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] Recheck Out of Sync: false 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] Retrieving tables for project 1, batch 0, table filter: pca_aft_check_log 
[2026-03-15 14:09:45] [INFO   ] [table-ctrl              ] --- START RECONCILIATION FOR TABLE: PCA_AFT_CHECK_LOG --- 
[2026-03-15 14:09:45] [INFO   ] [table-ctrl              ] Clearing data compare findings 
[2026-03-15 14:09:45] [INFO   ] [repo-ctrl               ] Data comparison results deleted for table 130 
[2026-03-15 14:09:45] [INFO   ] [column-metadata-builder ] (source) Building column expressions for fis.pca_aft_check_log 
[2026-03-15 14:09:45] [INFO   ] [column-metadata-builder ] (target) Building column expressions for fis.pca_aft_check_log 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] (source) Columns: badgeno,cdt,fixno,inputtype,ispass,mcbsno,pdline,refid,remark,snoid,testcount,wc 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] (target) Columns: badgeno,cdt,fixno,inputtype,ispass,mcbsno,pdline,refid,remark,snoid,testcount,wc 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] (source) PK Columns: id 
[2026-03-15 14:09:45] [INFO   ] [compare-ctrl            ] (target) PK Columns: id 
[2026-03-15 14:09:45] [INFO   ] [sql-syntax-srv          ] (source) Compare SQL: SELECT lower(md5(coalesce(trim(to_char(id,'0.9999999999EEEE')),' '))) AS pk_hash, '{'||'"id": ' || id||'}' as pk,lower(md5(coalesce(case when length(coalesce(trim(badgeno::text),''))=0 then ' ' else trim(badgeno::text) end,' ')||coalesce(to_char(cdt,'MMDDYYYYHH24MISS'),' ')||coalesce(case when length(coalesce(trim(fixno::text),''))=0 then ' ' else trim(fixno::text) end,' ')||coalesce(case when length(coalesce(trim(inputtype::text),''))=0 then ' ' else trim(inputtype::text) end,' ')||coalesce(case when length(coalesce(trim(ispass::text),''))=0 then ' ' else trim(ispass::text) end,' ')||coalesce(case when length(coalesce(trim(mcbsno::text),''))=0 then ' ' else trim(mcbsno::text) end,' ')||coalesce(case when length(coalesce(trim(pdline::text),''))=0 then ' ' else trim(pdline::text) end,' ')||coalesce(trim(to_char(refid,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(remark::text),''))=0 then ' ' else trim(remark::text) end,' ')||coalesce(trim(to_char(snoid,'0.9999999999EEEE')),' ')||coalesce(trim(to_char(testcount,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(wc::text),''))=0 then ' ' else trim(wc::text) end,' '))) AS column_hash FROM fis.pca_aft_check_log WHERE 1=1 AND cdt >= '2026-03-11 10:11:21.42' 
[2026-03-15 14:09:45] [INFO   ] [sql-syntax-srv          ] (target) Compare SQL: SELECT lower(md5(coalesce(trim(to_char(id,'0.9999999999EEEE')),' '))) AS pk_hash, '{'||'"id": ' || id||'}' as pk,lower(md5(coalesce(case when length(coalesce(trim(badgeno::text),''))=0 then ' ' else trim(badgeno::text) end,' ')||coalesce(to_char(cdt,'MMDDYYYYHH24MISS'),' ')||coalesce(case when length(coalesce(trim(fixno::text),''))=0 then ' ' else trim(fixno::text) end,' ')||coalesce(case when length(coalesce(trim(inputtype::text),''))=0 then ' ' else trim(inputtype::text) end,' ')||coalesce(case when length(coalesce(trim(ispass::text),''))=0 then ' ' else trim(ispass::text) end,' ')||coalesce(case when length(coalesce(trim(mcbsno::text),''))=0 then ' ' else trim(mcbsno::text) end,' ')||coalesce(case when length(coalesce(trim(pdline::text),''))=0 then ' ' else trim(pdline::text) end,' ')||coalesce(trim(to_char(refid,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(remark::text),''))=0 then ' ' else trim(remark::text) end,' ')||coalesce(trim(to_char(snoid,'0.9999999999EEEE')),' ')||coalesce(trim(to_char(testcount,'0.9999999999EEEE')),' ')||coalesce(case when length(coalesce(trim(wc::text),''))=0 then ' ' else trim(wc::text) end,' '))) AS column_hash FROM fis.pca_aft_check_log WHERE 1=1 AND cdt >= '2026-03-11 10:11:21.42' 
[2026-03-15 14:09:45] [INFO   ] [thread-manager          ] Starting compare hash threads 
[2026-03-15 14:09:46] [INFO   ] [staging-operations      ] Staging table created: dc_source_130_0 for location: source, table: 130, thread: 0 
[2026-03-15 14:09:46] [INFO   ] [staging-operations      ] Staging table created: dc_target_130_0 for location: target, table: 130, thread: 0 
[2026-03-15 14:09:46] [INFO   ] [observer-c28-t0         ] Starting reconcile observer 
[2026-03-15 14:09:46] [INFO   ] [observer-c28-t0         ] Connecting to repository database 
[2026-03-15 14:09:46] [INFO   ] [compare-source-130-t0   ] (source) Start database reconcile thread 
[2026-03-15 14:09:46] [INFO   ] [compare-target-130-t0   ] (target) Start database reconcile thread 
[2026-03-15 14:09:46] [INFO   ] [compare-source-130-t0   ] (source) Connecting to repository database 
[2026-03-15 14:09:46] [INFO   ] [compare-target-130-t0   ] (target) Connecting to repository database 
[2026-03-15 14:09:46] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-03-15 14:09:46] [INFO   ] [compare-source-130-t0   ] (source) Connecting to database 
[2026-03-15 14:09:46] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-03-15 14:09:46] [INFO   ] [connection              ] Successfully connected to postgres database (repo) 
[2026-03-15 14:09:46] [INFO   ] [compare-target-130-t0   ] (target) Connecting to database 
[2026-03-15 14:09:46] [INFO   ] [connection              ] Successfully connected to postgres database (source) 
[2026-03-15 14:09:46] [INFO   ] [connection              ] Successfully connected to postgres database (target) 
[2026-03-15 14:09:47] [INFO   ] [compare-target-130-t0   ] (target) Loaded 10,000 rows 
[2026-03-15 14:09:47] [INFO   ] [compare-target-130-t0   ] (target) Wait for Observer 
[2026-03-15 14:09:48] [INFO   ] [thread-manager          ] Waiting for compare threads to complete 
[2026-03-15 14:09:48] [INFO   ] [compare-source-130-t0   ] (source) Loaded 10,000 rows 
[2026-03-15 14:09:48] [INFO   ] [compare-source-130-t0   ] (source) Wait for Observer 
[2026-03-15 14:09:49] [INFO   ] [observer-c28-t0         ] Matched 9,283 rows 
[2026-03-15 14:09:49] [INFO   ] [compare-source-130-t0   ] (source) Cleared by Observer 
[2026-03-15 14:09:49] [INFO   ] [compare-target-130-t0   ] (target) Cleared by Observer 
[2026-03-15 14:09:50] [INFO   ] [observer-c28-t0         ] Matched 21,118 rows 
[2026-03-15 14:09:51] [INFO   ] [observer-c28-t0         ] Matched 10,672 rows 
[2026-03-15 14:09:51] [INFO   ] [observer-c28-t0         ] Matched 5,570 rows 
[2026-03-15 14:09:51] [INFO   ] [observer-c28-t0         ] Matched 4,671 rows 
[2026-03-15 14:09:52] [INFO   ] [observer-c28-t0         ] Matched 9,247 rows 
[2026-03-15 14:09:52] [INFO   ] [observer-c28-t0         ] Matched 10,906 rows 
[2026-03-15 14:09:52] [INFO   ] [observer-c28-t0         ] Matched 4,114 rows 
[2026-03-15 14:09:52] [INFO   ] [compare-source-130-t0   ] (source) Complete. Total rows loaded: 390,749 
[2026-03-15 14:09:53] [INFO   ] [observer-c28-t0         ] Matched 6,491 rows 
[2026-03-15 14:09:53] [INFO   ] [observer-c28-t0         ] Matched 8,090 rows 
[2026-03-15 14:09:53] [INFO   ] [observer-c28-t0         ] Matched 9,798 rows 
[2026-03-15 14:09:54] [INFO   ] [observer-c28-t0         ] Matched 10,331 rows 
[2026-03-15 14:09:54] [INFO   ] [observer-c28-t0         ] Matched 10,792 rows 
[2026-03-15 14:09:55] [INFO   ] [observer-c28-t0         ] Matched 10,770 rows 
[2026-03-15 14:09:55] [INFO   ] [observer-c28-t0         ] Matched 14,336 rows 
[2026-03-15 14:09:56] [INFO   ] [observer-c28-t0         ] Matched 14,281 rows 
[2026-03-15 14:09:56] [INFO   ] [observer-c28-t0         ] Matched 13,238 rows 
[2026-03-15 14:09:57] [INFO   ] [observer-c28-t0         ] Matched 12,628 rows 
[2026-03-15 14:09:57] [INFO   ] [observer-c28-t0         ] Matched 16,558 rows 
[2026-03-15 14:09:58] [INFO   ] [observer-c28-t0         ] Matched 12,516 rows 
[2026-03-15 14:09:59] [INFO   ] [observer-c28-t0         ] Matched 20,559 rows 
[2026-03-15 14:10:00] [INFO   ] [observer-c28-t0         ] Matched 20,780 rows 
[2026-03-15 14:10:00] [INFO   ] [compare-target-130-t0   ] (target) Complete. Total rows loaded: 390,749 
[2026-03-15 14:10:00] [INFO   ] [thread-manager          ] Waiting for reconcile threads to complete 
[2026-03-15 14:10:01] [INFO   ] [observer-c28-t0         ] Matched 27,631 rows 
[2026-03-15 14:10:01] [INFO   ] [observer-c28-t0         ] Matched 7,924 rows 
[2026-03-15 14:10:05] [INFO   ] [observer-c28-t0         ] Staging table cleanup 
[2026-03-15 14:10:05] [INFO   ] [staging-operations      ] Findings loaded from staging table dc_source_130_0 to main table for location: source 
[2026-03-15 14:10:05] [INFO   ] [staging-operations      ] Findings loaded from staging table dc_target_130_0 to main table for location: target 
[2026-03-15 14:10:06] [INFO   ] [thread-manager          ] All reconciliation threads completed 
[2026-03-15 14:10:06] [INFO   ] [result-processor        ] Starting result summarization 
[2026-03-15 14:10:09] [INFO   ] [result-processor        ] Reconciliation stats for tid=130: missingSource=0, missingTarget=0, notEqual=98445 
[2026-03-15 14:10:09] [INFO   ] [result-processor        ] Database results for cid=28: equal=292304, sourceCount=390749, targetCount=390749, missingSource=0, missingTarget=0, notEqual=98445 
[2026-03-15 14:10:09] [INFO   ] [result-processor        ] Result summarization completed successfully 
[2026-03-15 14:10:09] [INFO   ] [compare-ctrl            ] Reconciliation Complete: Table = pca_aft_check_log; Status = out-of-sync; Equal = 292,304; Not Equal = 98,445; Missing Source = 0; Missing Target = 0 
[2026-03-15 14:10:09] [INFO   ] [repo-ctrl               ] Table history completed for table 130, batch 1 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ] Summary:  
[2026-03-15 14:10:09] [INFO   ] [report-ctrl             ] Summary calculated: 390749 total rows, 98445 out of sync rows, 25 seconds elapsed 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]     Table Summaries: 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]     Table: pca_aft_check_log 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Status:          out-of-sync 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Elapsed Time:    23 seconds 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Total Rows:      390,749 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Equal Rows:      292,304 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Not Equal Rows:  98,445 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Missing Source:  0 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Missing Target:  0 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Throughput:      16,989 rows/second 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]   Job Summary: 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Tables Processed: 1 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Total Rows: 390,749 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Out of Sync Rows: 98,445 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Elapsed Time: 25 seconds 
[2026-03-15 14:10:09] [INFO   ] [display-operations      ]         Throughput: 15,629 rows/second 
[2026-03-15 14:10:09] [INFO   ] [compare-ctrl            ] Comparison operation completed successfully 
[2026-03-15 14:10:09] [INFO   ] [standalone-job          ] Job 631a83d3-462e-4191-b277-923d83ed0813 completed with status: completed 
[2026-03-15 14:10:09] [INFO   ] [main                    ] Shutting down 

[cobolbaby]

I’ve compared all 14 records and share the comparison results with loader-threads set to 0.

I also sampled this comparison result, and it appears to be accurate.

[cbrianpace]

Great detail and feedback. Investigating.

[cbrianpace]

Fix has been pushed into the pending PR (#98). Can you verify this resolves the issue before we merge into main?