Using rails-5.0.7.1
(according to bundle show rails
)
I wrote a migration which adds the "uuid-ossp"
extension, and the SQL gets executed, and the extension shows up when I type \dx
in the psql
console. However, the functions that this extension provides (such as uuid_generate_v4
) do NOT show up when I type \df
, and so any attempt to use the functions that should be added fails.
When I take the SQL from the ActiveRecord migration and copy+paste it into the psql
console directly, everything works as expected - extension is added, and functions are available.
class EnableUuidOssp < ActiveRecord::Migration[5.0]
def up
enable_extension "uuid-ossp"
end
def down
disable_extension "uuid-ossp"
end
end
Here is the output:
$ bundle exec rake db:migrate
== 20190308113821 EnableUuidOssp: migrating ==============================
-- enable_extension("uuid-ossp")
-> 0.0075s
== 20190308113821 EnableUuidOssp: migrated (0.0076s) =====================
^ this all appears to run successfully, but no functions are enabled. Which means future SQL that includes statements such as ... SET uuid = uuid_generate_v4() ...
fail with the this error HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Going directly into psql
and typing:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
^ This installs the extension and makes the functions avaiable.
Okay, so if I take the above SQL and rewrite my migration this way:
...
def up
execute <<~SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SQL
end
...
^ this migration will run without error, yet it will still not make the functions available.
So, the same copy+paste SQL that works in psql
doesn't work via the ActiveRecord execute
method, which really puzzles me. I'm not sure what piece I'm missing that's causing this to fail.
I assume that the schema where the extension is installed is not on your search_path
.
You can see that schema with
\dx "uuid-ossp"
Try to qualify the functions with the schema, like in public.uuid_generate_v4()
.