It's not that big of a deal, really. The content  database contains the managed
path for the contained sites, so it uses the web application details to provide
the full path. It just means that you would have the following sites available:

http://foo-prod.com/sites/test1

http://foo-dev.com/sites/test1

Technically you could even have this content database housed on the same farm
as long as they were in two seperate web applications. You just don't want to
try and bring up a copy of that content database on the original web
application. This would create confusion in the config database.

I'm not sure about it being supported by Microsoft or not, but I know
that 1 content  database will work fine from 2 farms - as long as you
don't make any changes that are registered with the configuration
database - otherwise the farm  that you are not using will not see that
change.. One of the most apparent changes to avoid is to create a new
site within that content database, because that new site will not be
registered in the configuration database for both farms.

It seems to be quite a big deal that you cannot create a new site within
the dev content  database and have it appear in production. However, I
imagine that removing, and then reattaching the content database from
production would associate the new site to that farm, just as it did the
first time the database was associated.

This reveals what may be a solution for a problem I've been having for
quite some time.

I tried to provision Project Web Access on a Shared Service Provider. It
failed to provision, and then failed to unprovision. Even after
detaching the content database, and rebuilding the SSP, I still get the
error that the site /MSProject already exists.

Does that error come from the farm  config? Can I fix it manually?