ActiveRecord with multi-schema postgresql database

Hi

The application we are developing uses schemas to isolate client data.
What I was looking to do was to have a connection for each active
schema - Guess I should have looked first!

The only thing I can see to do is to set the schema_search_path for
model classes before issuing any commands.

My question is is this safe? My understanding is that each request
will call the controller do any database work and render its view
without being interupted. Separate FCGI processes will have separate
database connections.

I have spent a couple of days looking at ActiveRecord to see if there
was another way as this method seems a bit of a cludge.

Has anyone tried this, or does anyone know a different way to achieve
this?

I am reluctant to change the schema as we are likely to have 600+
schemas in this system.

Thanks

Kieran

On 9/19/06, Kieran [email protected] wrote:

The application we are developing uses schemas to isolate client data.
What I was looking to do was to have a connection for each active
schema - Guess I should have looked first!

The only thing I can see to do is to set the schema_search_path for
model classes before issuing any commands.

You could use a before_filter to
ActiveRecord::Base.connection.schema_search_path =
“myapp_#{customer_name},myapp_shared”

My question is is this safe? My understanding is that each request

will call the controller do any database work and render its view
without being interupted. Separate FCGI processes will have separate
database connections.

What is at risk? How would connections per schema solve it?

I have spent a couple of days looking at ActiveRecord to see if there

was another way as this method seems a bit of a cludge.

Has anyone tried this, or does anyone know a different way to achieve
this?

I am reluctant to change the schema as we are likely to have 600+
schemas in this system.

How do you do it in PHP and Java?
Explicitly qualify all tables with their schema?

jeremy