Postgres: second UPDATE in the same transaction skips "updated_at changed" checks in AFTER triggers

An AFTER UPDATE trigger that enqueues work only when some tracked column changed, or when updated_at changed (set to now() by a BEFORE trigger), silently does nothing for the second update of the same row inside one transaction. now() is the transaction start time, so new.updated_at = old.updated_at. Changes to columns the trigger does not list (for example created_at) are then lost to the queue. This surfaces in pgTAP tests, which run everything in one transaction.

List every column the downstream consumer can filter on in the trigger's "changed columns" array (for example: || case when new.created_at is distinct from old.created_at then array['created_at'] else '{}' end). Do not rely on updated_at alone. In pgTAP, test the second write of a row on purpose to catch this. Also remember that BEFORE UPDATE OF col fires whenever col is in the SET list, even if its value does not change: only add the column to SET when it is really written (build the SQL fragment conditionally).

now() and current_timestamp are frozen for the whole transaction, so a BEFORE trigger that sets updated_at := now() gives the same value on every update in that transaction.