Replacing functions when updating, instead of dropping and recreating

[jlogsdon]

As we've begun adding functions to our system we've found a minor but annoying pain point when updating functions. With the current behavior a call to update_function will first drop and then recreate the function. For simple use-cases this behavior works fine, but as your functions grow and begin to depend on each other this no longer works. The problem becomes worse with the addition of views through the scenic gem.

As an example, imagine this scenario:

• A function age_years(dob date) returns int8 for calculating the number of years since dob.
• A function age_bracket(dob date) returns text returning a string of "child", "adolescent" or "adult" using age_years.
• A view (through scenic) that uses both age_years(dob date) and age_bracket(dob date).

In the current system we have two options:

1. The migration will drop all dependencies, and then rebuild. This is essentially a non-starter in terms of complexity.
2. Use CREATE OR REPLACE FUNCTION in our function definitions, and in the migrations manually execute the correct up and down versions.

My current thinking is that adding a replace_function for migrations, with a generator flag --replace, would be enough to completely support these scenarios. Perhaps even make the default replace_function and keep the "tear it all down" update_function approach for when actually needed.

An alternative, that I don't like as much, would be changing update_function to "realize" when a script has OR REPLACE and skip dropping those functions. Although instead of a new replace_function method we could add the flag replace: true to this method instead.

[teoljungberg]

Since update_function should be enough for the simple cases, I want to leave it as the default.

I like adding replace_function, it is the route scenic took. And they did not add a --replace flag, so I think we can opt out of adding that too and simply document it.

[jlogsdon]

Sounds good to me! I'll start work on a PR to add replace_function soon.

[reidab]

I'd love to see this too, since all my trigger handler functions fail to update because the triggers depend on them.

I've worked around it by using CREATE OR REPLACE and writing update migrations like this:

class UpdateFunctionHandleCampgroundUpdateToVersion2 < ActiveRecord::Migration[5.0]
  def up
    create_function :handle_campground_update, version: 2
  end

  def down
    create_function :handle_campground_update, version: 1
  end
end

[bbugh]

I had an issue where the return type of the function changed from timestamp without time zone to timestamp with time zone, and PG doesn't allow you to replace functions with different return types. update_function was not dropping it first, so I was surprised to see this issue. I was able to resolve it by dropping and then creating:

class UpdateFunctionThingToVersion2 < ActiveRecord::Migration[7.0]
  def up
    drop_function :thing
    create_function :thing, version: 2
  end

  def down
    drop_function :thing
    create_function :thing, version: 1
  end
end

[teoljungberg]

As mentioned in another PR - as this is workable but a bit clunky. I'll close this.