Disabled triggers not respected

[nowwireless-andy]

When using migra to convert a schema of test1 to match test2, a disabled trigger in test2 is turned into an active trigger in test1.

pg_dump test1 > test1.sql test1.sql.txt

pg_dump test2 > test2.sql test2.sql.txt

migra --unsafe postgresql:///test1 postgresql:///test2 |psql test1

pg_dump test1 > test1_after_migra.sql test1_after_migra.sql.txt

diff test2.sql test1_after_migra.sql

> 1
84,85d84
< 
< ALTER TABLE public.t DISABLE TRIGGER do_nothing_trigger;

Let me know if you need anything else. Migra version is migra-3.0.1612089247, pg version is in the pg_dump files.

[martsberger]

This is an example of a more general problem. Migra is unable to create triggers with an enabled status other than "Enabled". If triggers differ in the enabled status in any way migra's generated schema migration is to drop and re-add the trigger based on the full_definition, but this does not include the enabled status, which is actually changed with an ALTER TABLE statement (seems like an odd choice by postgres to change a trigger status by altering the table, but we work with what we work with).

A change that works for me is to include the ALTER TABLE statement in the create_statement method of InspectedTrigger in the schemainspect project. Here is the current definition of that method and this is what works for me:

@property
def create_statement(self):
    status_sql = {
        'O': 'ENABLE TRIGGER',
        'D': 'DISABLE TRIGGER',
        'R': 'ENABLE REPLICA TRIGGER',
        'A': 'ENABLE ALWAYS TRIGGER'
    }
    table_alter = f'ALTER TABLE {self.schema}.{self.table_name} {status_sql[self.enabled]} {self.name}'

    return self.full_definition + ";\n" + table_alter + ";"

Postgres docs for possible values of tg_enabled here https://www.postgresql.org/docs/current/catalog-pg-trigger.html Postgres alter table reference specifying how to alter a trigger to enable/disable/replica/always https://www.postgresql.org/docs/9.6/sql-altertable.html