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

Question

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.

score 2 · Accepted Answer · answered Mar 11 '19 at 14:24

2

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

answered Mar 11 '19 at 14:24

Laurenz Albe

209,280
17
206
263

Silly me -- thanks. Was totally working, but when I typed `\df` I wasn't specifying the schema, so I only thought the functions weren't available. Between that and mixing up several different versions of this migration I wasn't both installing the extension correctly AND looking at the resulting functions on the proper schema, at the same time. – jefflunt Mar 11 '19 at 14:56
Thanks for the `public.uuid_generate_v4()` tip! – nikoskip Sep 18 '20 at 02:02

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

Here is my migration code:

What does work

And yet, what doesn't work

1 Answers1