From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | lars(dot)vonk(at)gmail(dot)com |
Subject: | BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery |
Date: | 2019-03-26 15:08:49 |
Message-ID: | 15715-1eccf9c7dffc4093@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15715
Logged by: Lars Vonk
Email address: lars(dot)vonk(at)gmail(dot)com
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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2019-03-26 15:18:07 | BUG #15716: pgadmin 4.3: row_to_json error |
Previous Message | Joe Conway | 2019-03-26 14:55:36 | Re: BUG #15714: plctl extension issue |