toluaina
ElasticSearch not updating on one_to_many relationship
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)"
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
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
This should be addressed in the latest release. Can you please try 2.5.0 or off the main branch please?