Collected from the PG bugs email list.
|OS||Ubuntu 18.04.2 LTS|
|Reported by||Lars Vonk|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15715 Logged by: Lars Vonk Email address: (redacted) PostgreSQL version: 11.2 Operating system: Ubuntu 18.04.2 LTS Description: Hi, We are using a ruby library called delayed-job (https://github.com/collectiveidea/delayed_job) that generates the following sql LOG: duration: 82797.554 ms execute <unnamed>: UPDATE "delayed_jobs" SET locked_at = '2019-03-26 13:25:20.808244', locked_by = 'host:myhost pid:9958' WHERE id IN (SELECT "delayed_jobs"."id" FROM "delayed_jobs" WHERE ((run_at <= '2019-03-26 13:25:20.807815' AND (locked_at IS NULL OR locked_at < '2019-03-26 09:25:20.807828') OR locked_by = 'host:myhost pid:9958') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) RETURNING * We noticed that all the "delayed_jobs" were getting locked instead of just 1. There is already a bug report (and a recent "fix") for this in delayed_job: https://github.com/collectiveidea/delayed_job_active_record/issues/143 We also found some related stackoverflow content which sort of hints that this is a bug: - https://dba.stackexchange.com/questions/69471/postgres-update-limit-1 - https://github.com/feikesteenbergen/demos/blob/master/bugs/update_limit_bug.txt So is this a postgres bug? Or is "limit 1" not garantueed in subqueries? Kind regards, Lars
|2019-03-26 15:08:49+00||PG Bug reporting form||BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery|
|2019-03-26 17:03:17+00||Tom Lane||Re: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery|
|2019-03-26 20:19:22+00||Lars Vonk||Re: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery|
|2019-03-26 22:53:56+00||Tom Lane||Re: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery|
|2019-03-27 07:22:39+00||Lars Vonk|
|2019-03-27 14:14:21+00||Tom Lane|
|2019-03-27 16:17:51+00||Lars Vonk|