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

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