Replace UPDATE with UPSERT on device_max_stream_id table#6363
Replace UPDATE with UPSERT on device_max_stream_id table#6363anoadragon453 merged 5 commits intodevelopfrom
Conversation
dbkr
left a comment
There was a problem hiding this comment.
Hmm, so with this I think it will still reset after it's restarted with this new code, but then update correctly after that, so we swill still get one more round of UISIs after the update, after which it will be fine. We could add some code to the StreamIdGenerator where it loads the current ID to work around this... not sure if it's worthwhile though - probably better to just get this deployed.
| sql = "UPDATE device_max_stream_id SET stream_id = ?" | ||
| else: | ||
| # No rows, perform an insert | ||
| sql = "INSERT INTO device_max_stream_id (stream_id) VALUES (?)" |
There was a problem hiding this comment.
You could also do the update as before and then check the affected row count which would then just be a single query in the normal case, but if this is how we're doing upserts elsewhere then I guess just stick with that.
There was a problem hiding this comment.
It's a little more complicated since we have to check that the stream_id we're inserting is greater than what's currently in the DB. If it's not, then the UPDATE will return 0 rows affected.
We don't want to then insert another row at that point.
|
I'll merge this as-is and we can improve in the future if necessary. |
…)" This reverts commit 657d614.
* commit '657d614f6': Replace UPDATE with UPSERT on device_max_stream_id table (#6363)
Helps address #6311 (for the
device_max_stream_idtable anyways).We had a problem where when our multiple schema delta files were combined into a single schema, it left out an
INSERTstatement that insert the first row into thedevice_max_stream_id. Synapse assumes this row exists and runs anUPDATEagainst it. However, this would do nothing if no row existed, which was the case here.This change changes the
UPDATEto anUPSERT, by adding a new column to the table with a unique constraint, and always trying to insert with the same value. If a conflict arises (which is should do any time there's a row in that table), then thestream_idvalue is updated. If there's no row in there (which will be the case with homeservers initially) then a simpleINSERToccurs.There may be a more elegant way to do this, but this works.