Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 email.

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);

with

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?

Update

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.

share|improve this question
    
Do you issue the SET search_path and all CREATE statements from the same session? –  dezso Nov 14 '12 at 7:28

1 Answer 1

up vote 5 down vote accepted

Given the following setup:

regress=> CREATE SCHEMA A;
CREATE SCHEMA
regress=> CREATE SCHEMA B;
CREATE SCHEMA
regress=> SET search_path = B, public;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

I cannot reproduce the problem you report in PostgreSQL 9.2:

regress=> SET search_path = A, B;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
regress=> CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

However, rather than using the search_path, it's safer to use explicit schema-qualification. For example, I'd re-write the above as:

regress=> RESET search_path;
RESET
regress=> SHOW search_path ;
  search_path   
----------------
 "$user",public
(1 row)

CREATE TABLE B.bar(email text);
CREATE UNIQUE INDEX b.index_bar_on_email ON b.bar USING btree (email);
CREATE TABLE A.bar(email text);
CREATE UNIQUE INDEX index_bar_on_email ON A.bar USING btree (email);

The indexes are automatically created in the schema of their associated table; see:

regress=> \di B.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 b      | index_bar_on_email | index | craig | bar
(1 row)

regress=> \di A.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 a      | index_bar_on_email | index | craig | bar
(1 row)

Update based on question change:

Yes, what you've shown does look like a Rails adapter issue. It's checking to see whether the index exists in any schema. It should be checking to see whether the first table of the given name in the search_path has the named index.

I would write the query differently. I'd leave off the join on pg_class entirely, instead using a cast to regclass to handle search_path resolution for me. I'd use the resulting oid to search for the index. Compare original, then updated, below. Note that the updated query does require search_path to be set first.

regress=> 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 = 'bar'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('b','a') )
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey |  oid  | amname 
--------------------+-------------+--------+-------+--------
 index_bar_on_email | t           | 1      | 28585 | btree
 index_bar_on_email | t           | 1      | 28592 | btree
(2 rows)

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, 'bar'::regclass::oid, am.amname
 FROM pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND 'bar'::regclass = d.indrelid
 AND i.relam = am.oid
 ORDER BY i.relname                                                              
;
      relname       | indisunique | indkey | regclass | amname 
--------------------+-------------+--------+----------+--------
 index_bar_on_email | t           | 1      | 28585    | btree
(1 row)
share|improve this answer
    
Thanks Craig... I dug a little deeper based on your proof of concept here. Question updated. –  jordanpg Nov 14 '12 at 19:03
    
@jordanpg Updated –  Craig Ringer Nov 14 '12 at 22:20
    
I'm planning on doing a rails pull request for this once I test your proposed change and make sure this will fix the problem... I'll link to this post and mention your contribution by name, if that's OK. –  jordanpg Nov 15 '12 at 16:35
    
@jordanpg Sure. It's pretty trivial - and I don't know Rails, so I can't say the context is necessarily right - but so long as search_path is properly set, I'm pretty sure it's what you want. If search_path isn't set you have to use a join on pg_namespace and then order by, with checks for access using the system info functions (postgresql.org/docs/current/static/functions-info.html) like has_schema_privilege to simulate the search path. –  Craig Ringer Nov 15 '12 at 22:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.