Issue with Timestamp and dbsync (or geodiff)

[northrivergeo]

I've hit this weird problem with dbsync (this is probably geodiff vs DBsync but I thought I'd start here). So I've pushed a project into mergin maps. dbsync works (docker). I collect a point in the field and......

--- push nrgsinc/mobile_rjhale2 - nothing to do
Pushed new version to Mergin Maps:
Updating DB base schema...
Error: apply changeset failed!
GEODIFF: Error: postgres cmd error: ERROR: syntax error at or near "WHERE"
LINE 1: UPDATE "tn911_db_sync"."address_points" SET WHERE "id" = 17...

SQL:
UPDATE "tn911_db_sync"."address_points" SET WHERE "id" = 17022
geodiff failed!
['geodiff', 'apply', '--driver', 'postgres', 'host=gis1 dbname=test911 user=rjhale password='*****' '--skip-tables', 'esb_law;esb_fire;esb_ems;lanes_tbl;access_tbl;addrtype_tbl;cfcc_tbl;geosrc_tbl;lanes_tbl;lifecyclestatus_tbl;nametype_tbl;oneway_tbl;predir_tbl;segside_tbl;source_tbl;strucdomain_tbl;structype_tbl;type_tbl;unit_type_tbl', 'tn911_db_sync', '/tmp/mobile_rjhale2-dbsync-push-base2our']
Going to sleep

So I did a lot of testing and it's this trigger/function in the database:

CREATE OR REPLACE FUNCTION tn911.address_gpsdate()
RETURNS TRIGGER AS $$
BEGIN
NEW.gpsdate = current_timestamp;
RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER update_address_gpsdate BEFORE insert
ON tn911.address_points FOR EACH ROW EXECUTE PROCEDURE
tn911.address_gpsdate();

I build a completely different table with one trigger which was the timestamp and it failed. Remove timestamp and it works. I need the timestamp as part of the data collection exercise.

Thoughts - Suggestions?

[wonder-sk]

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.

[geographit2]

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.

[wonder-sk]

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));

[wonder-sk]

Fixed on master (will be db-sync 2.2.0)