Place function definitions at the beginning of schema in order to use it below

[perezpaya]

We are experiencing a problem with the way the dump is generated into the schema.

Generated schema puts function definitions at the bottom

ActiveRecord::Schema.define(version: 2019_06_04_171454) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "unaccent"
  
  create_table "products", force: :cascade do |t|
    t.string "name"
    t.index "ts_tokenize((name)::text)", name: "index_product_name_tokenized", using: :gin
  end

  create_function :ts_tokenize, sql_definition: <<-SQL
      CREATE OR REPLACE FUNCTION public.ts_tokenize(text)
       RETURNS tsvector
       LANGUAGE sql
       IMMUTABLE
      AS $function$
      SELECT to_tsvector(public.unaccent('public.unaccent', $1))
      $function$
  SQL
end

This creates a failure when loading the schema at our test suite because it tries first to create the index depending in the function that was not defined yet.

If we manually change the order, and define the function just after enabling extensions we experience no problem at all, but every migration/dump resets the default order making this fail.

Probably a fix for this might be defining the functions at the beginning of the schema in order to have it available for the rest of the code.

Thank you.

[perezpaya]

Hello again, is there any update on this?

I believe there must be a way to easily achieve this by adding a kind of priority order to Fx#create_function on schema dumps.

We are manually changing back the order on every commit, because we have indexes depending on our function and crashes our build / tests pipelines if is not done before committing.

Thanks again

[perezpaya]

@teoljungberg would you accept a pull request fixing this?

Thanks

[teoljungberg]

I'd be happy receiving a PR for adding this if it's behind a configuration flag, and that the existing behavior is kept intact.

[jrochkind]

I'm running into this problem too.

I used a function as a default value in a table.

create_table "somethings" do
  # ...
   t.string "some_column", default: -> { "my_postgres_function()" }

Fails on db:schema:load, because it tries to create the table with the default set to a postgres function call, before the postgres function has been created.

Works great if i manually move the create_function to the top of the schema.rb.

This was my use case for using fx, so it's disapointing that it won't currently work. (Curious what use cases it would break to make it default/always have functions first?).

Any chance of movement on this?

[jrochkind]

There is some movement, see #42 and #53

[teoljungberg]

Fixed by #53.