I have a 2004 dual 2.0 G5 with 6GB of ram, new WD 640 drives, and the xt800 graphic card - running OS Tiger.
I am a moderate type of user such as yourself and find this system more than adequate for my needs. I've recently purchased CS4 and find that Photoshop works just as fast on my machine as CS2. Even some of my experiments using 500mg files were okay performance wise - although you do have to wait 30 secs for a final composite, or wait 15 seconds for such images to open in Photoshop. E-mail and internet are snappy.
However, I need to qualify my experience. I've come to my G5 from a G4 last year with all the previous software - such as Tiger - which for me is rock solid and stable. I see no need to move on from this. If you are entering the game now and wanting to get a G5, you have a very small window now to obtain all the compatible software you'll need to do what you need to do - meaning that you'll not only need the funds to buy the G5, but you'll have to be able to purchase all of your software or soon it will not be available except through 3rd parties, used on e-bay, craig's list. I'm not sure that I trust such purchases of software other than reputable dealers.
Us G5 users are at the very tail end of that software that will be written for our machines. Everything will shortly be Intel only software. That is the biggest concern with purchasing a G5 at this point in time. I have all the software I need, and you will need to decide for yourself what you need and can afford now.
Then there are the problems with obtaining decent graphic cards for your G5. It's not easy finding them. My fear at the time of purchase was having my graphic card fail, then not being able to find a replacement - rendering the system inoperable. I bought a second back up graphic card last year when they were still floating around - now is a different story - they are getting increasingly hard to find.
Again - if you have the software, and you can get one of the 8 GB ram models and max it out - and you accept that software will soon move to Intel only - and your needs are moderate - I think the G5 is great. The prices are coming down nicely and are great value if they meet your performance needs. I am continually amazed at what my G5 can still do.
Good luck with your decision.
Mike