It _does_ work unless you hit some bug (and there have been some that affect some people). If you were an early adopter in particular there were some database corruption issues. If that's the case deleting the places database is often the best fix (especially if there's nothing in there you care about -- you're clearing private data, right?). Instructions at http://support.mozilla.com/ for this and other common problems.
The other issue is that the url bar shows both history and bookmarks. Obviously people don't want to clear their bookmarks so some data still shows up even after clearing history. This issue has been addressed in Firefox 3.5 with an option to not show bookmarks in the URL bar (on the Privacy tab in Options).
Firefox 3.5 is _not_ vulnerable to this attack.
The reason something like this scares me is that it lulls users into a higher level of trust... and doesn't protect them from hacked sites, or sites that choose not to implement this.
This mechanism isn't intended for users -- this is a tool for site authors, to cooperate with them in enforcing their policies. The site still has to make a best effort at implementing those policies themselves to protect all their visitors using browsers that don't support CSP (which includes every officially released version of Firefox to date). This is an extra layer of protection for users of CSP-compliant browsers, and a benefit to the site through the reporting function.
Please do continue running NoScript if you like. CSP is a mechanism for site authors to declare their policy, add-ons like NoScript and AdBlock are tools for users to declare their policies.
Even if this was never implemented in any other browser sites still benefit through early detection of active attacks. If your site implements a security policy with a report URI then every Firefox visitor will be conducting a passive security scan on every page they visit, at least for the types of security problems CSP targets (primarily XSS).
If you really don't want any upgrades just go into the options and toggle the "Check for updates" box. The default auto-upgrade is appropriate for 99% of internet users, but if you're one of the 1% please use the preference we put in just for you. No need to get all hostile about it.
To do nothing is to be nothing.
