search

koordinates / kart

Distributed version-control for geospatial and tabular data
https://kartproject.org
Other
515 stars 39 forks source link

Kart is slow when using PostGIS working copy #968

Open SrNetoChan opened 5 months ago

SrNetoChan commented 5 months ago

Describe the bug I find kart quite slow when using a PostGIS working copy on a remote server. My QGIS project have many layers +100 all tracked by kart. But it feels snappy enough when I am zooming, panning editing.

I noticed that the _kart_track table stores the tables and features that suffered changes. Nevertheless, even if I only have changed one table, when asked to see the global working changes or running git status. it takes lot's of time to return an answer.

I wonder what queries are being made to the database at that point and if they could be improved somehow or if there are indexes missing somewhere.

Output Add the output you're seeing to help explain your problem.

Version Info

rcoup commented 4 months ago

Hi @SrNetoChan

Can you run the commands with increased verbosity -vvv and see if it logs any more useful information?

SrNetoChan commented 4 months ago

I have tried kart -vvv status it took a lot and returned this:

On branch main

Changes in working copy:
  (use "kart commit" to commit)
  (use "kart restore" to discard changes)

  constru_linear:
    feature:
      548 inserts
      156 updates
      68 deletes
  edificio:
    feature:
      5 updates
      14 deletes
  lig_valor_utilizacao_atual_edificio:
    feature:
      238 updates
      15 deletes
  ponto_interesse:
    feature:
      339 inserts

No extra information was provided.

olsen232 commented 4 months ago

Apologies for the awkwardness of getting some logging output. Here is a script that Kart can run:

import logging
from kart.status import status

logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)

def main(ctx, args):
    ctx.invoke(status)

All this script does is turn on extra debug output, and then run kart status. You can run this script by putting it in your kart repository, calling it script.py, and then from the same folder, running: KART_USE_HELPER=0 kart ext-run script.py

This should produce quite a lot of output that could help diagnose the issue, mostly SQL queries. Feel free to attach it here, or let me know if you have any trouble running the script.

SrNetoChan commented 4 months ago

Thanks for the script. It works well.

Below there's only the begining part of the log, as it seems to repeat for all the layers under tracking since there are a lot of tables, it takes a while. Full log is here:

debug.log

I am probably missing something, so please ignore if this is a non-sense suggestion.

The SQL queries seems to first search for all the tables under tracking, and then for each table found, check if it's in the public._kart_state.table_name. This will cycle trough all tracked tables, whether they have changes or not.

Suggestion: Since there are triggers on all tracked tables to change the table status in the public._kart_state.table_name, wouldn't it be more direct if the status just checks for changes in the tables present on public._kart_state.table_name? knows what other tables have been changed, does it make sense to check for changes in all tracked tables? Is this intended?

