ForeignKeyError: No foreign key relationship error when records are updated

PGSync version: Version: 2.3.2

Postgres version: postgres:12.11-alpine

Elasticsearch version: 7.17.6

Redis version: redis-server --version Redis server v=7.0.5 sha=00000000:0 malloc=jemalloc-5.2.1 bits=64 build=d9291579292e26e3

Python version: Python 3.9.5

Problem Description: I trying to use relationtype one_to_many. It works properly first time but if I update relationship it fails:

[
    {
        "database": "data",
        "index": "ct",
        "nodes": {
            "table": "category",
            "schema": "public",
            "columns": [
                "name"
            ],
            "children": [
                {
                    "table": "tag",
                    "columns": [
                        "name"
                    ],
                    "relationship": {
                        "type": "one_to_many",
                        "variant": "scalar",
                        "through_tables": [
                            "category_tags"
                        ]
                    }
                }
            ]
        }
    }
]

Error Message (if any):

Sync data Xlog: [0] => Db: [1] => Redis: [total = 1 pending = 0] => Elastic: [8] ...
  [==================================================]  1/1  100%
Sync data Xlog: [0] => Db: [4] => Redis: [total = 4 pending = 0] => Elastic: [9] ...
2022-10-12 15:49:36.005:ERROR:pgsync.elastichelper: Exception 'No foreign key relationship between "public.tags_category_tags" and "public.tags_category_tags"'
Traceback (most recent call last):
  File "/opt/venv/lib/python3.9/site-packages/pgsync/elastichelper.py", line 130, in bulk
    self._bulk(
  File "/opt/venv/lib/python3.9/site-packages/pgsync/elastichelper.py", line 187, in _bulk
    for _ in helpers.parallel_bulk(
  File "/opt/venv/lib/python3.9/site-packages/elasticsearch/helpers/actions.py", line 472, in parallel_bulk
    for result in pool.imap(
  File "/usr/local/lib/python3.9/multiprocessing/pool.py", line 870, in next
    raise value
  File "/usr/local/lib/python3.9/multiprocessing/pool.py", line 125, in worker
    result = (True, func(*args, **kwds))
  File "/usr/local/lib/python3.9/multiprocessing/pool.py", line 144, in _helper_reraises_exception
    raise ex
  File "/usr/local/lib/python3.9/multiprocessing/pool.py", line 388, in _guarded_task_generation
    for i, x in enumerate(iterable):
  File "/opt/venv/lib/python3.9/site-packages/elasticsearch/helpers/actions.py", line 155, in _chunk_actions
    for action, data in actions:
  File "/opt/venv/lib/python3.9/site-packages/pgsync/sync.py", line 824, in _payloads
    filters = self._insert_op(
  File "/opt/venv/lib/python3.9/site-packages/pgsync/sync.py", line 491, in _insert_op
    foreign_keys = self.query_builder._get_foreign_keys(
  File "/opt/venv/lib/python3.9/site-packages/pgsync/querybuilder.py", line 68, in _get_foreign_keys
    for key, value in get_foreign_keys(through_node, node_b).items():
  File "/opt/venv/lib/python3.9/site-packages/pgsync/base.py", line 890, in get_foreign_keys
    for table, columns in _get_foreign_keys(
  File "/opt/venv/lib/python3.9/site-packages/pgsync/base.py", line 850, in _get_foreign_keys
    raise ForeignKeyError(
pgsync.exc.ForeignKeyError: 'No foreign key relationship between "public.tags_category_tags" and "public.tags_category_tags"'
Exception in poll_redis() for thread Thread-16: 'No foreign key relationship between "public.category_tags" and "public.category_tags"'
Exiting...

toluaina / pgsync

ForeignKeyError: No foreign key relationship error when records are updated #350