sync over HTTP does not trigger function on postgres

[federicoMoroni-git]

my client app uses a fallback method that uses a sync over HTTP if the TCP sync has some issues (poor connection for example).
my server is a postgres database with a Postgis enabled extension.
one column of my table geom is excluded from the sync configuration and calculated directly on the postgres via a trigger function:

CREATE TRIGGER trig_update_geometry BEFORE INSERT OR UPDATE ON trackplot_cog FOR EACH ROW EXECUTE FUNCTION compute_geometry()

BEGIN
    NEW.geom := ST_SetSRID(ST_MakePoint(NEW.long, NEW.lat), 4326);
    RETURN NEW;
END;

now the issue is that with the tcp sync the entries on my postgres are correcly showing the geom field, while when the sync is performed over HTTP the geom column stays empity.

I am afraid that the function is not triggered when the sync is performed over HTTP.

I use the same setup for both TCP and HTTP sync. here some code from my server web API

        private static void ConfigureSyncScopes(IServiceCollection services, string connectionString, SyncOptions options)
        {
            var provider = new NpgsqlSyncProvider(connectionString);

            // Configure trackplot_cog_sync_0_0_1 scope
            var trackplotSetup = new SyncSetup();
            var trackplotTable = trackplotSetup.Tables.Add("trackplot_cog", "sac");
            trackplotTable.Columns.AddRange(new string[]
            {
                "gid", "id", "timestamp", "vessel_name", "lat", "long", "height_h",
                "coord_x", "coord_y", "coord_z", "heading", "course", "speed",
                "roll", "pitch", "heave", "bathy", "dal", "dol", "runline_name",
                "recording_state", "last_modification_dt", "project_code"
            });
            trackplotTable.SyncDirection = SyncDirection.UploadOnly;
            services.AddSyncServer(provider, trackplotSetup, options, null, "trackplot_cog_sync_0_0_0"); 

and from my client (MAUI app)

        public async Task<HttpSyncResult> PerformHttpSyncForConfiguration(SyncConfiguration config)
        {
            var result = new HttpSyncResult
            {
                TableName = config.Name,
                SyncTime = DateTime.Now
            };

            try
            {
                Debug.WriteLine($"[HttpSyncService] Starting HTTP sync for {config.Name}");
                
                // Create HTTP orchestrator
                var serverOrchestrator = new WebRemoteOrchestrator(_webApiUrl);
                
                // Use the same setup and options from the configuration
                var options = new SyncOptions
                {
                    BatchSize = 1000,
                    DisableConstraintsOnApplyChanges = true,
                    ProgressLevel = SyncProgressLevel.Debug,
                    DbCommandTimeout = 600,
                };

                // Create progress handler
                var progress = new Progress<ProgressArgs>(args =>
                {
                    Debug.WriteLine($"[HttpSyncService] {config.Name}: {args.ProgressPercentage}% - {args.Message}");
                });

                // Create client provider
                var clientProvider = new SqliteSyncProvider(config.SqliteDbPath);

                // Create sync agent
                var agent = new SyncAgent(clientProvider, serverOrchestrator, options);
                
                // Map configuration name to HTTP scope name
                var httpScopeName = GetHttpScopeNameForConfiguration(config.Name);
                
                // Perform sync
                var syncResult = await agent.SynchronizeAsync(
                    scopeName: httpScopeName,
                    setup: config.Setup,
                    progress: progress
                );

[Mimetis]

Based on this example https://github.com/Mimetis/Dotmim.Sync/tree/master/Samples/HelloWebSync , can you create a simple github repository that will reproduce your error ?
Dont forget to add a little sql file to be able to create at least the server side database.

[federicoMoroni-git]

thanks for you answer Sebastien, I`ve published a repo here where you should be able to reproduce the issue
REPO

[Mimetis]

Hello @federicoMoroni-git

Thanks a ton for the detailed issue reproduction.
I had to make some small adjustements to make it working correctly (installing postgis, executing SET search_path = public, postgis, sac; and a little bit of code refactoring) but at the end I made it working correctly

And I think the error is coming from an option you are using : DisableConstraintsOnApplyChanges = true

In a nutshell, this commands disable the triggering process from postgres:

 CommandText = $"ALTER TABLE {this.NpgsqlObjectNames.TableQuotedFullName}  DISABLE TRIGGER ALL;",

Can you try using this option set to false (on the server side) ?

[federicoMoroni-git]

Hi Sebastien,

thanks for your quick answer, indeed thats the issue;
AI already suggested me to set DisableConstraintsOnApplyChanges = false but I did it only on the client side and it didn`t fix the issue.

I tried again by setting it to false either on client and server and now the trigger works fine.

it was an easy fix, thanks for the time spent helping me
Cheers

[Mimetis]

AI already suggested me to set DisableConstraintsOnApplyChanges = false

Really ??? Awesome :D