It also seems that even if the target table is not is in the kart_state table , it still proceeds to do an extra query (that I don't fully understand what it does but seems to try to see the differences between working copy changes and the last commit).

Suggestion: Since the table name is not in the kart_state table, shouldn't this be avoided?

Meanwhile, thanks for your great work, let me know if I can be of any help.


 kart git:(main) KART_USE_HELPER=0 kart ext-run debug.py                           
2024-02-27 10:35:05,772 INFO sqlalchemy.engine.Engine - select pg_catalog.version()
2024-02-27 10:35:05,773 INFO sqlalchemy.engine.Engine - [raw sql] {}
2024-02-27 10:35:05,922 INFO sqlalchemy.engine.Engine - select current_schema()
2024-02-27 10:35:05,922 INFO sqlalchemy.engine.Engine - [raw sql] {}
2024-02-27 10:35:06,070 INFO sqlalchemy.engine.Engine - show standard_conforming_strings
2024-02-27 10:35:06,070 INFO sqlalchemy.engine.Engine - [raw sql] {}
2024-02-27 10:35:06,514 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:06,515 INFO sqlalchemy.engine.Engine - SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name=%(schema_name)s;
2024-02-27 10:35:06,516 INFO sqlalchemy.engine.Engine - [generated in 0.00030s] {'schema_name': 'public'}
2024-02-27 10:35:06,592 INFO sqlalchemy.engine.Engine - 
                    SELECT COUNT(*) FROM information_schema.tables
                    WHERE table_schema=%(table_schema)s AND table_name IN (%(kart_state_name)s, %(kart_track_name)s);

2024-02-27 10:35:06,592 INFO sqlalchemy.engine.Engine - [generated in 0.00026s] {'table_schema': 'public', 'kart_state_name': '_kart_state', 'kart_track_name': '_kart_track'}
2024-02-27 10:35:06,671 INFO sqlalchemy.engine.Engine - 
                    SELECT COUNT(*) FROM information_schema.tables
                    WHERE table_schema=%(table_schema)s;

2024-02-27 10:35:06,671 INFO sqlalchemy.engine.Engine - [generated in 0.00029s] {'table_schema': 'public'}
2024-02-27 10:35:06,746 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:07,119 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:07,121 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:07,121 INFO sqlalchemy.engine.Engine - [generated in 0.00021s] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:07,196 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:07,592 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:07,593 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:07,593 INFO sqlalchemy.engine.Engine - [cached since 0.4721s ago] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:07,670 INFO sqlalchemy.engine.Engine - SELECT obj_description((%(table_identifier)s)::regclass, 'pg_class');
2024-02-27 10:35:07,670 INFO sqlalchemy.engine.Engine - [generated in 0.00029s] {'table_identifier': 'public.adm_publica'}
2024-02-27 10:35:07,746 INFO sqlalchemy.engine.Engine - SELECT * FROM pg_extension WHERE extname = 'postgis';
2024-02-27 10:35:07,746 INFO sqlalchemy.engine.Engine - [generated in 0.00020s] {}
2024-02-27 10:35:07,822 INFO sqlalchemy.engine.Engine - 
            SELECT
                C.column_name, C.ordinal_position, C.data_type, C.udt_name,
                C.character_maximum_length, C.numeric_precision, C.numeric_scale,
                PK.ordinal_position AS pk_ordinal_position,
                CASE WHEN udt_name='geometry' THEN
                        upper(postgis_typmod_type(A.atttypmod)) ELSE NULL END AS geometry_type,
                CASE WHEN udt_name='geometry' THEN
                    postgis_typmod_srid(A.atttypmod) ELSE NULL END  AS geometry_srid
            FROM information_schema.columns C
            LEFT OUTER JOIN (
            SELECT KCU.* FROM information_schema.key_column_usage KCU
            INNER JOIN information_schema.table_constraints TC
            ON KCU.constraint_schema = TC.constraint_schema
            AND KCU.constraint_name = TC.constraint_name
            WHERE TC.constraint_type = 'PRIMARY KEY'
        ) PK
            ON (PK.table_schema = C.table_schema)
            AND (PK.table_name = C.table_name)
            AND (PK.column_name = C.column_name)
            LEFT OUTER JOIN pg_attribute A
            ON (A.attname = C.column_name)
            AND (A.attrelid = (%(table_identifier)s)::regclass::oid)
            WHERE C.table_schema=%(table_schema)s AND C.table_name=%(table_name)s
            ORDER BY C.ordinal_position;

2024-02-27 10:35:07,823 INFO sqlalchemy.engine.Engine - [generated in 0.00030s] {'table_identifier': 'public.adm_publica', 'table_schema': 'public', 'table_name': 'adm_publica'}
2024-02-27 10:35:07,925 INFO sqlalchemy.engine.Engine - 
                SELECT GC.f_geometry_column AS column_name, GC.srid, SRS.srtext
                FROM geometry_columns GC
                LEFT OUTER JOIN spatial_ref_sys SRS ON (GC.srid = SRS.srid)
                WHERE GC.f_table_schema=%(table_schema)s AND GC.f_table_name=%(table_name)s;

2024-02-27 10:35:07,925 INFO sqlalchemy.engine.Engine - [generated in 0.00029s] {'table_schema': 'public', 'table_name': 'adm_publica'}
2024-02-27 10:35:08,006 WARNING kart.sqlalchemy.adapter.base - SQL type UUID not fully supported - importing as text
2024-02-27 10:35:08,011 INFO sqlalchemy.engine.Engine - SELECT public._kart_track.pk AS ".__track_pk", public.adm_publica.identificador, to_char(public.adm_publica.inicio_objeto, %(to_char_1)s) AS inicio_objeto, to_char(public.adm_publica.fim_objeto, %(to_char_2)s) AS fim_objeto, public.adm_publica.nome, public.adm_publica.ponto_de_contacto, public.adm_publica.valor_tipo_adm_publica 
FROM public._kart_track LEFT OUTER JOIN public.adm_publica ON public._kart_track.pk = CAST(public.adm_publica.identificador AS TEXT) 
WHERE public._kart_track.table_name = %(table_name_1)s
2024-02-27 10:35:08,011 INFO sqlalchemy.engine.Engine - [generated in 0.00022s] {'to_char_1': 'YYYY-MM-DD"T"HH24:MI:SS', 'to_char_2': 'YYYY-MM-DD"T"HH24:MI:SS', 'table_name_1': 'adm_publica'}
2024-02-27 10:35:08,089 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:08,462 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:08,462 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:08,463 INFO sqlalchemy.engine.Engine - [cached since 1.341s ago] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:08,537 INFO sqlalchemy.engine.Engine - SELECT obj_description((%(table_identifier)s)::regclass, 'pg_class');
2024-02-27 10:35:08,537 INFO sqlalchemy.engine.Engine - [cached since 0.8678s ago] {'table_identifier': 'public.agua_lentica'}
2024-02-27 10:35:08,613 INFO sqlalchemy.engine.Engine - SELECT * FROM pg_extension WHERE extname = 'postgis';
2024-02-27 10:35:08,613 INFO sqlalchemy.engine.Engine - [cached since 0.8671s ago] {}
2024-02-27 10:35:08,691 INFO sqlalchemy.engine.Engine - 
            SELECT
                C.column_name, C.ordinal_position, C.data_type, C.udt_name,
                C.character_maximum_length, C.numeric_precision, C.numeric_scale,
                PK.ordinal_position AS pk_ordinal_position,
                CASE WHEN udt_name='geometry' THEN
                        upper(postgis_typmod_type(A.atttypmod)) ELSE NULL END AS geometry_type,
                CASE WHEN udt_name='geometry' THEN
                    postgis_typmod_srid(A.atttypmod) ELSE NULL END  AS geometry_srid
            FROM information_schema.columns C
            LEFT OUTER JOIN (
            SELECT KCU.* FROM information_schema.key_column_usage KCU
            INNER JOIN information_schema.table_constraints TC
            ON KCU.constraint_schema = TC.constraint_schema
            AND KCU.constraint_name = TC.constraint_name
            WHERE TC.constraint_type = 'PRIMARY KEY'
        ) PK
            ON (PK.table_schema = C.table_schema)
            AND (PK.table_name = C.table_name)
            AND (PK.column_name = C.column_name)
            LEFT OUTER JOIN pg_attribute A
            ON (A.attname = C.column_name)
            AND (A.attrelid = (%(table_identifier)s)::regclass::oid)
            WHERE C.table_schema=%(table_schema)s AND C.table_name=%(table_name)s
            ORDER BY C.ordinal_position;

2024-02-27 10:35:08,691 INFO sqlalchemy.engine.Engine - [cached since 0.8685s ago] {'table_identifier': 'public.agua_lentica', 'table_schema': 'public', 'table_name': 'agua_lentica'}
2024-02-27 10:35:08,779 INFO sqlalchemy.engine.Engine - 
                SELECT GC.f_geometry_column AS column_name, GC.srid, SRS.srtext
                FROM geometry_columns GC
                LEFT OUTER JOIN spatial_ref_sys SRS ON (GC.srid = SRS.srid)
                WHERE GC.f_table_schema=%(table_schema)s AND GC.f_table_name=%(table_name)s;

2024-02-27 10:35:08,779 INFO sqlalchemy.engine.Engine - [cached since 0.8548s ago] {'table_schema': 'public', 'table_name': 'agua_lentica'}
2024-02-27 10:35:08,859 INFO sqlalchemy.engine.Engine - 
                    SELECT ST_Zmflag(geometria) AS zm,
                    ST_SRID(geometria) AS srid, SRS.srtext
                    FROM public.agua_lentica LEFT OUTER JOIN spatial_ref_sys SRS
                    ON SRS.srid = ST_SRID(geometria)
                    WHERE geometria IS NOT NULL LIMIT 1;

2024-02-27 10:35:08,859 INFO sqlalchemy.engine.Engine - [generated in 0.00037s] {}
2024-02-27 10:35:08,936 WARNING kart.sqlalchemy.adapter.base - SQL type UUID not fully supported - importing as text
2024-02-27 10:35:08,959 INFO sqlalchemy.engine.Engine - SELECT public._kart_track.pk AS ".__track_pk", public.agua_lentica.identificador, to_char(public.agua_lentica.inicio_objeto, %(to_char_1)s) AS inicio_objeto, to_char(public.agua_lentica.fim_objeto, %(to_char_2)s) AS fim_objeto, public.agua_lentica.nome, public.agua_lentica.cota_plena_armazenamento, to_char(public.agua_lentica.data_fonte_dados, %(to_char_3)s) AS data_fonte_dados, public.agua_lentica.mare, public.agua_lentica.origem_natural, public.agua_lentica.profundidade_media, public.agua_lentica.id_hidrografico, public.agua_lentica.valor_agua_lentica, public.agua_lentica.valor_persistencia_hidrologica, public.agua_lentica.geometria 
FROM public._kart_track LEFT OUTER JOIN public.agua_lentica ON public._kart_track.pk = CAST(public.agua_lentica.identificador AS TEXT) 
WHERE public._kart_track.table_name = %(table_name_1)s
2024-02-27 10:35:08,959 INFO sqlalchemy.engine.Engine - [generated in 0.00042s] {'to_char_1': 'YYYY-MM-DD"T"HH24:MI:SS', 'to_char_2': 'YYYY-MM-DD"T"HH24:MI:SS', 'to_char_3': 'YYYY-MM-DD', 'table_name_1': 'agua_lentica'}
2024-02-27 10:35:09,036 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:09,410 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:09,411 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:09,411 INFO sqlalchemy.engine.Engine - [cached since 2.29s ago] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:09,486 INFO sqlalchemy.engine.Engine - SELECT obj_description((%(table_identifier)s)::regclass, 'pg_class');
2024-02-27 10:35:09,486 INFO sqlalchemy.engine.Engine - [cached since 1.817s ago] {'table_identifier': 'public.area_agricola_florestal_mato'}
2024-02-27 10:35:09,562 INFO sqlalchemy.engine.Engine - SELECT * FROM pg_extension WHERE extname = 'postgis';
2024-02-27 10:35:09,562 INFO sqlalchemy.engine.Engine - [cached since 1.816s ago] {}
2024-02-27 10:35:09,638 INFO sqlalchemy.engine.Engine - 
            SELECT
                C.column_name, C.ordinal_position, C.data_type, C.udt_name,
                C.character_maximum_length, C.numeric_precision, C.numeric_scale,
                PK.ordinal_position AS pk_ordinal_position,
                CASE WHEN udt_name='geometry' THEN
                        upper(postgis_typmod_type(A.atttypmod)) ELSE NULL END AS geometry_type,
                CASE WHEN udt_name='geometry' THEN
                    postgis_typmod_srid(A.atttypmod) ELSE NULL END  AS geometry_srid
            FROM information_schema.columns C
            LEFT OUTER JOIN (
            SELECT KCU.* FROM information_schema.key_column_usage KCU
            INNER JOIN information_schema.table_constraints TC
            ON KCU.constraint_schema = TC.constraint_schema
            AND KCU.constraint_name = TC.constraint_name
            WHERE TC.constraint_type = 'PRIMARY KEY'
        ) PK
            ON (PK.table_schema = C.table_schema)
            AND (PK.table_name = C.table_name)
            AND (PK.column_name = C.column_name)
            LEFT OUTER JOIN pg_attribute A
            ON (A.attname = C.column_name)
            AND (A.attrelid = (%(table_identifier)s)::regclass::oid)
            WHERE C.table_schema=%(table_schema)s AND C.table_name=%(table_name)s
            ORDER BY C.ordinal_position;

2024-02-27 10:35:09,639 INFO sqlalchemy.engine.Engine - [cached since 1.816s ago] {'table_identifier': 'public.area_agricola_florestal_mato', 'table_schema': 'public', 'table_name': 'area_agricola_florestal_mato'}
2024-02-27 10:35:09,727 INFO sqlalchemy.engine.Engine - 
                SELECT GC.f_geometry_column AS column_name, GC.srid, SRS.srtext
                FROM geometry_columns GC
                LEFT OUTER JOIN spatial_ref_sys SRS ON (GC.srid = SRS.srid)
                WHERE GC.f_table_schema=%(table_schema)s AND GC.f_table_name=%(table_name)s;

2024-02-27 10:35:09,727 INFO sqlalchemy.engine.Engine - [cached since 1.803s ago] {'table_schema': 'public', 'table_name': 'area_agricola_florestal_mato'}
2024-02-27 10:35:09,807 INFO sqlalchemy.engine.Engine - 
                    SELECT ST_Zmflag(geometria) AS zm,
                    ST_SRID(geometria) AS srid, SRS.srtext
                    FROM public.area_agricola_florestal_mato LEFT OUTER JOIN spatial_ref_sys SRS
                    ON SRS.srid = ST_SRID(geometria)
                    WHERE geometria IS NOT NULL LIMIT 1;

2024-02-27 10:35:09,807 INFO sqlalchemy.engine.Engine - [generated in 0.00029s] {}
2024-02-27 10:35:09,883 WARNING kart.sqlalchemy.adapter.base - SQL type UUID not fully supported - importing as text
2024-02-27 10:35:09,902 INFO sqlalchemy.engine.Engine - SELECT public._kart_track.pk AS ".__track_pk", public.area_agricola_florestal_mato.identificador, to_char(public.area_agricola_florestal_mato.inicio_objeto, %(to_char_1)s) AS inicio_objeto, to_char(public.area_agricola_florestal_mato.fim_objeto, %(to_char_2)s) AS fim_objeto, public.area_agricola_florestal_mato.valor_areas_agricolas_florestais_matos, public.area_agricola_florestal_mato.nome, public.area_agricola_florestal_mato.geometria 
FROM public._kart_track LEFT OUTER JOIN public.area_agricola_florestal_mato ON public._kart_track.pk = CAST(public.area_agricola_florestal_mato.identificador AS TEXT) 
WHERE public._kart_track.table_name = %(table_name_1)s
2024-02-27 10:35:09,902 INFO sqlalchemy.engine.Engine - [generated in 0.00036s] {'to_char_1': 'YYYY-MM-DD"T"HH24:MI:SS', 'to_char_2': 'YYYY-MM-DD"T"HH24:MI:SS', 'table_name_1': 'area_agricola_florestal_mato'}
2024-02-27 10:35:09,979 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:10,353 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:10,354 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:10,354 INFO sqlalchemy.engine.Engine - [cached since 3.233s ago] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:10,430 INFO sqlalchemy.engine.Engine - SELECT obj_description((%(table_identifier)s)::regclass, 'pg_class');
2024-02-27 10:35:10,430 INFO sqlalchemy.engine.Engine - [cached since 2.761s ago] {'table_identifier': 'public.area_infra_trans_aereo'}
2024-02-27 10:35:10,506 INFO sqlalchemy.engine.Engine - SELECT * FROM pg_extension WHERE extname = 'postgis';
2024-02-27 10:35:10,506 INFO sqlalchemy.engine.Engine - [cached since 2.76s ago] {}
2024-02-27 10:35:10,583 INFO sqlalchemy.engine.Engine - 
            SELECT
                C.column_name, C.ordinal_position, C.data_type, C.udt_name,
                C.character_maximum_length, C.numeric_precision, C.numeric_scale,
                PK.ordinal_position AS pk_ordinal_position,
                CASE WHEN udt_name='geometry' THEN
                        upper(postgis_typmod_type(A.atttypmod)) ELSE NULL END AS geometry_type,
                CASE WHEN udt_name='geometry' THEN
                    postgis_typmod_srid(A.atttypmod) ELSE NULL END  AS geometry_srid
            FROM information_schema.columns C
            LEFT OUTER JOIN (
            SELECT KCU.* FROM information_schema.key_column_usage KCU
            INNER JOIN information_schema.table_constraints TC
            ON KCU.constraint_schema = TC.constraint_schema
            AND KCU.constraint_name = TC.constraint_name
            WHERE TC.constraint_type = 'PRIMARY KEY'
        ) PK
            ON (PK.table_schema = C.table_schema)
            AND (PK.table_name = C.table_name)
            AND (PK.column_name = C.column_name)
            LEFT OUTER JOIN pg_attribute A
            ON (A.attname = C.column_name)
            AND (A.attrelid = (%(table_identifier)s)::regclass::oid)
            WHERE C.table_schema=%(table_schema)s AND C.table_name=%(table_name)s
            ORDER BY C.ordinal_position;

2024-02-27 10:35:10,583 INFO sqlalchemy.engine.Engine - [cached since 2.76s ago] {'table_identifier': 'public.area_infra_trans_aereo', 'table_schema': 'public', 'table_name': 'area_infra_trans_aereo'}
2024-02-27 10:35:10,674 INFO sqlalchemy.engine.Engine - 
                SELECT GC.f_geometry_column AS column_name, GC.srid, SRS.srtext
                FROM geometry_columns GC
                LEFT OUTER JOIN spatial_ref_sys SRS ON (GC.srid = SRS.srid)
                WHERE GC.f_table_schema=%(table_schema)s AND GC.f_table_name=%(table_name)s;

2024-02-27 10:35:10,674 INFO sqlalchemy.engine.Engine - [cached since 2.75s ago] {'table_schema': 'public', 'table_name': 'area_infra_trans_aereo'}
2024-02-27 10:35:10,753 INFO sqlalchemy.engine.Engine - 
                    SELECT ST_Zmflag(geometria) AS zm,
                    ST_SRID(geometria) AS srid, SRS.srtext
                    FROM public.area_infra_trans_aereo LEFT OUTER JOIN spatial_ref_sys SRS
                    ON SRS.srid = ST_SRID(geometria)
                    WHERE geometria IS NOT NULL LIMIT 1;

2024-02-27 10:35:10,754 INFO sqlalchemy.engine.Engine - [generated in 0.00031s] {}
2024-02-27 10:35:10,830 WARNING kart.sqlalchemy.adapter.base - SQL type UUID not fully supported - importing as text
2024-02-27 10:35:10,849 INFO sqlalchemy.engine.Engine - SELECT public._kart_track.pk AS ".__track_pk", public.area_infra_trans_aereo.identificador, to_char(public.area_infra_trans_aereo.inicio_objeto, %(to_char_1)s) AS inicio_objeto, to_char(public.area_infra_trans_aereo.fim_objeto, %(to_char_2)s) AS fim_objeto, public.area_infra_trans_aereo.valor_tipo_area_infra_trans_aereo, public.area_infra_trans_aereo.geometria 
FROM public._kart_track LEFT OUTER JOIN public.area_infra_trans_aereo ON public._kart_track.pk = CAST(public.area_infra_trans_aereo.identificador AS TEXT) 
WHERE public._kart_track.table_name = %(table_name_1)s
2024-02-27 10:35:10,850 INFO sqlalchemy.engine.Engine - [generated in 0.00036s] {'to_char_1': 'YYYY-MM-DD"T"HH24:MI:SS', 'to_char_2': 'YYYY-MM-DD"T"HH24:MI:SS', 'table_name_1': 'area_infra_trans_aereo'}
2024-02-27 10:35:10,926 INFO sqlalchemy.engine.Engine - COMMIT
2024-02-27 10:35:11,300 INFO sqlalchemy.engine.Engine - BEGIN (implicit)
2024-02-27 10:35:11,301 INFO sqlalchemy.engine.Engine - SELECT public._kart_state.value 
FROM public._kart_state 
WHERE public._kart_state.table_name = %(table_name_1)s AND public._kart_state.key = %(key_1)s
2024-02-27 10:35:11,301 INFO sqlalchemy.engine.Engine - [cached since 4.18s ago] {'table_name_1': '*', 'key_1': 'tree'}
2024-02-27 10:35:11,377 INFO sqlalchemy.engine.Engine - SELECT obj_description((%(table_identifier)s)::regclass, 'pg_class');
2024-02-27 10:35:11,377 INFO sqlalchemy.engine.Engine - [cached since 3.708s ago] {'table_identifier': 'public.area_infra_trans_cabo'}
2024-02-27 10:35:11,453 INFO sqlalchemy.engine.Engine - SELECT * FROM pg_extension WHERE extname = 'postgis';
2024-02-27 10:35:11,453 INFO sqlalchemy.engine.Engine - [cached since 3.707s ago] {}
2024-02-27 10:35:11,532 INFO sqlalchemy.engine.Engine - 
            SELECT
                C.column_name, C.ordinal_position, C.data_type, C.udt_name,
                C.character_maximum_length, C.numeric_precision, C.numeric_scale,
                PK.ordinal_position AS pk_ordinal_position,
                CASE WHEN udt_name='geometry' THEN
                        upper(postgis_typmod_type(A.atttypmod)) ELSE NULL END AS geometry_type,
                CASE WHEN udt_name='geometry' THEN
                    postgis_typmod_srid(A.atttypmod) ELSE NULL END  AS geometry_srid
            FROM information_schema.columns C
            LEFT OUTER JOIN (
            SELECT KCU.* FROM information_schema.key_column_usage KCU
            INNER JOIN information_schema.table_constraints TC
            ON KCU.constraint_schema = TC.constraint_schema
            AND KCU.constraint_name = TC.constraint_name
            WHERE TC.constraint_type = 'PRIMARY KEY'
        ) PK
            ON (PK.table_schema = C.table_schema)
            AND (PK.table_name = C.table_name)
            AND (PK.column_name = C.column_name)
            LEFT OUTER JOIN pg_attribute A
            ON (A.attname = C.column_name)
            AND (A.attrelid = (%(table_identifier)s)::regclass::oid)
            WHERE C.table_schema=%(table_schema)s AND C.table_name=%(table_name)s
            ORDER BY C.ordinal_position;

2024-02-27 10:35:11,533 INFO sqlalchemy.engine.Engine - [cached since 3.71s ago] {'table_identifier': 'public.area_infra_trans_cabo', 'table_schema': 'public', 'table_name': 'area_infra_trans_cabo'}
2024-02-27 10:35:11,620 INFO sqlalchemy.engine.Engine - 
                SELECT GC.f_geometry_column AS column_name, GC.srid, SRS.srtext
                FROM geometry_columns GC
                LEFT OUTER JOIN spatial_ref_sys SRS ON (GC.srid = SRS.srid)
                WHERE GC.f_table_schema=%(table_schema)s AND GC.f_table_name=%(table_name)s;

2024-02-27 10:35:11,620 INFO sqlalchemy.engine.Engine - [cached since 3.696s ago] {'table_schema': 'public', 'table_name': 'area_infra_trans_cabo'}
olsen232 commented 4 months ago

The issue is that the kart_track table stores only stores the rows which have changed - which, happily, you have populated yourself without knowing it, since whenever you modify those tables using another client, it runs some triggers which put the table + primary key of the changed row into the kart_track table. However, we don't know from this information if the table itself has changed - ALTER TABLE area_agricola_florestal_mato RENAME foo TO bar will not cause any triggers to run and not put anything in the kart_track table. Kart was generally developed testing one table at a time or with very low latency to the working copy, so we aren't necessarily aware of where the pain points will be as users add more tables to track / more changes to track / more latency to the WC.

Having only just started thinking about this, I don't have a solution right now. Possible improvements could be:

SrNetoChan commented 4 months ago

Ah, that makes sense. I was not aware that kart would be able to track changes to the tables structure. That's nice!

The information_schema.columns table stores information about all columns and tables, including the constraints, datatypes, etc...

If this table could somehow be tracked internally (for kart) for the all the tables under version control, with one single query it would be possible to return a list of tables that suffered structure changes.

Then only tables in kart_status and with the structure changes would need to actually check the differences.

Again, just an idea, as I have no idea how kart works internally.

SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' ;