It really depends on your needs.
I recently sold my G5 for almost 70% of what I had purchased it for 2 years prior. They are still very much valued.
If you get one of the last G5s put out, cutting edge, it's still a very fast machine. People say that Intel is more future-proof, but really, you're buying computers that are roughly the same speed if you get one of the G5s or one of the lesser Mac Pros.
I'd say this is especially the case if you are a (legitimate) Photoshop owner who doesn't need a lot of the extras that comes in CS3.
Mac Pros are expensive, and so is CS3. If CS2 satisfies your needs and Tiger / Leopard will suit you for the next few years, I don't think it's any kind of computer suicide to buy a G5.