Auto-Update Fields: updated_at

Learning Objectives

Understand how ON UPDATE CURRENT_TIMESTAMP and trigger-based updated_at columns work
Identify the bug where updated_at changes even on read-only or no-op operations
Design test cases that verify auto-update behavior and detect race conditions

How Auto-Update Columns Work

An updated_at column is supposed to track the last time a row's data was meaningfully changed. In MySQL, the simplest way to implement this is:

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Every time a row is updated via UPDATE, MySQL automatically sets updated_at to the current timestamp. In PostgreSQL, there is no built-in ON UPDATE trigger — developers must create an explicit trigger function:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Both approaches have the same surface behavior but differ in edge cases that matter for testing.

The No-Op Update Bug

A classic bug: updated_at changes even when the update sets a column to the same value it already had.

-- Row: { id: 1, status: 'active', updated_at: '2024-03-01 10:00:00' }
UPDATE orders SET status = 'active' WHERE id = 1;
-- updated_at is now '2024-03-15 09:23:41' — even though nothing changed!

MySQL's ON UPDATE CURRENT_TIMESTAMP fires on any UPDATE statement touching the row, regardless of whether any column value actually changed. PostgreSQL triggers have the same behavior by default — NEW equals OLD but the trigger still runs.

Test case: Find a record's current updated_at. Issue an UPDATE that sets every column to its current value. Re-read updated_at. If it changed, the implementation has the no-op update bug. This matters for cache invalidation, sync systems, and audit trails.

Race Conditions in Timestamps

When two requests update the same row within the same second (or microsecond, on high-precision systems), the following race condition emerges:

Request A reads updated_at = '2024-03-15 10:00:00.000'
Request B updates the row → updated_at = '2024-03-15 10:00:00.452'
Request A updates the row → updated_at = '2024-03-15 10:00:00.789'
The application uses updated_at as an optimistic lock check
Request A's update succeeds even though it was working with stale data

Test case: Use concurrent requests (or a sleep/delay in the application) to simulate two simultaneous updates. Verify that the optimistic locking mechanism (if any) correctly rejects the stale write.

Testing Auto-Update Behavior

Test Scenario	Expected Result
INSERT a new row	`updated_at` equals `created_at` (or is NULL if not set)
UPDATE with changed data	`updated_at` is later than before
UPDATE with identical data (no-op)	Depends on spec: should NOT change if using `IF NEW != OLD` guard
SELECT only (no UPDATE)	`updated_at` must not change
Bulk UPDATE via admin script	Every affected row's `updated_at` should change
Concurrent updates	`updated_at` reflects the last writer; no data corruption