From experience (and I’m sure any programmer will corroborate): bugs like this are virtually always something really stupid that you overlooked. We have all been there. But whatever it is, you’re the only one with the access to narrow it down.
Trying to reproduce in psql is a good way to rule some stuff out.
Good points. I loaded the backup into a Dev db and there wasn’t any conflict. I should dig more. And I should try psql yes.
Yup that’s why I’m kinda desperate hoping someone already had seen something similar and know the answer.
I couldn’t believe my eyes what I was seeing.
Can you please show the schema definition for the module you’re querying? It might be that it uses some special encoding of the IDs or it might use some other field as the primary key
I have had some funky behaviour like this after a pg upgrade. I fixed it by running a vacuum.
Actually yeah I’ve seen weird behaviour like this with a corrupted index after an upgrade. Rebuilding indexes fixed it for me.
Thanks! I think these are the answers (vacuum / rebuild indices). I worked around by setting new IDs (because the things had this problem were not referenced elsewhere as fk, so it’s safe to do so) and I’m not sure if I will be able to confirm it’s the proper fix.
I indeed upgraded the db recently.
Wow, I had heard Matrix ran into corrupted indexes recently but their DB is pretty big. Didn’t think it was this common! If that’s what it was you’re lucky you caught it while testing. Could have been a very scary bug.