On Friday, July 26, 2013 11:53:47 AM UTC-7, [email protected] wrote:
>
> Hello,
> I'm trying to write some migrations that remove indexes. We currently use
> schemas in postgres quite heavily and I think I'm running into a bug with
> the drop table qualification.
>
> I have this as the drop table line:
> drop_index :test__accounts, :oldindex
>
> and when it runs I get this error:
> PG::Error: ERROR: index "test_accounts_oldindex_index" does not exist
>
> But if I go into my database and check I see that I have the index in the
> test schema:
> select schemaname, tablename, indexname from pg_indexes where indexname =
> 'test_accounts_oldindex_index';
>
> schemaname | tablename | indexname
> ------------+-----------+------------------------------------------
> test | accounts | test_accounts_oldindex_index
>
> Doing it manually with the generated index name works if I prefix it with
> the schema, without it it fails.
>
> Is this a bug or do I need to do something different to qualify the table
> for drop table?
>
Well, I don't think it's a bug, though I do admit the behavior is
undesirable. The issue comes about as follows. When you create an index
on a schema qualified table, it automatically creates that index in the
same schema. Note that Sequel doesn't explicitly qualify the index with a
schema, it's just that PostgreSQL's defaults to putting indexes in the same
schema as the table. Example:
DB.create_schema(:test)
# CREATE SCHEMA "test"
DB.create_table(:test__a){Integer :a, :index=>true}
# CREATE TABLE "test"."a" ("a" integer)
# CREATE INDEX "test_a_a_index" ON "test"."a" ("a")
PostgreSQL uses a global index namespace, or a least a schema level index
namespace instead of a table level index namespace, so when dropping an
index, it doesn't use the table:
DB.drop_index :test__a, :a
# DROP INDEX "test_a_a_index"
If the schema for the table/index is not in the search path, such a query
will fail.
Sequel currently does not really support the idea of schema qualified
indexes. PostgreSQL doesn't really support the idea either, at least in
the sense that you can't specify a different schema when creating the
index. However, it's possible to get things to work automatically,
assuming you reference the table the same way, using the patch at
http://pastie.org/pastes/8178869/text, with the following result:
DB.drop_index :test__a, :a
DROP INDEX "test"."test_a_a_index"
This patch looks at the schema on the table you passed in, and uses that
same schema for the index. I think that will solve the issue in the
majority of cases. Please let me know if that fixes the issue for you. If
so, it will probably be applied after I add the appropriate tests.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
