Hello!
I have recently encountered weird PostgreSQL behaviour which might be a bug (or
not).
In short, it seems like a SELECT subquery with FOR UPDATE clause might silently
skip rows if they're being updated by top-level UPDATE statement.
The following example simulates a job dependency graph. The
CREATE TYPE pg_temp.status AS ENUM('waiting', 'ready', 'done');
CREATE TABLE pg_temp.jobs(
job_id BIGSERIAL PRIMARY KEY,
status status NOT NULL DEFAULT 'waiting'
);
CREATE TABLE pg_temp.jobs_dependencies(
dependent BIGSERIAL REFERENCES jobs(job_id),
dependency BIGSERIAL REFERENCES jobs(job_id)
);
INSERT INTO jobs(job_id) VALUES
(1),
(20),
(21);
INSERT INTO jobs_dependencies(dependent, dependency) VALUES
(20, 1),
(21, 1);
UPDATE jobs AS dependent
SET status='ready'
WHERE
status='waiting'
AND TRUE = ALL (
SELECT (
dependency.status = 'done'
)
FROM jobs AS dependency, jobs_dependencies
WHERE jobs_dependencies.dependency = dependency.job_id AND
jobs_dependencies.dependent = dependent.job_id
FOR UPDATE OF dependency
);
SELECT * FROM jobs;
The result is wrong:
job_id | status
--------+--------
1 | ready
20 | ready
21 | ready
(3 rows)
The expected result, which is returned if you comment out "FOR UPDATE" clause,
is this:
job_id | status
--------+---------
20 | waiting
21 | waiting
1 | ready
(3 rows)
I've asked some LLMs about this. I'll spare you of the details, but they came
to the conclusion this is caused by TM_SelfModified handling in ExecLockRows.
Is this a bug that TM_SelfModified causes a SELECT ... FOR UPDATE subquery to
skip rows silently, even though it was supposed to affect UPDATE itself only?
