Triggers
CREATE TRIGGER
CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();What this does
Reads keep flowing (SHARE ROW EXCLUSIVE does not conflict with ACCESS SHARE or ROW SHARE), but writes block for as long as the lock is held, and SHARE ROW EXCLUSIVE is self-exclusive, so a second CREATE TRIGGER or FOREIGN KEY on the same table has to wait too.
Safe rewrite
Minimize lock duration with lock_timeout and retry logic
SET lock_timeout = '2s';
CREATE TRIGGER orders_set_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- Retry in a loop if lock_timeout expires