Skip to content

Understanding Postgres Database Triggers

Tags: databases, postgres • Categories: Learning

Table of Contents

I’ve always known that my knowledge and usage of postgres was elementary. Triggers and extensions were two topics that intrigued me, and I recently found a good excuse to spend the time to understand them. Here’s what I learned!

Introduction to Triggers

Here’s how triggers work:

  • TG_ARGV contains an array of arguments passed to the trigger
  • Which arguments are passed to the trigger are defined when it is created by CREATE TRIGGER
  • Here’s an example function creation SQL CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
    • audit is the schema/namespace
    • $body$ indicates the start of the method body, think of it as a heredoc string
  • It seems as though you can define a function multiple times, with different args, like elixir. Neat and confusing.
  • To execute functions SELECT schema.function_name('param');. Think of SELECT as a eval is SQL in this context.
  • Variables are set using DECLARE var_name text = 'VARIABLE_VALUE'; although is not valid in a normal SQL statement and only in a FUNCTION
  • RAISE EXCEPTION 'text' allows you to throw an exception within a function
  • $ can be used like a " in any normal language. Kind of like a heredoc.
  • Adding comments to columns, tables, and schema is a powerful way to document your SQL and even more important when creating new schema in an extension.
  • I have no idea how you debug these outside of caveman trial & error-style debugging. These seem like the most obvious downside of triggers: you want them to be as small and simple as possible to reduce debugging risk.
  • What’s interesting is it seems like the function logic uses a completely separate set of SQL than the SQL you use to query. There’s if statements, variables, a ROW() function for insertions, etc. This feels really weird as a design decision.
    • Doing more research here looks like there is a specific extension of the standard postgres SQLfor extensions/stored procedures (functions).
    • When functions are defined, you’ll see $$ LANGUAGE plpgsql; at the end of the definition indicating the specific language the extension is written in.
    • You can write extensions in multiple languages, plpgsql is just one of them

Some SQL Findings

Everytime I do something semi-serious with SQL I come away with a bunch of learnings:

  • '{one, two, three}'::text[] and ARRAY['one', 'two', 'three'] are the same expression
  • Access the array by using SELECT (ARRAY['version_col', 'changed_by', 'changed_timestamp'])[1]; the parens are important.
  • SELECT last_value FROM entity_id_seq will get you the auto increment counter
  • SELECT setval('entity_id_seq', (SELECT MAX(id) FROM entity)) can update the counter for that table if it’s not matching reality (after an import or data issue)
  • A schema is bascially a namespace within a database.

    Installing & Using the Audit Trigger

    My primary goal was to setup audit logging for a json payload on a particular table. I found an open source trigger and made some improvements to it. It’s a great example of a medium-complexity trigger, what they look like and how they work.

The trick was how do I get it integrated + tested in my application? Here’s how I built the migration and test for the audit log:

defmodule App.Repo.Migrations.AddAuditTrigger do
  use Ecto.Migration

  def up do
    abs_path = Path.expand("priv/repo/audit_trigger.sql")
    database_url = Application.fetch_env!(:app, App.Repo)[:url]

    # for a separate time, but executing multiple SQL statements in Ecto is impossible
    {:ok, result} =
      PsqlExecutor.execute_sql_file(database_url, abs_path)


    table = App.Entity.__schema__(:source) |> to_string()

    # create a JSON-like string of the columns we want to exclude
    # we only want to watch the crawl_payload (and the id, which should never change)
    columns =
      (App.Entity.__schema__(:fields) -- [:crawl_payload, :id])
      |> Enum.join(",")

    :ok =
      SELECT audit.audit_table(
        -- audit rows, not just tables
        -- output sql which triggered the audit
        -- list of *exclusion* columns

  def down do
    :ok = execute("DROP SCHEMA IF EXISTS audit CASCADE;")

And here’s an example application-layer test case:

test "triggers" do
  entity = fixture(:entity)
  entity_record = App.Entity.create_entity_from_payload(entity)

  App.Entity.changeset(entity_record, %{json_payload: %{"something" => "changed"}})
  |> App.Repo.update!()

  # raw ecto execute select on audit.logged_actions
  result = App.Repo.sql("SELECT * FROM audit.logged_actions")

  assert result.num_rows == 2
  [insert_audit, update_audit] = result.rows

  assert, -1) == nil
  assert, -1) ==

  previous_payload_column = -4
  changed_payload_column = -3

  assert nil ==
  , changed_payload_column)

  assert %{"json_payload" => "{\"something\": \"changed\"}"} ==
  , changed_payload_column)

Merge Multiple Git Branches

One common pattern I run into with working with zombie open source projects (like audit trigger) is there are a bunch of PRs that should be merged, that aren’t. But I want them merged into my fork; and I’d love to try to convince the original author to merge them as well.

One trick I’ve developed for myself is a custom .git-custom-branch file along with a custom zsh script to merge all the branches together into a custom branch:


This makes it easy to cherry-pick branches and mix them in with my own. You can easily pluck PRs to your local repo using the excellent GH cli gh pr checkout 32.