Postgresql and schemas

Rails
1

There are some bugs somewhere in the stack in which the schemas are
not automatically chosen.
My database.yml includes the following:
schema_search_path: iaa_development

This creates the database in the correct schema, but subsequent
queries don’t prepend the actual schema name in the query (i.e.)

SELECT * FROM users WHERE (username = E’admin’) LIMIT 1

or

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc,
a.attnotnull
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = ‘users’::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

this should be qualified somehow:
SELECT * FROM scheam_name.users WHERE (username = E’admin’) LIMIT 1

I could be wrong but I know PG very well even if I am new to Rails.

TIA

2

On Oct 24, 2007, at 12:45 , tresero wrote:

There are some bugs somewhere in the stack in which the schemas are
not automatically chosen.
My database.yml includes the following:
schema_search_path: iaa_development

This creates the database in the correct schema,

You mean your migrations install database objects (tables, functions
and such) in the correct schema?

but subsequent
queries don’t prepend the actual schema name in the query (i.e.)

I haven’t had any issues with this. If you execute “show search_path”
from within your app, what do you get?

this should be qualified somehow:
SELECT * FROM scheam_name.users WHERE (username = E’admin’) LIMIT 1

Qualification is only necessary if there’s another object with the
same name that occurs prior in the search path.

Michael G.
grzm seespotcode net

3

You pretty much have to use search path.

The first matching table in the search path is taken to be the one
wanted. If there is no match in the search path, an error is reported,
even if matching table names exist in other schemas in the database.

Either rails or the driver is not passing the search path under
certain conditions. The above code was from acts_as_authenticated.

I will do some more experimenting, I am new to ruby/rails, so I don’t
know how much help I can be.