FIX: Topic user bookmarked column is out of sync after post moves (PR #12612)

When posts are moved from one topic to another, the topic_user.bookmarked column for all users in the new and the old topic needs to be resynced, for example because a user bookmarks post 12 in topic 1, then it is moved to topic 2, the topic_user record for topic 1 should no longer be bookmarked.

This has been addressed in other places in the past:

GitHub

Overall the PR looks good, but I’m always unsure of performance implications of queries like this. :smiley:

It will run a count(*) subquery for each topic_users record that has bookmarked set to true? Will the database optimize it, and even if it will – are you sure it won’t cause problems on massive forums?

The same question applies to SyncTopicUserBookmarked.

Well, I assume it will work fine because a similar migration has been run twice before without any issues :slight_smile: See the other 2 PRs linked in the OP. I will do a quick test against meta and see (using BEGIN…ROLLBACK)

@CvX I just tried this on meta, which has 5,423,106 topic_user records. The first query took ~1s, the second query took ~4s.

it is certainly an expensive query, an alternative to minimize locking would be to update on batches (update a max of say 1000 rows in a loop)

Not sure if we need to go down this path recommend you test on bbs.

disable ddl transaction here. This can fail halfway and then recover cleanly. Means we hold locks for a lot less time.

@SamSaffron I just tried on bbs, they have 10,444,705 topic_user records. It actually ran faster than meta! < 1s. I will add disable_ddl_transaction! in the migration then merge

1 Like

@SamSaffron will merge this next week so I can keep an eye on it.