Problem
The bump_timestamp() trigger computes the next last_modified via millisecond-precision epoch arithmetic:
previous := as_epoch(MAX(last_modified)); -- milliseconds
current := as_epoch(clock_timestamp()::TIMESTAMP); -- milliseconds
IF previous IS NOT NULL AND previous >= current THEN
current := previous + 1; -- +1 millisecond
END IF;
NEW.last_modified := from_epoch(current);This SELECT MAX(...) is not serialized across concurrent transactions. When two transactions insert/update rows with the same (parent_id, resource_name) within the same millisecond, both read the same MAX(last_modified), compute the same previous + 1, and one fails:
IntegrityError: duplicate key value violates unique constraint
"idx_objects_parent_id_resource_name_last_modified"
DETAIL: Key (parent_id, resource_name, last_modified)=(...) already exists.
The trigger's own comment acknowledges this:
If a bunch of requests from the same user on the same resource arrive in the same millisecond, the unicity constraint can raise an error (operation is cancelled).
In high-throughput deployments (multiple application pods writing to the same collection concurrently), this is not a rare edge case — it happens routinely under normal load.
Proposal: microsecond-precision bumps (no API change)
The TIMESTAMP column already stores microsecond precision natively in PostgreSQL. The collision window is artificially wide because the trigger round-trips through millisecond-truncated epoch integers.
The fix is to work in TIMESTAMP directly and bump by 1 microsecond instead of 1 millisecond — reducing the collision window by 1000x without changing any client-facing behavior:
CREATE OR REPLACE FUNCTION bump_timestamp()
RETURNS trigger AS $$
DECLARE
previous_ts TIMESTAMP;
current_ts TIMESTAMP;
BEGIN
previous_ts := NULL;
WITH existing_timestamps AS (
(
SELECT last_modified
FROM objects
WHERE parent_id = NEW.parent_id
AND resource_name = NEW.resource_name
ORDER BY last_modified DESC
LIMIT 1
)
UNION
(
SELECT last_modified
FROM timestamps
WHERE parent_id = NEW.parent_id
AND resource_name = NEW.resource_name
)
)
SELECT MAX(last_modified) INTO previous_ts
FROM existing_timestamps;
current_ts := clock_timestamp()::TIMESTAMP;
IF previous_ts IS NOT NULL AND previous_ts >= current_ts THEN
current_ts := previous_ts + INTERVAL '1 microsecond';
END IF;
IF NEW.last_modified IS NULL OR
(previous_ts IS NOT NULL AND NEW.last_modified = previous_ts) THEN
NEW.last_modified := current_ts;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;Why this is backward compatible
TIMESTAMP column: Already stores microsecond precision — no schema change needed.- Unique index (
idx_objects_parent_id_resource_name_last_modified): Operates on the TIMESTAMP column directly — benefits from the finer granularity automatically.
as_epoch() / HTTP API: Continues to return millisecond-precision integers for ETag, last_modified in JSON, and _since filtering. No client-visible change. Two records that would have collided now get distinct microsecond timestamps but may share the same millisecond epoch — this is fine since _since polling is idempotent.- No migration needed beyond replacing the trigger function (which is
CREATE OR REPLACE).
Impact
This affects any deployment where multiple processes write objects to the same (parent_id, resource_name) concurrently — e.g., multiple API pods behind a load balancer, concurrent webhook callbacks, or batch imports. The current workaround is application-level retry with transaction.abort(), which works but adds complexity that belongs in the storage layer.
Problem
The
bump_timestamp()trigger computes the nextlast_modifiedvia millisecond-precision epoch arithmetic:This
SELECT MAX(...)is not serialized across concurrent transactions. When two transactions insert/update rows with the same(parent_id, resource_name)within the same millisecond, both read the sameMAX(last_modified), compute the sameprevious + 1, and one fails:The trigger's own comment acknowledges this:
In high-throughput deployments (multiple application pods writing to the same collection concurrently), this is not a rare edge case — it happens routinely under normal load.
Proposal: microsecond-precision bumps (no API change)
The
TIMESTAMPcolumn already stores microsecond precision natively in PostgreSQL. The collision window is artificially wide because the trigger round-trips through millisecond-truncated epoch integers.The fix is to work in
TIMESTAMPdirectly and bump by 1 microsecond instead of 1 millisecond — reducing the collision window by 1000x without changing any client-facing behavior:Why this is backward compatible
TIMESTAMPcolumn: Already stores microsecond precision — no schema change needed.idx_objects_parent_id_resource_name_last_modified): Operates on theTIMESTAMPcolumn directly — benefits from the finer granularity automatically.as_epoch()/ HTTP API: Continues to return millisecond-precision integers forETag,last_modifiedin JSON, and_sincefiltering. No client-visible change. Two records that would have collided now get distinct microsecond timestamps but may share the same millisecond epoch — this is fine since_sincepolling is idempotent.CREATE OR REPLACE).Impact
This affects any deployment where multiple processes write objects to the same
(parent_id, resource_name)concurrently — e.g., multiple API pods behind a load balancer, concurrent webhook callbacks, or batch imports. The current workaround is application-level retry withtransaction.abort(), which works but adds complexity that belongs in the storage layer.