PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15715
PG Version11.2
OSUbuntu 18.04.2 LTS
Opened2019-03-26 15:08:49+00
Reported byLars Vonk
StatusNew

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

Messages

DateAuthorSubject
2019-03-26 15:08:49+00PG Bug reporting formBUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-26 17:03:17+00Tom LaneRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-26 20:19:22+00Lars VonkRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-26 22:53:56+00Tom LaneRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-27 07:22:39+00Lars VonkRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-27 14:14:21+00Tom LaneRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery
2019-03-27 16:17:51+00Lars VonkRe: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery