unable to sync local sqlite with postgres table does not have any primary key

[federicoMoroni-git]

I'm attempting to synchronize a SQLite database with a PostgreSQL database using Dotmim.Sync. Here are the details of my setup:

Database Providers:

Client (SQLite): new SqliteSyncProvider(@"Data Source=C:\Desktop\test.db")
Server (PostgreSQL): new NpgsqlSyncProvider(serverConnectionString)

Table Structures:
SQLite table (client):
CREATE TABLE "trackplot_cog" (
    "gid"	INTEGER PRIMARY KEY AUTOINCREMENT,
    "id"	INTEGER,
    "timestamp"	TEXT,
    "name"	TEXT,
    "lat"	REAL,
    "long"	REAL
);
PostgreSQL table (server):
CREATE TABLE dataengine.trackplot_cog (
    gid serial primary key,
    id integer,
    "timestamp" timestamp,
    name character varying(255),
    lat numeric (38,8),
    long numeric (38,8)
);

Synchronization Method:
I'm initiating the sync process using the following code:

var syncAgent = new SyncAgent(clientProvider, serverProvider);
var setup = new SyncSetup("dataengine.trackplot_cog");
var result = await syncAgent.SynchronizeAsync(setup);

Error:

When I run the synchronization, I receive the following error:
CopySynchronization error:
[InternalEnsureScopelnfoAsync].Overwrite:False .. [InternalGetSchemaAsync] .. 
[InternalGetTableSchemaAsync].Table:dataengine.trackplot_cog .. 
[SetPrimaryKeysAsync].Table:dataengine.trackplot_cog .. 
Table trackplot_cog does not have any primary key.

Stack Trace: at Dotmim.Sync.SyncAgent.SynchronizeAsync(String scopeName, 
SyncSetup setup, SyncType syncType, SyncParameters parameters, 
CancellationToken cancellationToken, IProgress`1 progress)
at SyncService.cs:line 76

Dotmim.Sync is unable to detect the primary key in the dataengine.trackplot_cog table, despite both tables having a primary key defined on the gid column.

Why is Dotmim.Sync failing to recognize the primary key in my PostgreSQL table?
Are there any additional configuration steps I need to take to ensure proper primary key detection?
Could there be any issues with schema detection or naming conventions that I'm overlooking?

[Mimetis]

Weird, it's working for me ...

here is the code I used:

Creating the database on PostgreSQL

create schema dataengine;

create table dataengine.trackplot_cog (
    gid serial primary key,
    id integer,
    "timestamp" timestamp,
    name character varying(255),
    lat numeric (38,8),
    long numeric (38,8)
);

Making a sync (that will create automatically the SQLite database):

var serverProvider = new NpgsqlSyncProvider(DBHelper.GetNpgsqlDatabaseConnectionString("data"));
var clientProvider = new SqliteSyncProvider(Path.GetRandomFileName().Replace(".", "").ToLowerInvariant() + ".db");

var setup = new SyncSetup("dataengine.trackplot_cog");
var options = new SyncOptions();

options.DisableConstraintsOnApplyChanges = true;

var progress = new SynchronousProgress<ProgressArgs>(s =>
    Console.WriteLine($"{s.ProgressPercentage:p}:  " +
    $"\t[{s?.Source?[..Math.Min(4, s.Source.Length)]}] {s.TypeName}: {s.Message}"));
do
{
    try
    {
        Console.ForegroundColor = ConsoleColor.Green;
        var s = await agent.SynchronizeAsync(scopeName, setup, progress:progress);
        Console.WriteLine(s);
    }
    catch (SyncException e)
    {
        Console.ResetColor();
        Console.WriteLine(e.Message);
    }
    catch (Exception e)
    {
        Console.ResetColor();
        Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
    }

    Console.WriteLine("--------------------");
}
while (Console.ReadKey().Key != ConsoleKey.Escape);

Questions

What PostgreSQL version are you using ?

For your specific error, DMS is using this query to get all the primary keys:

SELECT 	CCU.COLUMN_NAME,
    	TC.CONSTRAINT_NAME,
    	C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_SCHEMA = TC.CONSTRAINT_SCHEMA
AND TC.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TC.TABLE_NAME = 'trackplot_cog'
    AND TC.TABLE_SCHEMA = 'dataengine';

Is it working for you ?

[federicoMoroni-git]

edit:

postgres version "server_version" "16.3"
some details worth mentioning before the rest

1. I am actually trying to do the opposite. changes are happening in my local sqlite and I want to keep the remote postgres updated,
2. I am usign postgis. I already saw that dotmim is not compatible with geom data for example. but can I simly sync the regular fields of my table and keep the geom generation on the server side?

end edit

thanks for the quick and clear response. the issue that I am encountering is indeed quite strange.
if I query my table to check the primary key

SELECT 
    kcu.column_name
FROM 
    information_schema.table_constraints tc
JOIN 
    information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE 
    tc.constraint_type = 'PRIMARY KEY' 
    AND tc.table_name = 'trackplot_cog'
    AND tc.table_schema = 'dataengine';

my result is

"gid"

but if I apply the code you posted which is in the method to check the prim key for dotmim sync

SELECT 	CCU.COLUMN_NAME,
    	TC.CONSTRAINT_NAME,
    	C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_SCHEMA = TC.CONSTRAINT_SCHEMA
AND TC.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TC.TABLE_NAME = 'trackplot_cog'
    AND TC.TABLE_SCHEMA = 'dataengine';

result is
nothing

after some debugging it seems that the problem could be due to this

Let's analyze the findings:

The primary key constraint exists and is named "trackplot_cog_pkey".
The TABLE_CONSTRAINTS view shows the primary key constraint.
Surprisingly, the CONSTRAINT_COLUMN_USAGE view returns nothing for your table.
You have SELECT privileges on the information schema views.
You don't have permissions to grant privileges or alter the table structure.

The key issue here is that the CONSTRAINT_COLUMN_USAGE view is empty for your table. This is likely why Dotmim.Sync can't detect the primary key. The discrepancy between TABLE_CONSTRAINTS and CONSTRAINT_COLUMN_USAGE suggests there might be an inconsistency in the database metadata.

as you can imagine I am not a database expert. I wonder if this is a bug of dotmim sync or an underlying structural problem of my postgres database implementation.

I will contact the admin and ask to manually reset the prim key

ALTER TABLE dataengine.trackplot_cog DROP CONSTRAINT IF EXISTS trackplot_cog_pkey;
ALTER TABLE dataengine.trackplot_cog ADD CONSTRAINT trackplot_cog_pkey PRIMARY KEY (gid);

and post an update here

[Mimetis]

I can also change a little bit my query, if it's more reliable .

Something like this will work for me:

SELECT kcu.column_name, kcu.constraint_name, kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY' 
    AND tc.table_name = 'trackplot_cog'
    AND tc.table_schema = 'dataengine';

[federicoMoroni-git]

this query works on my end too

SELECT kcu.column_name, kcu.constraint_name, kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY' 
    AND tc.table_name = 'trackplot_cog'
    AND tc.table_schema = 'dataengine';

result

 "gid"	"trackplot_cog_pkey"	1

let me know if you can update the packages with this change.

would you be so keen to address this questions:
1-I am actually trying to do the opposite. changes are happening in my local sqlite and I want to keep the remote postgres updated (and viceversa. right now I am only able to update the local sqlite when the postgres one changes. I ask because now I am having this error when I add something to the sqlite

'[InternalApplyThenGetChangesAsync]..[InternalApplyThenGetChangesAsync]..[InternalApplyChangesAsync]..[InternalApplyTableChangesAsync]..42883: function pg_temp.users_update(integer, text, text, timestamp with time zone, uuid, bigint, bigint) does not exist

2-I am using a postgis enabled database. I already saw that dotmim is not compatible with geom data for example. but can I simply sync the regular fields of my table and keep the geom generation on the server side?

[Mimetis]

1-I am actually trying to do the opposite.

DMS is bidirectional by default, so It should works. Local SQLite records are uploaded to server, once you have made a first sync to initialize everything

2-I am using a postgis enabled database.

You can specify the columns you want to sync. Just omit the ones that are not supported https://dotmimsync.readthedocs.io/Configuration.html#filtering-columns

[federicoMoroni-git]

I`m sorry but your implementation works only in one direction (from postgres to sqlite). if I insert some rows in the sqlite I always have this error

0,00%:          [main] SessionBeginArgs: Session Begins. Id:b9f1ceaa-7f1b-43c2-9979-e6825df848bd. Scope name:scope1.
0,00%:          [gtec] SessionBeginArgs: Session Begins. Id:b9f1ceaa-7f1b-43c2-9979-e6825df848bd. Scope name:scope1.
10,00%:         [main] TableChangesSelectedArgs: [trackplot_cog] [Total] Upserts:3. Deletes:0. Total:3.
10,00%:         [main] BatchChangesCreatedArgs: [dataengine.trackplot_cog] [Modified] Batch dataengine_trackplot_cog_0000_UPSERTS_fvhxuo4k_aou.json (1) Created.
30,00%:         [main] DatabaseChangesSelectedArgs: [Total] Upserts:3. Deletes:0. Total:3. [main_LOCAL_GETCHANGES_2024_08_10_09agmc04jfzgv]
100,00%:        [main] SessionEndArgs: Session Ends. Id:b9f1ceaa-7f1b-43c2-9979-e6825df848bd. Scope name:scope1.
100,00%:        [gtec] SessionEndArgs: Session Ends. Id:b9f1ceaa-7f1b-43c2-9979-e6825df848bd. Scope name:scope1.
[InternalApplyThenGetChangesAsync]..[InternalApplyThenGetChangesAsync]..[InternalApplyChangesAsync]..[InternalApplyTableChangesAsync]..42883: function pg_temp.trackplot_cog_scope1_update(integer, integer, timestamp with time zone, text, numeric, numeric, uuid, bigint, bigint) does not exist

POSITION: 15

I used your same exact code, just using a fixed reference for the sqlite to be able to use a single source for it

using Dotmim.Sync;
using Dotmim.Sync.PostgreSql;
using Dotmim.Sync.Sqlite;
using System;
using System.Security.Cryptography.X509Certificates;
using System.Threading.Tasks;

namespace SyncApp
{
    class Program
    {

        static async Task Main(string[] args)
        {
            var localConnectionString = "Host=localhost;Port=5432;Database=gtecdb;Username=postgres;Password=postgres";
            var serverProvider = new NpgsqlSyncProvider(localConnectionString);

             // I changed only this line ( the clientProvider path)
            //var clientProvider = new SqliteSyncProvider(Path.GetRandomFileName().Replace(".", "").ToLowerInvariant() + ".db");
            var clientProvider = new SqliteSyncProvider(@"C:\Users\0031181\source\repos\sync with dotmim\sync with dotmim\bin\Debug\net8.0\puacqwtzubu.db");

            SyncAgent agent = new SyncAgent(clientProvider, serverProvider);

            var setup = new SyncSetup("dataengine.trackplot_cog");
            var options = new SyncOptions();

            options.DisableConstraintsOnApplyChanges = true;

            var progress = new SynchronousProgress<ProgressArgs>(s =>
                Console.WriteLine($"{s.ProgressPercentage:p}:  " +
                $"\t[{s?.Source?[..Math.Min(4, s.Source.Length)]}] {s.TypeName}: {s.Message}"));
            do
            {
                try
                {
                    Console.ForegroundColor = ConsoleColor.Green;
                    var s = await agent.SynchronizeAsync("scope1", setup, progress: progress);
                    Console.WriteLine(s);
                }
                catch (SyncException e)
                {
                    Console.ResetColor();
                    Console.WriteLine(e.Message);
                }
                catch (Exception e)
                {
                    Console.ResetColor();
                    Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
                }

                Console.WriteLine("--------------------");
            }
            while (Console.ReadKey().Key != ConsoleKey.Escape);


        }
}

I`ve found a similar issue here issue 1225

EDIT: I`m trying to debug the code myself. even if the function should be added to the schema

await preCommand.ExecuteNonQueryAsync().ConfigureAwait(false);

the code brakes here

                        (command, isBatch) = await this.InternalGetCommandAsync(scopeInfo, context, syncAdapter, dbCommandType,
                                    runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);

with exception
Npgsql.PostgresException: 42883: function pg_temp.trackplot_cog_scope1_update(integer, integer, timestamp with time zone, text, numeric, numeric, uuid, bigint, bigint) does not exist