IIRC, the effects of diffraction are related to physical aperture size
Yes, that is the way I like to look at it, especially when comparing different formats. The angular smearing of light passing through a small aperture depends on the aperture diameter: light spreads with the bulk of it within an angle of very roughly (wavelength of the light)/(aperture diameter), measured in radians. (Multiply by about 3000 to get it in minutes [corrected!] of arc.) For visible light this is about (0.0005)/(aperture in mm).
When it comes to prints of a given size and angular FOV, this angular spread is what counts; in a sense the aperture diameter sets a rough upper limit on the number of "lines per picture height" at which you can get decent resolution on a given subject. So when limiting diffraction effects on prints of a given size and subjects of a given FOV to a given level, longer focal lengths can use proportionately higher aperture ratios.
On the other hand, when comparig different focal lengths and FOV's used with the same format, it is relevant that the diffraction spot size on the film/sensor is proportional to both this angle and the focal length, so is proportional to aperture ratio. That is why photographic discussions of diffraction effects measure diffraction in terms of f-stops. And if one's goal is to get all the sharpness one can out of a given pixel size (as with Jonathan's approach to CoC) the upper f-stop limit is the same for all focal lengths.
Using a formula from
this page at Norm Koren's site and the rough estimate that the resolution limit of a Bayer sensor is about one line pair per three pixels, we get that for the 9 micron pixel pitch of the original 1Ds and current MF backs (giving 22MP in current backs, about 30MP in full 645 format), diffraction has 50% MTF (one stop of contrast lost) at the sensor's resolution limit for about f/21. If one more optimistically believes that the sensor can resolve all the way to one line per pixel, this becomes f/14. So f/16 emerges as a pretty good rough limit if you wish to yield no significant resolution to diffraction.
Even then, most interesting detail is not right at the sensor's resolution limits, and this somewhat less fine detail is hardly affected by diffraction at all even at f/22 (or f/32). Remember that the sense of sharpness of an image usually has more to do with having very high MTF (50%, 80%?) for moderately fine details than with the finest detail that gets just barely resolved.
P. S. As soon as you back of from the edge of resolution limits and look for good holding of contrast (high MTF) in slightly less fine details, sensors and such might well give way to lens MTF performance as the main limiting factor. This is one respect in which MF lenses can have an advantage over 35mm format, often overlooked when we look at numerical measures of performance at the extremes.