postgresqlactiverecordruby-on-rails-5postgresql-extensions

ActiveRecord: "uuid-ossp" extension added, but no uuid functions available


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.

Here is my migration code:

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.

What does work

Going directly into psql and typing:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

^ This installs the extension and makes the functions avaiable.

And yet, what doesn't work

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.


Solution

  • 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().