The (lack of) documentation suggests that this behaviour is an implementation detail, and is therefore undefined and subject to change at any time.
This is in stark contrast to CREATE FULLTEXT INDEX, where you have to specify the name of an index to attach to -- AFAIK, there is no undocumented FOREIGN KEY
syntax to do the equivalent (though theoretically, there could be in the future).
As mentioned, it does make sense that SQL Server chooses the smallest physical index with which to associate the foreign key. If you change the script to create the unique constraint as CLUSTERED
, the script "works" on 2008 R2. But that behaviour is still undefined and should not be relied upon.
As with most legacy applications, you'll just have to get down to the nitty-gritty and clean things up.
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)
Best Answer
Assuming current Postgres 9.6, it can work.
Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run
ANALYZE
for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.Related answer with more details:
Since PostGIS is an extension, this new feature of Postgres 9.6 is particularly relevant:
Bold emphasis mine.
The use of indexes is bound to operators. Various PostGIS spatial functions are transformed to use operators internally when the query is planned. This related answer on SO has more explanation:
Assuming Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick:
Here is a related blog post by Michael Paquier.