23

I want to verify correctness of database migrations which add triggers to some tables. I'm using sqitch, so I'd like to find a way to check it with SQL queries. I believe it should be possible with postgres system tables, but I currently can't find a way to do this.

Improve this question

2 Answers 2

Reset to default
31

Use the catalog pg_trigger.

A simple lookup for a table books:

select tgname
from pg_trigger
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     
---------------
 books_trigger
(1 row)

Using pg_proc to get the source of the trigger function:

select tgname, proname, prosrc 
from pg_trigger
join pg_proc p on p.oid = tgfoid
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     |    proname    |                    prosrc
---------------+---------------+------------------------------------------------
 books_trigger | books_trigger |                                               +
               |               | begin                                         +
               |               |     if tg_op = 'UPDATE' then                  +
               |               |         if new.listorder > old.listorder then +
               |               |             update books                      +
               |               |             set listorder = listorder- 1      +
               |               |             where listorder <= new.listorder  +
               |               |             and listorder > old.listorder     +
               |               |             and id <> new.id;                 +
               |               |         else                                  +
               |               |             update books                      +
               |               |             set listorder = listorder+ 1      +
               |               |             where listorder >= new.listorder  +
               |               |             and listorder < old.listorder     +
               |               |             and id <> new.id;                 +
               |               |             end if;                           +
               |               |     else                                      +
               |               |         update books                          +
               |               |         set listorder = listorder+ 1          +
               |               |         where listorder >= new.listorder      +
               |               |         and id <> new.id;                     +
               |               |     end if;                                   +
               |               |     return new;                               +
               |               | end
(1 row)

Example of the pg_get_triggerdef() function usage:

select pg_get_triggerdef(t.oid) as "trigger declaration"
from pg_trigger t
where not tgisinternal
and tgrelid = 'books'::regclass;

                                             trigger declaration                
--------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER books_trigger BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger()
(1 row)

In a Sqitch verify script you can use an anonymous code block, e.g.:

do $$
begin
    perform tgname
    from pg_trigger
    where not tgisinternal
    and tgrelid = 'books'::regclass;
    if not found then 
        raise exception 'trigger not found';
    end if;
end $$;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I've actually tried this and was quite confused. Weird thing is: i'm getting 7 tgnames looking like this RI_ConstraintTrigger_c_195564, instead of 1. I'm using postgresql 9.4.
They are internal constraint triggers. Use not tgisinternal to skip them, like in the edited answer.
This solution might not work for sqitch as asked in the question. I having the same problem. Sqitch needs your query your to return an error in order to correctly verify the deploy code. All the queries mentioned in the above solution will only give empty result if the trigger doesn't exists. Since no error error is thrown, sqitch will assume it is verified correctly.
From what I understood on how sqitch works, it first runs the deploy script which will be creating adding the new trigger. It then runs the verify script which checks the if the trigger is added correctly. If verify script runs without any errors, it will assume everything is fine even if deploy failed for some reason. If the verify script would have thrown some error, sqitch would have got to know that something failed so it will run the revert script.
@pratpor - Use DO command with ASSERT or RAISE In the verify script. See the updated answer.
3

to check by trigger name you can do it:

select trigger_name from information_schema.triggers 
WHERE trigger_name = 'your_trigger_name'

Also can select with other information.

select event_object_schema as table_schema,
       event_object_table as table_name,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') as event,
       action_timing as activation,
       action_condition as condition,
       action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
         table_name;

Column details

  • table_schema - name of the table schema
  • table_name - name of the trigger table
  • trigger_schema - name of the trigger schema
  • trigger_name - name of the trigger
  • event - specific SQL operation: Insert, Update or Delete
  • activation - trigger activation time: After, Instead of or BEFORE
  • condition - trigger activation condition
  • definition - definition of trigger - in postgreSQL it is always EXECUTE PROCEDURE function_name()

If you don't want to select event_manipulation, you may remove the group by in the query.

reference

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.