Photoshop CS4 was released as an Universal app (system requirements). So both PowerPC (PPC) and Intel support. Since it's an Universal app, you shouldn't need Rosetta on an Intel .
So upgrading to Snow Leopard shouldn't be a problem... Until you're gonna use the droplet functionality of Photoshop CS4. That piece of code still needs Rosetta to function.
So no way that CS4 is Universal. It's just another piece of buggy Adobe software.
What's next? An announcement that this crap won't be supported and that you can upgrade to the >1000USD Adobe CS5 suite next year?