Cool Stuff in PostgreSQL 10: Auto-logging

We started off by creating a logging infrastructure, then arranging for a single table to use it.

Rather than repeat that work for each table, let’s use a relatively although not completely new feature: <span class="pln">EVENT TRIGGER</span>. The idea here is that we fire a trigger on <span class="pln">CREATE TABLE</span> and see to it that the table is logged. We’ll write the trigger first, even though in reality, we’d need to load the function it calls first.

  1. <span class="pln">CREATE EVENT TRIGGER add_logger</span>
  2. <span class="pln"> ON ddl_command_end</span>
  3. <span class="pln"> WHEN tag IN </span><span class="pun">(</span><span class="str">'create table'</span><span class="pun">)</span>
  4. <span class="pln"> EXECUTE PROCEDURE add_logger</span><span class="pun">();</span>
  6. <span class="pln">COMMENT ON EVENT TRIGGER add_logger IS </span><span class="str">'Ensure that each table which is not a log gets logged'</span><span class="pun">;</span>

The magic happens inside <span class="pln">add_logger</span><span class="pun">()</span>, but it’s magic we’ve already seen. First, we’ll get the table’s name and schema using <span class="pln">pg_event_trigger_ddl_commands</span><span class="pun">()</span>, filtering out tables which are already log tables. The test here is crude and string-based, but we could easily go to schema-based ones.