Exceptions while running trigger in Ecto Migrations

[bvjebin]

I am running into an issue where in after running triggers via ecto migrations, match error exception is thrown. Issue is reported here in the ecto repo. This is blocking the migration to be registered in the database. Can you guys see what is going wrong?

[tbash]

@bvjebin stumbled upon this in the ecto repo. If you're are still having an issue with this, the solution may be to separate the triggers into two separate execute functions. I know that this is a thing: "cannot insert multiple commands into a prepared statement" when using psql.

[liveforeverx]

@bvjebin Is it still actual, should I try to reproduce/work on it?

[bvjebin]

@liveforeverx Yes. It'd be helpful if you can work on it.

[liveforeverx]

@bvjebin Can you create some small application, where the crashes are reproducible?

[bvjebin]

@liveforeverx Just create a phoenix application or a simple Elixir application(you have to add ecto manually to the application in this case). Create a new Ecto migration using mix task(mix gen.ecto.migration). Replace the content with the following code and try to migrate it. You will see the issue.

defmodule AppServer.Repo.Migrations.AddEmailOrPhoneValidationTriggerToUsersTable do
  use Ecto.Migration

  def change do
    execute "CREATE TRIGGER InsertFieldemailPhoneNotNull BEFORE INSERT ON users
    FOR EACH ROW BEGIN
      IF (NEW.email IS NULL AND NEW.phone IS NULL) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Email and Phone cannot both be null';
      END IF;
    END;
    CREATE TRIGGER UpdateFieldemailPhoneNotNull BEFORE UPDATE ON users
      FOR EACH ROW BEGIN
        IF (NEW.email IS NULL AND NEW.phone IS NULL) THEN
          SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Email and Phone cannot both be null';
        END IF;
      END;"
  end
end