Changes to root node table does not sync when root and child node tables have the same names but are in different schemas

[DavidSAmundsen]

PGSync version: 3.1.0

Postgres version: 13

Elasticsearch/OpenSearch version: 8.13.4

Redis version: 7.2.5

Python version: 3.12

Problem Description: PGSync does not update Elasticsearch index if a new row is added to the root table defined in schema.json if it is joined with a child table with the same name from a different schema. Below is a minimal working example.

Populate database:

BEGIN;

CREATE SCHEMA IF NOT EXISTS book;
CREATE SCHEMA IF NOT EXISTS author;

DO
$$
    BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname = 'book' AND tablename = 'my_table') THEN
            CREATE TABLE book.my_table
            (
                id SERIAL PRIMARY KEY,
                title TEXT,
                author_id INTEGER
            );
        END IF;
    END
$$;

DO
$$
    BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname = 'author' AND tablename = 'my_table') THEN
            CREATE TABLE author.my_table
            (
                id INTEGER PRIMARY KEY,
                name TEXT
            );
        END IF;
    END
$$;

INSERT INTO book.my_table (title, author_id) VALUES ('The Great Gatsby', 1);
INSERT INTO book.my_table (title, author_id) VALUES ('The Catcher in the Rye', 2);
INSERT INTO author.my_table (id, name) VALUES (1, 'F. Scott Fitzgerald');
INSERT INTO author.my_table (id, name) VALUES (2, 'J.D. Salinger');

COMMIT;

Then start PGSync with the following schema.json:

[
  {
    "database": "postgres",
    "index": "books",
    "nodes": {
      "table": "my_table",
      "schema": "book",
      "columns": [
        "id",
        "title"
      ],
      "children": [
        {
          "table": "my_table",
          "schema": "author",
          "columns": [
            "id",
            "name"
          ],
          "relationship": {
            "variant": "object",
            "type": "one_to_one",
            "foreign_key": {
              "child": [
                "id"
              ],
              "parent": [
                "author_id"
              ]
            }
          }
        }
      ]
    }
  }
]

All rows are synced as expected. Then run the following SQL to insert one more row in the books.my_table:

INSERT INTO book.my_table (title, author_id) VALUES ('Test book 1', 1);

PGSync detects the change, but does not update Elasticsearch.

Error Message (if any): There is no error message, but the verbose output from PGSync looks like this:

child._subquery:
LATERAL
  (SELECT CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_1)s, CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_2)s, JSON_BUILD_ARRAY(my_table_1.id)) AS JSONB)) AS JSONB) AS _keys,
          CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_3)s, my_table_1.id, %(JSON_BUILD_OBJECT_4)s, my_table_1.name) AS JSONB) AS my_table,
          my_table_1.id
   FROM author.my_table AS my_table_1,
        book.my_table AS my_table_2
   WHERE my_table_1.id = my_table_2.author_id
     AND my_table_1.id = %(id_1)s)
-------------------------------------------------------------------------------
Query:
SELECT JSON_BUILD_ARRAY(anon_1._keys) AS "JSON_BUILD_ARRAY_1",
       CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_2)s, my_table_1.id, %(JSON_BUILD_OBJECT_3)s, my_table_1.title, %(JSON_BUILD_OBJECT_4)s, anon_1.my_table) AS JSONB) AS "JSON_BUILD_OBJECT_1",
       my_table_1.id
FROM book.my_table AS my_table_1
JOIN LATERAL
  (SELECT CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_5)s, CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_6)s, JSON_BUILD_ARRAY(my_table_2.id)) AS JSONB)) AS JSONB) AS _keys,
          CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_7)s, my_table_2.id, %(JSON_BUILD_OBJECT_8)s, my_table_2.name) AS JSONB) AS my_table,
          my_table_2.id AS id
   FROM author.my_table AS my_table_2
   WHERE my_table_2.id = my_table_1.author_id
     AND my_table_2.id = %(id_1)s) AS anon_1 ON anon_1.id = my_table_1.author_id
WHERE my_table_1.id = %(id_2)s
-------------------------------------------------------------------------------

txid_current:
SELECT *
FROM TXID_CURRENT()
-------------------------------------------------------------------------------

If I rename the tables to "book" and "author", still keeping them in separate schemas, root node table changes are successfully synced and the verbose output from PGSync is the following:

Query:
SELECT JSON_BUILD_ARRAY(anon_1._keys) AS "JSON_BUILD_ARRAY_1",
       CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_2)s, book_1.id, %(JSON_BUILD_OBJECT_3)s, book_1.title, %(JSON_BUILD_OBJECT_4)s, anon_1.author) AS JSONB) AS "JSON_BUILD_OBJECT_1",
       book_1.id
FROM book.book AS book_1
LEFT OUTER JOIN LATERAL
  (SELECT CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_5)s, CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_6)s, JSON_BUILD_ARRAY(author_1.id)) AS JSONB)) AS JSONB) AS _keys,
          CAST(JSON_BUILD_OBJECT(%(JSON_BUILD_OBJECT_7)s, author_1.id, %(JSON_BUILD_OBJECT_8)s, author_1.name) AS JSONB) AS author,
          author_1.id AS id
   FROM author.author AS author_1
   WHERE author_1.id = book_1.author_id) AS anon_1 ON anon_1.id = book_1.author_id
WHERE book_1.id = %(id_1)s
-------------------------------------------------------------------------------

1)
pkeys: [3]
{'_meta': {'author': {'id': [1]}},
 'author': {'id': 1, 'name': 'F. Scott Fitzgerald'},
 'id': 3,
 'title': 'Test book 1'}
----------
txid_current:
SELECT *
FROM TXID_CURRENT()
-------------------------------------------------------------------------------