I have an Apple TV now and for our family needs it's great. We keep the kids on mostly PBS Kids shows and other educational stuff. They also have a handful of Disney movies I've transcoded. We have our music library in iTunes on our Mac Mini.
We can access the entire iTunes library on the other side of the house. Works great for us because the cheapo LCD is located in a cabinet where there is no cable line. But I can get a network connection for AppleTV.
I also use this in conjunction with El Gato's EyeTV. (And I did get the EyeTV and HD USB Tuner yesterday at Macworld for $99). This is basically a DVR on a Mac. It records kids shows and automatically converts them to Apple TV format and dumps the vid into iTunes. Then when you go to the Apple TV, the new shows appear automatically. Some of the new features of this allow you to use your iPhone/iTouch via Wifi and view the same EyeTV content. Seems you can also do it via EDGE but haven't tried.
I also have Tivos that can pull video (yeah, I'm an engineer...). But I rarely do that because EyeTV works so well.
If you give into iTunes and you have iPods, then yes, it's a good option. I'm not going to get into DRM and Apple's monopoly blah blah blah. It works and it's very easy. Can't speak for the rentals yet because it's not for 2 weeks. But the concept is much better than before.
