"I had the same problem. All CS3 apps were running as usual after Lion upgrade... all but Photoshop. Then, after reading your post, I remembered Photoshop was running under Rosetta.
What I did? I moved Photoshop to desktop using option when dragging, in order no avoid an application alias creation on desktop. Then run from desktop... OS ask to make some fix because app was moved from original place.... You can cancel or allow (I canceled). Photoshop oppened as usual, played with the application 1 or 2 minutes, closed it and returned to the application's original folder.
Again I ran application, the system ask to repair... I allowed.... And Photoshop is working....
I hope my experience could help you.
Edwin"
Wow! That works reasonably well. At least until some gets their head out and comes out with a real solution. I copied PS to the desktop by dragging while holding the option key
Opened just like you said
I deleted the link in my dock and replaced it with a link to the new app. and hid it away in a separate folder.
Everytime I open it it gives me the repair or cancel option and I simply cancel and use it.
Hope someone comes up with something more permanent but, for the time being this works. I'm not sure who to blame Apple or Adobe but, for Apple to remove the open in Roseta checkbox and then let Photoshop continue to try to do it is ridiculous.
Thanks very much Edwin.