[BUG] Data from postgres synchronized just once on startup and then not reflects changes

[devzerker]

PGSync version: 2.3.3

Postgres version: 14.6.0

Elasticsearch version: 8.5.0

Redis version: 7.0.7

Python version: 3.7

Problem Description:

Got specific tables and relations when children in PGSync have "self-reference" through other table:

create table clients
(
    id   int primary key,
    name text
);

create table calls
(
    id            int primary key,
    from_id       int references clients not null,
    to_id         int references clients not null,
    call_duration int,
    timestamp     timestamptz
);

insert into clients
values (1, 'John'),
       (2, 'Alice'),
       (3, 'Piter'),
       (4, 'Carl');

insert into calls
values (1, 1, 2, 184528, '2022-12-17T09:05:16+0300'),
       (2, 1, 3, 269037, '2022-12-17T09:45:03+0300'),
       (3, 2, 3, 44278, '2022-12-17T15:20:35+0300'),
       (4, 2, 4, 25689, '2022-12-17T15:30:08+0300');

this corresponds to the following schema:

[
    {
        "database": "postgres",
        "index": "clients",
        "nodes": {
            "table": "clients",
            "schema": "public",
            "children": [
                {
                    "table": "calls",
                    "schema": "public",
                    "relationship": {
                        "variant": "object",
                        "type": "one_to_many",
                        "foreign_key": {
                            "child": ["from_id"],
                            "parent": ["id"]
                        }
                    },
                    "children": [
                        {
                            "table": "clients",
                            "schema": "public",
                            "label": "to",
                            "relationship": {
                                "variant": "object",
                                "type": "one_to_one",
                                "foreign_key": {
                                    "child": ["id"],
                                    "parent": ["to_id"]
                                }
                            }
                        }
                    ]
                }
            ]
        }
    }
]

Data from postgres synchronized just once on startup and then not reflects changes. If i execute next script:

insert into calls
values (6, 1, 4, 255896, '2022-12-17T16:10:52+0300');

index data not updates.

https://user-images.githubusercontent.com/10089615/208267757-dfce53d3-b157-45a8-b962-6dc680963123.mp4

Here full docker compose example with repro.

Error Message (if any): None

[kamal951]

I have the same issue, have you found a solution?

[toluaina]

Sorry about the delay here. Self-referential relationships are NOT supported. There is a WIP to address this but it's not quite finished yet. This is the example of the WIP branch

[devzerker]

Sorry about the delay here. Self-referential relationships are NOT supported. There is a WIP to address this but it's not quite finished yet. This is the example of the WIP branch

Dont rush, thanks for your work!