Fx dumps triggers created by other extension in schema.

[inkstak]

We start using Fx on a database using PostGIS.

When performing rails db:schema:dump, fx is dumping a trigger from the topology extension:

ActiveRecord::Schema.define(version: 2022_11_29_082047) do
  enable_extension "postgis"
  enable_extension "postgis_topology"

(...)

  create_trigger :layer_integrity_checks, sql_definition: <<-SQL
      CREATE TRIGGER layer_integrity_checks BEFORE DELETE OR UPDATE ON topology.layer FOR EACH ROW EXECUTE FUNCTION topology.layertrigger()
  SQL
end

After then, any rails db:schema:load will fail.

$ rails db:schema:load
rails aborted!
ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR:  trigger "layer_integrity_checks" for relation "layer" already exists

The trigger is dumped by the statement defined in (..)/adapters/postgres/triggers.rb :

# SELECT * FROM pg_trigger WHERE pg_trigger.tgname NOT ILIKE '%constraint%';
  oid  | tgrelid | tgparentid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+------------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+------------
 46987 |   46972 |          0 | layer_integrity_checks |  46971 |     27 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
(1 row)

I tried but couldn't find a way to link triggers to namespaces to include only "public" triggers as is done for functions.

[inkstak]

For those in the same situation, my current workaround is to monkey-patch Fx::SchemaDumper::Trigger

# config/initializers/fx.rb

module Fx
  module SchemaDumper
    module Trigger
      private

      def dumpable_triggers_in_database
        @_dumpable_triggers_in_database ||= Fx.database.triggers
          .reject { |trigger| trigger.name == "layer_integrity_checks" }
      end
    end
  end
end

[teoljungberg]

I'd entertain a PR that expands the sql used to dump triggers to exclude stuff from postgis.