Table is not uploaded to elastic when using children -> children with setting relation type

[liashchynskyi]

PGSync version: latest

Postgres version: 13

Elasticsearch version: 7.10

This works:

"children":[
   {
      "table":"address",
      "columns":[
         "address",
         "address2",
         "phone",
         "district"
      ],
      "relationship":{
         "type":"one_to_many",
         "variant":"object"
      },
      "children":[
         {
            "table":"city",
            "columns":[
               "city"
            ],
            "relationship":{
               "type":"one_to_many",
               "variant":"object"
            },
            "children":[
               {
                  "table":"country",
                  "columns":[
                     "country"
                  ],
                  "relationship":{
                     "type":"one_to_many",
                     "variant":"object"
                  }
               }
            ]
         }
      ]
   }
]

It won't work if I specify through_tables or foreign_key type in any children's relationship. Instead I get an error about foreign keys. I do not think it has to be that way.

[eichmann]

Do the child tables assert foreign key dependencies on their parents?

[toluaina]

• pgsync will automatically try to determine the relationship between a parent and a child
• This is needed to build a query to pull data with
• If there is no foreign key constraint then you can specify a join condition using the foreign_key declaration in the schema.json
• Can you provide more details on your database schema ?

[liashchynskyi]

There is a relation between tables. The point is, if I clearly specify it in one of the children, it stops working. If I leave it to pgsync to automatically determine relation, then it works.

[lehuuthien2]

i don't know if my problem is same as above, but i also have 3 tier relationship. You treat the middle tier (train_stations) like an pivot table, but it's a normal table and has subdata as the 3rd tier (train_station_floors) (3rd tier may or may not have data). Problems:

• If there is no data in the 3rd tier, the 2nd tier will not be indexed even though it has data
• When the 3rd tier changes (insert, update,...) nothing happens, and I have to update the 2nd tier to sync the data

"table": "railway_assets",
"schema": "public",
"columns": [
    "id",
    "company_id",
    "area_id",
    "name",
    "milepost",
    "image"
],
"children": [
    {
        "table": "train_stations",
        "label": "train_station",
        "schema": "public",
        "columns": [
            "id",
            "railway_asset_id",
            "type",
            "area",
        ],
        "relationship": {
            "variant": "object",
            "type": "one_to_one",
            "foreign_key": {
                "child": [
                    "railway_asset_id"
                ],
                "parent": [
                    "id"
                ]
            }
        },
        "children": [
            {
                "table": "train_station_floors",
                "label": "train_station_floors",
                "relationship": {
                    "variant": "object",
                    "type": "one_to_many",
                    "foreign_key": {
                        "child": [
                            "train_station_id"
                        ],
                        "parent": [
                            "id"
                        ]
                    }
                }
            }
        ]
    }
]

[jpughcs]

Any update on this?

[toluaina]

@jpughcs I think the issue you're describing is different. Can you please open a separate issue to track this

[manishasodekar]

@toluaina I'm facing same issue.

schema.json

[
    {
        "database": "voice-analytics-2",
        "index": "calls-synced",
        "plugins": [
            "comment"
        ],
        "mapping": {
            "reactions": {
                "type": "nested"
            }
        },
        "routing": "call_id",
        "nodes": {
            "table": "calls_comments",
            "columns": [
                "id",
                "call_id",
                "text",
                "parent_id",
                "type",
                "call_location",
                "created_by",
                "created_at",
                "updated_at",
                "updated_by"
            ],
            "transform": {
                "rename": {
                    "id": "commentId",
                    "text": "commentText",
                    "parent_id": "parentId",
                    "created_by": "createdBy",
                    "created_at": "createdAt",
                    "updated_at": "updatedAt",
                    "updated_by": "updatedBy"
                }
            },
            "children": [
                {
                    "table": "calls_comments_reactions",
                    "columns": [
                        "code",
                        "created_at",
                        "created_by"
                    ],
                    "transform": {
                        "rename": {
                            "code": "reactionCode",
                            "created_by": "createdBy",
                            "created_at": "createdAt"
                        }
                    },
                    "label": "reactions",
                    "relationship": {
                        "variant": "object",
                        "type": "one_to_many",
                        "foreign_key": {
                            "child": [
                                "comment_id"
                            ],
                            "parent": [
                                "id"
                            ]
                        }
                    }
                }
            ]
        }
    }
]

Mode: daemon

Issue: If I make any changes in child table. changes are not reflecting on ES. but, I get this instead in logs.

Additonal Info: I have added plugins for transformation.

Observation: If I rerun pgsync without daemon mode previous changes to child table will reflect to ES index.

[jpughcs]

@jpughcs I think the issue you're describing is different. Can you please open a separate issue to track this

I think you meant @lehuuthien2

I am also facing this issue

[cinzunzas]

Same issue dealing with nested children same behavior as described by @jpughcs on previous comments.