I too have had only one problem with Leo on my MacPro, MacBook Pro and G4. C3 works just fine as does all my other software, including PS plugins.
The only exception is LR 1.2 going to Print mode, which is a nuisance but hardly crippling as I usually use CS3 for printing using Canon's 16 but Export module.
Time Machine alone is a really good reason to go to Leo.
I (probably unwisely) installed Leopard on my G5 quad core almost right away (tho retaining a clone of my hard drive with Tiger on a separate drive, just in case). Lightroom RAW processing is working with Phase One and Canon RAW files just fine and CS3 is also working fine. At first I had a number of CS3 freezes where the image file would just "disappear" but still be open--- but now I haven't had this happen in the last several days at all, and I've been working with Lightroom and CS3 several hours each day. Imageprint 7 with Leopard fix is working fine, as is Disk Warrior to rebuild my directory(when started from the DW disk). Fruit menu does not work, and I miss that. iPhoto and iWeb also seem to be working fine and I've been using both of these apps the last several days also. Integrated Color is OK, as is Toast for burning discs. Silverkeeper and Super Duper back up are working flawlessly.
One thing that I had a complaint about is that since I worked on my web site with Leopard, computers in an office were freezing when they tried to go to the site (that may have been just the time I was uploading...don't know. Anyway when I access it from any of my Macs, it works flawlessly...so go figure??
Hope this helps from first hand experience. Long story short, my system with Leopard is working so good now that I backed it up on the external drive (using Super Duper) that I had Tiger on). I felt that safe.
Also, iView MediaPro works fine with leopard.
BTW--the way the dock opens---stacks and/or all those huge folders in a grid drive me crazy. I yearn for the doc that I can right click on a folder and it opens with a simple vertical list of the contents of that folder---no curved menus with big icons and no grids with those huge folder icons---waaay too much eye candy for me. Hope apple gives us the choice to set the dock opening like it used to be. eleanor