Issue with Timestamp and dbsync (or geodiff) #145

Activity

1. wonder-sk commented on Oct 21, 2024

   

   wonder-sk

   on Oct 21, 2024

   Contributor

   More actions

   It looks like a bug in geodiff. The "UPDATE" command generated by geodiff is wrong, because there is nothing between "SET" and "WHERE" keywords. What kind of timestamp column is it - with or without timezone?

   To further diagnose the issue, it would be useful to have:

   1. the original "CREATE TABLE" command for the address_points postgresql table
   2. geopackage from Mergin Maps that contains the same address_points table, with a new point recorded.
2. geographit2 commented on Apr 29, 2025

   

   geographit2

   on Apr 29, 2025

   Last edited by geographit2

   More actions

   I confirm the issue.

   CREATE TABLE db_sync.points (
     fid serial primary key,
     name text,
     rating integer, 
     last_update  timestamp without time zone DEFAULT CURRENT_TIMESTAMP, 
     geom geometry(Point, 4326)
   );
   
   CREATE OR REPLACE FUNCTION public.update_last_update()
       RETURNS trigger
       LANGUAGE 'plpgsql'
       COST 100
       VOLATILE NOT LEAKPROOF
   AS $BODY$
   BEGIN
      NEW.last_update = now(); 
      RETURN NEW;
   END;
   $BODY$;

   The trigger creates the type of error described above.

   Any trigger seem to cause the error, not necessarily one related to updated a timestamp value.
3. wonder-sk commented on Oct 20, 2025

   

   wonder-sk

   on Oct 20, 2025

   Contributor

   More actions

   It turns out the issue is due to the fact that now() or current_timestamp return time with microsecond precision, however in geodiff, we currently work with millisecond precision, and in one place this is not handled correctly, causing issues.

   Workaround: use now()::timestamp(3) in PostgreSQL code instead of now() to force millisecond precision to avoid this bug.

   Related: MerginMaps/geodiff#188

   Cause: in postgres driver, when looking for changes between two tables (in sqlFindModified()), the generated select statement is not entirely correct - outputting a row with two equal values (allColumnNames() gives values with millisecond accuracy, but WHERE clause checks two timestamp columns for exact equality - the "modified" table has full 6-digit precision why "base" table only has 3-digit precision).

   Suggested fix: either we should switch to 6-digit precision of timestamps everywhere (if possible) or fix the WHERE clause to do equality test with 3-digit precision.

   Minimal example to replicate (no need for triggers):

   CREATE SCHEMA bug_145;
   
   CREATE TABLE bug_145.points (
     fid serial primary key,
     name text,
     last_update  timestamp without time zone,
     geom geometry(Point, 4326)
   );
   
   INSERT INTO bug_145.points VALUES (1, 'hello', now(), ST_Point(100,200));

   👍React with 👍1Reacted by geographit2
4. wonder-sk commented on Jan 12, 2026

   

   wonder-sk

   on Jan 12, 2026

   Contributor

   More actions

   Fixed on master (will be db-sync 2.2.0)
5. wonder-sk

   closed this as completedon Jan 12, 2026