wonder-sk
commented
3 days ago 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:
- the original "CREATE TABLE" command for the
address_pointspostgresql table - geopackage from Mergin Maps that contains the same
address_pointstable, with a new point recorded.
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?