I already have Laowa 15 and 100 along with EF 100mm L so not getting RF 100mm Macro anytime soon, but would really like too get updated 180mm Macro for butterflying and venomous snakes for RF mount. For now I am borrowing Sigma 180mm f2.8 for odd times when I shoot butterflies but its seriously heavy to carry in field. Apparently Sony holds a stake in Tamron so they might not make lenses for either Z or RF and in case of Sigma they stated they wont make lenses for closed off systems so once again RF and Z users are out of luck.
I do a lot of butterfly photography (also other insects, reptiles, mammals, birds etc). Mostly I shoot butterflies with the 100mm macro. The EF version is better than the RF version, in terms of ability to instantly switch between MF and AF without having to fumble with buttons or switches when shooting in SERVO AF.
A 100mm macro can make it difficult to approach nervous butterflies, but is very manoeuvrable due to the light weight. I prefer longer focal lengths for cleaner backgrounds though. Previously, I used the Canon 180mm macro which has gorgeous bokeh, but I sold it recently because I just find an unstabilised lens too difficult to use handheld. In the tropics, I sometimes use the RF100-500mm, but it's too heavy to cart around all day long in tropical heat. AlanF reckons that the 100-400mm is excellent, much lighter and cheaper, and it focuses close enough for medium-sized butterflies, so that might be worth trying.
The Sigma 150mm and 180mm macros are beasts. Very heavy, clunky, very slow to focus and they have pretty ineffective stabilisation in my experience (compared to Canon 100mm and 180mm). I know some people who use the Tamron 180mm, which is lighter, so that could also be worth trying if you can find a secondhand one in good nick. Of course, you'd need to use the RF-EF adaptor with the Sigma and Tamron lenses.
I'd take what Sigma say about closed off systems with a pinch of salt. Sooner or later, both Sigma and Tamron will realise that they could shift a hell of a lot of lenses if they made them available in RF mount, it's just a matter of time IMO. Existing EF mount designs could easily be ported to RF, and the EF and RF protocols are near-identical, so compatibility issues would be minimal. Also, they have designs for Sony MILCs that could easily be put out in RF mount. I think it will take a lot longer for third parties to produce Z mount lenses though, simply because Nikon is sadly now a minor league brand.