A rule like "one comment per user per hour" looks easy. You check for a recent row, and you only insert if there is none. It works in every test you write by hand. Then two requests arrive at the same moment, and the rule quietly breaks.
We set up the exact case on PostgreSQL 17.5, made it fail on purpose, and tried four fixes. Every number below comes from those runs.
The question
Someone on r/PostgreSQL asked about this query. The goal was to let a user post only one comment per hour:
INSERT INTO comments (user_id, content)
SELECT :user_id, 'my comment'
WHERE NOT EXISTS (
SELECT 1 FROM comments
WHERE user_id = :user_id
AND created_at > now() - interval '1 hour'
);
They asked a fair question. If two threads run this at the same time, can two comments slip through? People in the thread said yes. We wanted to show why, and what to do about it.
Why it breaks
PostgreSQL's default mode is called Read Committed. The docs say each query in this mode sees a snapshot of the database taken "the instant the query begins to run." It never sees rows that other transactions have not committed yet.
So here is the timeline:
- Request A runs the query. It finds no recent comment, so it inserts one. A has not committed yet.
- Request B runs the same query. A's new row is not committed, so B cannot see it. B also finds no recent comment, and inserts one.
- Both commit. The user now has two comments in the same hour.
Nothing here is a bug in PostgreSQL. The check and the insert are in one statement, but the check only looks at committed data. There is no unique key for the database to defend, so nothing makes B wait.
The test setup
Our test opens two connections. Request A runs its insert and stays open. Request B runs while A is still open. After half a second, A commits. Then we count rows.
CREATE TABLE users (id int PRIMARY KEY);
CREATE TABLE comments (
id bigserial PRIMARY KEY,
user_id int NOT NULL REFERENCES users(id),
content text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
The core of the Python test, using psycopg2:
a, b = psycopg2.connect(DSN), psycopg2.connect(DSN)
fn(a.cursor(), uid, "from request A") # A is inside its transaction
def second():
start = time.monotonic()
fn(b.cursor(), uid, "from request B") # B runs while A is open
result["waited"] = time.monotonic() - start
b.commit()
t = threading.Thread(target=second); t.start()
time.sleep(0.5)
a.commit(); t.join()
The plain query fails just as the thread said:
naive NOT EXISTS rows=2 B inserted=1 B waited=0.0s
Two rows, and B never waited.
Fix 1: an advisory lock per user
An advisory lock is a lock with a meaning you choose. PostgreSQL does not attach it to any table. pg_advisory_xact_lock holds the lock until the transaction ends, so you never have to free it by hand.
def advisory(cur, uid, txt):
# Lock first, in its own statement.
cur.execute("SELECT pg_advisory_xact_lock(42, %s)", (uid,))
cur.execute(NAIVE, {"uid": uid, "txt": txt})
The 42 is just a number we picked to mean "comment limit." The second number is the user id. So two users never block each other.
The detail that matters: the lock must be its own statement, before the check. In Read Committed, each statement gets a fresh snapshot. B waits on the lock. When A commits, B gets the lock, and its next statement takes a new snapshot that includes A's row.
advisory lock rows=1 B inserted=0 B waited=0.5s
advisory, 2 users rows=2 B inserted=1 B waited=0.0s
B waited for A, then saw the row and inserted nothing. A second user was not slowed down at all.
Fix 2: lock the user's row
If you already have a users table, you can lock the user's row instead. SELECT ... FOR UPDATE makes any other transaction that tries to lock the same row wait.
def row_lock(cur, uid, txt):
cur.execute("SELECT 1 FROM users WHERE id = %s FOR UPDATE", (uid,))
cur.execute(NAIVE, {"uid": uid, "txt": txt})
FOR UPDATE on users rows=1 B inserted=0 B waited=0.5s
Same result. This one is easy to read in code review, because the lock is on a real thing. The cost: anything else that locks that user row will wait too.
Fix 3: let the database enforce the rule
Locks only work if every code path takes them. A new script or a second service might skip the lock. A constraint cannot be skipped.
PostgreSQL has exclusion constraints. They say "no two rows may match on these tests." With the btree_gist extension, we can say "same user AND overlapping one-hour windows" is not allowed.
Our first try failed:
ALTER TABLE comments ADD CONSTRAINT x EXCLUDE USING gist
(user_id WITH =, tstzrange(created_at, created_at + interval '1 hour') WITH &&);
-- ERROR: functions in index expression must be marked IMMUTABLE
Adding an interval to a timestamptz can depend on the time zone setting, so PostgreSQL will not use it in an index. The fix is to store the window as its own column:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE comments ADD COLUMN active_window tstzrange NOT NULL;
ALTER TABLE comments ADD CONSTRAINT one_comment_per_hour
EXCLUDE USING gist (user_id WITH =, active_window WITH &&);
Then insert with ON CONFLICT DO NOTHING, so a blocked comment is skipped and does not throw an error:
INSERT INTO comments (user_id, content, active_window)
VALUES ($1, $2, tstzrange(now(), now() + interval '1 hour'))
ON CONFLICT DO NOTHING;
exclusion constraint rows=1 B inserted=0 B waited=0.5s
exclusion, 2 users rows=2 B inserted=1 B waited=0.0s
One thing to check if you copy an answer from the thread. One reply showed an exclusion constraint with only the time range, and no user_id WITH =. That version stops any two users from posting close together. Always put the user column in.
A tstzrange built this way includes its start and leaves out its end. So a comment exactly one hour later does not overlap and is allowed.
Fix 4: SERIALIZABLE, plus a retry
You can also leave the query alone and run both transactions at the SERIALIZABLE level. PostgreSQL watches for reads and writes that could not happen in any one-at-a-time order, and it cancels one of the transactions.
A committed
B commit failed, SQLSTATE 40001 - could not serialize access due to read/write dependencies among transactions
serializable naive: rows = 1
This is correct, but your code must catch 40001 and run the whole transaction again. The PostgreSQL docs say the same thing: apps at this level must be ready to retry.
A trap we found: advisory lock plus REPEATABLE READ
We also ran Fix 1 with both transactions at REPEATABLE READ. It let two rows through:
B committed
repeatable read + advisory: rows = 2
The docs explain why. At REPEATABLE READ, the snapshot is taken at the start of the first statement in the transaction. Here that first statement is the lock call itself. So B's snapshot is fixed before it waits for the lock, and it never sees A's row. Fixes 1 and 2 depend on Read Committed. If your app or framework changes the isolation level, use Fix 3 or Fix 4.
Which one should you pick?
- Fix 3, the exclusion constraint, if the rule must hold no matter which code writes to the table. It is our first choice.
- Fix 1 or 2 if the rule is complex, such as "five comments per hour," which a simple constraint cannot express. Keep the default Read Committed level.
- Fix 4 if your app already runs at SERIALIZABLE and has a retry loop.
In every case, keep the lock or the transaction short. B waits for as long as A holds on.
Docs we read
- PostgreSQL 17, Transaction Isolation: https://www.postgresql.org/docs/17/transaction-iso.html
- PostgreSQL 17, Explicit Locking, Advisory Locks: https://www.postgresql.org/docs/17/explicit-locking.html
- PostgreSQL 17, Advisory Lock Functions: https://www.postgresql.org/docs/17/functions-admin.html
- PostgreSQL 17, Constraints, Exclusion Constraints: https://www.postgresql.org/docs/17/ddl-constraints.html
- PostgreSQL 17, INSERT and ON CONFLICT: https://www.postgresql.org/docs/17/sql-insert.html
- PostgreSQL 17, btree_gist: https://www.postgresql.org/docs/17/btree-gist.html
- PostgreSQL 17, Range Types: https://www.postgresql.org/docs/17/rangetypes.html
From TalentOp Systems LLC
We are TalentOp Systems LLC, based in Omaha, Nebraska. We started in February 2025. We work in IT staffing, software consulting, project development, and manpower and staffing for clients in banking, insurance, healthcare and retail.
TalentOp Systems is a direct vendor for Kroger, whose offices are in Blue Ash, Ohio.
TalentOp Systems LLC is E-Verified.
Need H-1B sponsorship? TalentOp Systems LLC provides it for hires who meet the role's requirements and eligibility.
OPT and STEM OPT graduates are welcome to apply.
Website: www.talentopsystems.com. Email: info@talentopsystems.com.
Top comments (2)
Running this on a real 17.5 instance with two open connections is more convincing than arguing about isolation levels. For the one-per-hour rule, a transaction-scoped advisory lock keyed on the user id is a nice option because it serializes only that user's inserts without needing a unique key. Did you see any difference in the failure window between the lock approach and a serializable retry loop under heavier concurrency?
The REPEATABLE READ trap with the advisory lock caught my eye. A lock that still lets two rows through is easy to miss in review.