PGSync tries to find FK between unrelated tables.

[namoshizun]

PGSync version: v2.1.1

Postgres version: v11.1

Elasticsearch version: v7.11.2

Redis version: v5

Python version: v3.8

Problem Description:

Using this schema, while bootstrap and pgsync commands run without issues, I am getting the following error when actually create a Tenant database record. Look like pgsync has mistakenly tried to correlate the UserRoles medium table with Role's foreign key entity District?

Error Message (if any):

Exception in thread Thread-23:
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/threading.py", line 932, in _bootstrap_inner
    self.run()
  File "/usr/local/lib/python3.8/threading.py", line 870, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 860, in poll_redis
    self.on_publish(payloads)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 927, in on_publish
    self.sync_payloads(_payload)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 829, in sync_payloads
    for doc in self._payloads(payloads):
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 645, in _payloads
    filters = self._insert(
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 339, in _insert
    foreign_keys = self.query_builder._get_foreign_keys(
  File "/usr/local/lib/python3.8/site-packages/pgsync/querybuilder.py", line 45, in _get_foreign_keys
    for key, value in get_foreign_keys(through, node_b).items():
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 961, in get_foreign_keys
    for table, columns in _get_foreign_keys(
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 920, in _get_foreign_keys
    raise ForeignKeyError(
pgsync.exc.ForeignKeyError: 'No foreign key relationship between "public.saas_api_user_roles" and "public.saas_api_district"'

[toluaina]

There needs to be a direct or indirect relationship between all tables in your schemas. Can you please provide more details of your database schema?

[namoshizun]

There needs to be a direct or indirect relationship between all tables in your schemas. Can you please provide more details of your database schema?

I've hyperlinked the schema source code in the issue description, or this link: https://gist.github.com/namoshizun/3f86d97cd0b4ce72b779ed913a4fa849

[toluaina]

Sorry I meant the database schema itself. You can send this to me directly if you have any reservations.

@namoshizun

[JacobReynolds]

Bumping this, I'm experiencing the same issue. It seems to happen when you add children to a node that has a through_table property. For example this child seems to be using the through_table table of its parent instead of the table of its parent.

I haven't been able to find exact reproduction steps, the initial bootstrap works, but at some point it breaks during a bulk update.

This seems to be a duplicate of #199.

[loren]

I appear to be running into this as well. Initial bootstrap and sync work, but adding a new record on my Book<->Author through table errors with

pgsync.exc.ForeignKeyError: 'No foreign key relationship between "public.BookAuthor" and "public.BookAuthor"'

Adding just a new Book works. Adding just a new Author works. It's adding a Book with an Author that is failing. The op causing it is

DEBUG:pgsync.sync: tg_op: INSERT table: public.BookAuthor

The last bit of the stack trace is

File "/Users/me/opt/anaconda3/lib/python3.9/site-packages/pgsync/sync.py", line 824, in _payloads
    filters = self._insert_op(
  File "/Users/me/opt/anaconda3/lib/python3.9/site-packages/pgsync/sync.py", line 491, in _insert_op
    foreign_keys = self.query_builder._get_foreign_keys(
  File "/Users/me/opt/anaconda3/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 "/Users/me/opt/anaconda3/lib/python3.9/site-packages/pgsync/base.py", line 890, in get_foreign_keys
    for table, columns in _get_foreign_keys(
  File "/Users/me/opt/anaconda3/lib/python3.9/site-packages/pgsync/base.py", line 850, in _get_foreign_keys
    raise ForeignKeyError(

I'll try to dig into the code to see what it's expecting but would be happy to hear suggestions or workarounds.

[loren]

this fixed it for me: https://github.com/toluaina/pgsync/pull/332

[toluaina]

Sorry for taking a while to address this. Can you please try against the main branch. This should be resolved now.