Check constraints in migration not adding constraint in schema.rb

[ingnam]

Steps to reproduce

This is a migration file

class AddCategoryNameConstraint < ActiveRecord::Migration[4.2]
  def up
    ActiveRecord::Base.connection.execute(%q(
      CREATE OR REPLACE FUNCTION duplicate_category_name(cate_id INTEGER, cate_name TEXT, translation_locale TEXT)
        RETURNS BOOLEAN
      AS
      $$
        DECLARE
          cate_parent_id int;
          cate_category_set_id int;
        BEGIN
          SELECT categories.parent_id, categories.category_set_id INTO cate_parent_id, cate_category_set_id FROM categories WHERE id = cate_id;
          IF (SELECT count(cat1.id) FROM categories as cat1 INNER JOIN category_translations ct1 ON cat1.id = ct1.category_id AND cat1.id != cate_id AND ( (cate_parent_id IS NULL AND  cat1.parent_id IS NULL) OR cat1.parent_id = cate_parent_id) AND ct1.name = cate_name AND cat1.category_set_id = cate_category_set_id AND ct1.locale = translation_locale AND discontinued_at='1970-01-01 00:00:00') > 0
          THEN RETURN false;
          ELSE RETURN true;
          END IF;
        END;
      $$ LANGUAGE PLpgSQL;
    ))
    ActiveRecord::Base.connection.execute("ALTER TABLE category_translations ADD CONSTRAINT duplicate_category_name_check CHECK(duplicate_category_name(category_id, name, locale)) NOT VALID;")
  end

  def down
    ActiveRecord::Base.connection.execute("ALTER TABLE category_translations DROP CONSTRAINT IF EXISTS duplicate_category_name_check;")
    ActiveRecord::Base.connection.execute("DROP FUNCTION IF EXISTS  duplicate_category_name(cate_id INTEGER, cate_name TEXT, translation_locale TEXT);")
  end
end

When I run this migration, it is adding this line on schema.rb

t.check_constraint nil, name: "duplicate_category_name_check"

So when I run rake db:schema:load, it is throwing the error below:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near ")"
LINE 1: ... varying, CONSTRAINT duplicate_category_name_check CHECK ())
                                                                     ^
/bundle/vendor/ruby/3.2.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `exec'
/bundle/vendor/ruby/3.2.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `async_exec'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `block (2 levels) in execute'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:46:in `block in execute'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
/bundle/vendor/ruby/3.2.0/gems/activesupport-6.1.3.2/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:45:in `execute'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:322:in `create_table'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/migration.rb:929:in `block in method_missing'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/migration.rb:897:in `block in say_with_time'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/migration.rb:897:in `say_with_time'
/bundle/vendor/ruby/3.2.0/gems/activerecord-6.1.3.2/lib/active_record/migration.rb:918:in `method_missing'
/app/db/schema.rb:426:in `block in <top (required)>'

On db:migrate, it was suppose to add the line below in schema.rb:

t.check_constraint "duplicate_category_name(category_id, name, locale)", name: "duplicate_category_name_check"

System configuration

Rails version:
6.1.3.2

Ruby version:
3.2

[fatkodima]

Was able to reproduce on 6.1.3.2, but works as expected on main - it was fixed in between. So you need to upgrade.

[skipkayhil]

@fatkodima thank you for looking into this. Would you mind checking 7-0-stable as well? Just want to make sure its been backported if possible

[fatkodima]

Just checked - works as expected on that too.

[skipkayhil]

Perfect, I'm going to close then! As suggested the solution will be to upgrade to 7.0 since 6.1 is not receiving bug fixes.

[ingnam]

This issue seems to be fixed on this pull request:
#43963

Since I cannot upgrade to newer version at the moment, I fixed by monkey patching the particular function.