Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app.
I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table in a non-PK field, say
If I set my schema search path with
SET search_path TO A,B;, then I can run:
CREATE TABLE foo (...);
and it creates table foo in schema A. The following migration fails, though:
CREATE TABLE bar (...); CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
Index name 'index_bar_on_email' on table 'bar' already exists
This suggests that
create table and
create unique index handle the search path differently. Is there a way to coax PG into making the indexes in schema A?
The issue seems to be related to how Rails interfaces with Postgres. After the Users table is created, the following queries are run before Rails attempts to create the index:
SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname FROM pg_class t, pg_class i, pg_index d, pg_am am WHERE i.relkind = 'i' AND d.indexrelid = i.oid AND d.indisprimary = 'f' AND t.oid = d.indrelid AND t.relname = 'users' AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('B','A') ) AND i.relam = am.oid ORDER BY i.relname SELECT a.attnum, a.attname, t.typname FROM pg_attribute a, pg_type t WHERE a.attrelid = 311384 AND a.attnum IN (2) AND a.atttypid = t.oid
Based on the result of one or both of these, which seem to be schema-agnostic queries, I am guessing that a non-empty return is telling Rails that this index already exists and so it's not even bothering to try.
For now, I'm just going to rename the indexes, but this might be a bug in the Rails Postgres adatper.