Thanks Jeremy
That fixed my issues and seems to work great.
Another (unrelated) issue we had was creating a table that inherits from
another one. We solved it via:
def create_table_from_generator(name, generator, options)
sql = create_table_sql(name, generator, options)
if options.has_key? :inherits
sql = "#{ sql } INHERITS (#{ quote_schema_table(options[:inherits])
})"
end
execute_ddl(sql)
end
but I'm not sure that is the best solution or if it would make sense to be
changed somewhere postgres specific. Not a big deal anyway.
Thanks for the help,
Erik
On Fri, Jul 26, 2013 at 4:00 PM, Jeremy Evans <[email protected]>wrote:
> 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 a topic in the
> Google Groups "sequel-talk" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sequel-talk/SXZQQObxTZU/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>
>
>
--
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.
