Are `AFTER UPDATE` triggers `FOR EACH ROW` atomic?

Question

I'd like to insert a row inside a "history" table whenever the original row gets modified (updated). I'd like to use original row's ID as a FOREIGN KEY for the history table. But this ID might change.

Have a look at the example below:

CREATE TABLE account
(
 id BIGSERIAL PRIMARY KEY,
 username TEXT NOT NULL,
 balance BIGINT NOT NULL DEFAULT 0
);
CREATE TABLE account_balance_change
(
 account_id BIGINT NOT NULL,
 diff BIGINT NOT NULL,
 ts TIMESTAMPTZ NOT NULL,
 FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE FUNCTION dump_diff()
RETURNS TRIGGER
LANGUAGE plpython3u
AS
$$
from datetime import datetime
now = datetime.now()
old = TD['old']
new = TD['new']
diff = new['balance'] - old['balance']
query = f"""
INSERT INTO
 account_balance_change
 (
 account_id,
 diff,
 ts
 )
VALUES
 (1,ドル 2,ドル 3ドル)
;
"""
# i know it should be cached, but for the sake of simplicity...
stmt = plpy.prepare(query, ["BIGINT", "BIGINT", "TIMESTAMPTZ"])
# see how it's using new['id'], so that there can be no mistake in the new record
stmt.execute([new['id'], diff, now])
$$;
CREATE TRIGGER after_account_balance_update_trigger
AFTER UPDATE OF balance ON account
FOR EACH ROW
EXECUTE FUNCTION
 store_diff()
;

The expected behavior is that every time someone UPDATEs any account's balance value, the balance difference and date and time of such change would be written into the account_balance_change table.

But as per system requirements, the ID might change or get deleted.

Therefore, are we safe to assume that new['id'] won't change at the time of this trigger being executed? Can we actually end up in a spot where a race condition occurred and new['id'] no longer exists in the database?

It all basically boils down to one question: Are AFTER UPDATE TRIGGERs atomic with respect to the UPDATE operation they belong to?

• gotqn: stackoverflow.com/a/21464646/1080354