Just thought I'd jump in here with my recent experience. I was using CS2/CS3 on a G4 with Tiger: no problems whatsoever, been that way for a couple of years.
Just upgraded to a new MacPro, together with Leopard. Great machine, flies like a dream, and Leopard has many good points, BUT the ONLY problems I've had so far is with the CS3 suite (design premium). Firstly, Photoshop wouldn't start, crashing on opening. Finally found out that it was some non-Adobe plugins that weren't Universal that were causing the problem. Removed them, and Photoshop worked fine. Checked for updates, reinstalled the plugins, only a couple work, the rest started crashing Photoshop again. Damn shame, as they were plugins I was quite fond of too. All I can do is wait and hope an update fixes the problem sometime soon.
My scanning software (TWAIN, from within Photoshop) stopped working, I've had to replace that with something else. That's livable with though. A Postscript font or two went a bit funny, bitmapping. Again, annoying as one was a font I use a lot, haven't got a replacement yet. MyFonts told me their version was playing up in Leopard too.
The biggest single problem I've had is colour management. Across all the CS3 apps, what I see on my screen is not what comes out of my printer. I've tried everything, i'm properly calibrated, the works. All the print dialogue boxes have changed in Leopard, and while previously I was fairly confident I knew what I was doing with colour management, in Photoshop 6 onwards, on MacOS 7 onwards, suddenly i feel I'm on unfamiliar territory again.
I could use a decent manual....
My recomendation would be not to upgrade to Leopard yet, not until Apple and Adobe are truly working in harmony, which, despite their press releases, they really aren't quite doing just yet.