I have a 2 processor G5 (2.5 GHz), and I'm pretty happy with it. In 2009 when 10.6 rolls out, it'll have basically the same end-user functionality.
If the Intel transition was any indicator, it'll be 6-9 months until major apps I need start requiring 10.6, with some lagging way behind that. They have their own release cycles and won't magically stop producing universal code just because there's a new OS.
2010 will roll around and my G5 will be 4.5 years old and I'll start thinking about an intel box as my main desktop. Longer than some I've owned and shorter than others.
No G5 Owners, Snow Leopard is not a Screw Job