MerginMaps
Floating-point precision issue in UPDATE statements for double precision columns
Problem Description
A database conflict occurs when updating columns of type double precision. This issue originates from the geodiff library, which generates SQL UPDATE statements that use full floating-point precision in the WHERE clause, causing the update to fail.
When a double value is changed (e.g., from 32.8 to 33.8), the mergin-db-sync tool, which uses geodiff, produces a conflict.
Error Log from mergin-db-sync:
mergin-db-sync | Warn: CONFLICT: update_nothing:
mergin-db-sync | {
mergin-db-sync | "changes": [
mergin-db-sync | {
mergin-db-sync | "column": 11,
mergin-db-sync | "new": 33.8,
mergin-db-sync | "old": 32.8
mergin-db-sync | },
mergin-db-sync | {
mergin-db-sync | "column": 76,
mergin-db-sync | "old": 4214
mergin-db-sync | }
mergin-db-sync | ],
mergin-db-sync | "table": "stabla",
mergin-db-sync | "type": "update"
mergin-db-sync | }
mergin-db-sync | Warn: Wrong number of affected rows! Expected 1, got: 0
mergin-db-sync | SQL: UPDATE "zk_base"."stabla" SET "visina" = 33.799999999999997 WHERE "visina" = 32.799999999999997 AND "id_stablo" = 4214
Root Cause Analysis
The issue is caused by the WHERE clause in the generated SQL statement. Comparing floating-point numbers using exact equality (=) is unreliable because the stored binary representation of a number like 32.8 might not be exactly 32.799999999999997.
The UPDATE statement correctly includes the table's primary key ("id_stablo" = 4214) to identify the row. However, the additional condition on the value being updated ("visina" = 32.799999999999997) is intended as a safeguard but fails for double types due to precision mismatches.
The code responsible for this is located in the editTable function within the database drivers.
We are using latest docker db-sync image, and have done initial sync from pg. And that is perhaps another problem.
Pg - visina numeric(10, 2)
GPKG - visina REAL
Thanks a lot for the detailed bug report!
NUMERIC type in postgresql allows arbitrary precision numbers, which indeed do not match very well the representation using the usual floating point numbers (4-byte floats or 8-byte doubles). Do you have a particular reason for using NUMERIC instead of REAL or DOUBLE PRECISION types? (the latter has precision of 15+ decimal digits, so it should be more than enough)
For geodiff to handle NUMERIC type properly, we would probably to store the numbers as strings in SQLite (SQLite also has "decimal" extension that does this approach, but it is unclear if it is available in most builds). Other pieces of software may also have trouble dealing with NUMERIC type, so unless really needed, I would suggest to use the standard REAL / DOUBLE PRECISION types...
Ok, that was our first idea, to change pg type to REAL and force init again. Thanks!
@wonder-sk but we have the same error using real. So changeset is formed correctly but SQL uses wrong representation.
mergin-db-sync-tc | Warn: CONFLICT: update_nothing:
mergin-db-sync-tc | {
mergin-db-sync-tc | "changes": [
mergin-db-sync-tc | {
mergin-db-sync-tc | "column": 11,
mergin-db-sync-tc | "new": 25.8,
mergin-db-sync-tc | "old": 25.7
mergin-db-sync-tc | },
mergin-db-sync-tc | {
mergin-db-sync-tc | "column": 66,
mergin-db-sync-tc | "new": "myself",
mergin-db-sync-tc | "old": null
mergin-db-sync-tc | },
mergin-db-sync-tc | {
mergin-db-sync-tc | "column": 76,
mergin-db-sync-tc | "old": 2474
mergin-db-sync-tc | }
mergin-db-sync-tc | ],
mergin-db-sync-tc | "table": "stabla",
mergin-db-sync-tc | "type": "update"
mergin-db-sync-tc | }
mergin-db-sync-tc | Warn: Wrong number of affected rows! Expected 1, got: 0
mergin-db-sync-tc | SQL: UPDATE "public"."stabla" SET "visina" = 25.800000000000001, "user_name" = 'myself' WHERE "visina" = 25.699999999999999 AND "user_name" IS NULL AND "id_stablo" = 2474
Pg - float4, and then force init back to gpkg:
Basically you cannot UPDATE anything having REAL in GPKG and sync that to PostgreSQL.
Update, float8 works... But still strange. Is that extra WHERE necessary at all?