My Sony is not switching .... as long as I tell him to do so.
<snip>
This leads, in my case, to normal videos with colour space 'auto' and HDR videos with bt.2020.
Yes - this is a known issue.
At the moment the Pi 4B builds correctly send the HDR EOTF (HLG/ARIB B-67 or HDR10/ST.2084) signal to switch the TV from SDR mode into the right HDR mode.
However the other requirement for most HDR material (and all HDR10 material) is to switch from Rec 709 to Rec 2020 colour gamut (which is the Wide Colour Gamut used for all HDR10 and most other HDR content). The Rec 2020 colour gamut can be manually forced on your Sony TV, as it can be on mine.
That gets the colour into the right gamut for HDR10.
The two other issues are that the output is still RGB 8-bit not 10-bit - so banding is more likely to be an issue - and if the gamut is flagged as Rec 709 then there is no guarantee that the YCbCr->RGB colour matrices used to generate the RGB won't also still be the Rec 709 ones (which are slightly different to the Rec 2020 ones)
Rec 709 : Y = 0.2126 x R + 0.7152 x G + 0.0722 x B
Rec 2020 : Y = 0.2627 x R + 0.678 x G + 0.0593 x B
UHD TVs don't automatically assume all UHD inputs are Rec 2020 though - as some (most?) SDR UHD content is often still in Rec 709 gamut (the same gamut used for HD)
(For reference the SD Rec 601 RGB->YCbCr matrix is also different - Y = 0.299 x R + 0.587 x G + 0.114 x B)