ElasticSearch not updating on one_to_many relationship

[Sami2EaCOS]

PGSync version: 2.3.3

Postgres version: 15

Elasticsearch version: 7.17.7

Redis version: 7.0.5

Python version: 3.7.15

Problem Description: ElasticSearch isn't updated for one_to_many relationship

schema.json

[
   {
      "database":"rexcraftia",
      "index":"gear_rarity",
      "setting":{
         "analysis":{
            "analyzer":{
               "keyword_lowercase":{
                  "tokenizer":"standard",
                  "filter":[
                     "lowercase"
                  ]
               }
            }
         }
      },
      "nodes":{
         "table":"gear_rarity",
         "label":"gear_rarity",
         "columns":[
            "color",
            "name",
            "id"
         ],
         "transform":{
            "mapping":{
               "color":{
                  "type":"text",
                  "analyzer":"keyword_lowercase",
                  "fielddata":true
               },
               "name":{
                  "type":"text",
                  "analyzer":"keyword_lowercase",
                  "fielddata":true
               }
            }
         },
         "children":[
            {
               "table":"gear",
               "label":"gears",
               "columns":[
                  "material",
                  "name",
                  "id"
               ],
               "transform":{
                  "mapping":{
                     "material":{
                        "type":"text",
                        "analyzer":"keyword_lowercase",
                        "fielddata":true
                     },
                     "name":{
                        "type":"text",
                        "analyzer":"keyword_lowercase",
                        "fielddata":true
                     }
                  }
               },
               "relationship":{
                  "variant":"object",
                  "type":"one_to_many",
                  "foreign_key":{
                     "child":[
                        "rarity_id"
                     ],
                     "parent":[
                        "id"
                     ]
                  }
               }
            }
         ]
      }
   },
   {
      "database":"rexcraftia",
      "index":"gear",
      "setting":{
         "analysis":{
            "analyzer":{
               "keyword_lowercase":{
                  "tokenizer":"standard",
                  "filter":[
                     "lowercase"
                  ]
               }
            }
         }
      },
      "nodes":{
         "table":"gear",
         "label":"gear",
         "columns":[
            "material",
            "name",
            "id"
         ],
         "transform":{
            "mapping":{
               "material":{
                  "type":"text",
                  "analyzer":"keyword_lowercase",
                  "fielddata":true
               },
               "name":{
                  "type":"text",
                  "analyzer":"keyword_lowercase",
                  "fielddata":true
               }
            }
         },
         "children":[
            {
               "table":"gear_rarity",
               "label":"rarity",
               "columns":[
                  "color",
                  "name",
                  "id"
               ],
               "transform":{
                  "mapping":{
                     "color":{
                        "type":"text",
                        "analyzer":"keyword_lowercase",
                        "fielddata":true
                     },
                     "name":{
                        "type":"text",
                        "analyzer":"keyword_lowercase",
                        "fielddata":true
                     }
                  }
               },
               "relationship":{
                  "variant":"object",
                  "type":"one_to_one"
               }
            }
         ]
      }
   },
   {
      "database":"rexcraftia",
      "index":"player",
      "setting":{
         "analysis":{
            "analyzer":{
               "keyword_lowercase":{
                  "tokenizer":"standard",
                  "filter":[
                     "lowercase"
                  ]
               }
            }
         }
      },
      "nodes":{
         "table":"player",
         "label":"player",
         "columns":[
            "uuid"
         ]
      }
   }
]

DB Schema

Error Message (if any):

I have this when i launch PGSync with "--analyze"

Missing index on table "gear" for columns: ['rarity_id']
9:38 Create one with: "CREATE INDEX idx_gear_rarity_id ON gear (rarity_id)"

Missing index on table "gear_rarity" for columns: ['id']
Create one with: "CREATE INDEX idx_gear_rarity_id ON gear_rarity (id)"

[toluaina]

Hi, I suspect the underlying queries are taking longer due to the missing indices. Can you try creating these indices as suggested and see if it makes any difference

[rutkowskib]

Hi, This bug is also present in 2.4.0. I have created a repository with minimal reproduction of this bug: https://github.com/rutkowskib/pgsync-insert-bug. I have also checked that the last not affected version is 2.3.2

[toluaina]

This should be addressed in the latest release. Can you please try 2.5.0 or off the main branch please?

[rutkowskib]

@toluaina I have just tested this and it's fixed in 2.5.0. Thanks!