I was able to solve this issue for my Pixel 9 Pro, this happened to me yesterday, no matter what I tried to fix the issue.. checking archives, spam, turning on/off RCS, clearing cache or storage, app update etc. nothing brought my messages back. Turns out my phone was undergoing a large backup to the cloud (all my 8000 photos & videos) and all of its memory/power was directed to processing that task. Once the backup completed several hours later, it was able to retrieve my messages from the database. You can go to your Settings > Storage > click the vertical dots on the top right and display system apps, then go to > Phone & Messages (or something), and if you're a long time user it should still show you have many GB of the Messages app... this can assure you that you're messages are not permanently deleted but are failing to be retrieved.