Thread: UPDATE grabs multiple rows when it seems like it should only grab one
Hi,
I'm trying to write a job queue that grabs one job at a time from the
queue. I expect that the following query should update a maximum of one row
in the table:
UPDATE queued_jobs
SET status='in-progress',
updated_at=now()
FROM (
SELECT id AS inner_id
FROM queued_jobs
WHERE status='queued'
AND name = $1
AND run_after <= now()
LIMIT 1
FOR UPDATE
) find_job
WHERE queued_jobs.id = find_job.inner_id
AND status='queued'
RETURNING id,
name,
attempts,
run_after,
expires_at,
status,
data,
created_at,
updated_at
However, I observe that multiple rows are updated. I am certain that it's a
single query updating multiple rows, because I observed this in the EXPLAIN
output, and also configured my application to crash if multiple rows were
returned, and could reliably trigger an application crash.
Here is the EXPLAIN output from a query when two rows were returned:
Update on queued_jobs (cost=0.75..16.83 rows=1 width=120) (actual
time=3.011..67.515 rows=2 loops=1)
-> Nested Loop (cost=0.75..16.83 rows=1 width=120) (actual
time=2.974..67.458 rows=2 loops=1)
Join Filter: (queued_jobs.id = find_job.inner_id)
Rows Removed by Join Filter: 475
-> Index Scan using queued_jobs_pkey on queued_jobs
(cost=0.38..8.39 rows=1 width=80) (actual time=0.011..1.326 rows=477
loops=1)
Filter: (status = 'queued'::job_status)
Rows Removed by Filter: 1
-> Subquery Scan on find_job (cost=0.38..8.42 rows=1 width=56)
(actual time=0.137..0.138 rows=1 loops=477)
-> Limit (cost=0.38..8.41 rows=1 width=22) (actual
time=0.136..0.136 rows=1 loops=477)
-> LockRows (cost=0.38..8.41 rows=1 width=22) (actual
time=0.136..0.136 rows=1 loops=477)
-> Index Scan using find_queued_job on
queued_jobs queued_jobs_1 (cost=0.38..8.40 rows=1 width=22) (actual
time=0.134..0.135 rows=2 loops=477)
Index Cond: ((name = $1) AND (run_after <=
now()))
Filter: (status = 'queued'::job_status)
Here's the EXPLAIN output from a "normal" query that only gets one row:
Update on queued_jobs (cost=0.41..8.53 rows=1 width=120) (actual
time=3.730..3.733 rows=1 loops=1)
-> Nested Loop (cost=0.41..8.53 rows=1 width=120) (actual
time=3.688..3.690 rows=1 loops=1)
-> Subquery Scan on find_job (cost=0.00..0.08 rows=1 width=56)
(actual time=3.672..3.673 rows=1 loops=1)
-> Limit (cost=0.00..0.07 rows=1 width=22) (actual
time=3.662..3.662 rows=1 loops=1)
-> LockRows (cost=0.00..2935.47 rows=42743 width=22)
(actual time=3.661..3.661 rows=1 loops=1)
-> Seq Scan on queued_jobs queued_jobs_1
(cost=0.00..2508.04 rows=42743 width=22) (actual time=1.362..1.375 rows=5
loops=1)
Filter: ((status = 'queued'::job_status)
AND (name = $1) AND (run_after <= now()))
Rows Removed by Filter: 1
-> Index Scan using queued_jobs_pkey on queued_jobs
(cost=0.41..8.44 rows=1 width=80) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (id = find_job.inner_id)
Filter: (status = 'queued'::job_status)
For convenience, I've posted these (and a table schema) here:
https://gist.github.com/kevinburkeshyp/ba5fdac337b3793628261de5fb26d6a3
I'm running Postgres 9.4.6 on a Mac 10.10.5, installed via Homebrew, with
the read committed isolation level. The client is a Go application with 8
concurrent database connections, using prepared statements with the
github.com/lib/pq client.
I also observe that this only seems to occur when I am simultaneously
inserting rows into the table. The inserts occur from a different Go
application, running on a separate process with a separate connection pool.
Any ideas? Maybe I don't understand SQL properly? I can reliably reproduce
this, please ping me if you'd like more information!
--
kevin
(I should also note - the `id` column is a uuid primary key, I double checked the table afterwards, there's no chance that two ID's were inserted with the same value.) On Fri, Apr 22, 2016 at 3:56 PM, Kevin Burke <burke@shyp.com> wrote: > Hi, > I'm trying to write a job queue that grabs one job at a time from the > queue. I expect that the following query should update a maximum of one row > in the table: > > UPDATE queued_jobs > SET status='in-progress', > updated_at=now() > FROM ( > SELECT id AS inner_id > FROM queued_jobs > WHERE status='queued' > AND name = $1 > AND run_after <= now() > LIMIT 1 > FOR UPDATE > ) find_job > WHERE queued_jobs.id = find_job.inner_id > AND status='queued' > RETURNING id, > name, > attempts, > run_after, > expires_at, > status, > data, > created_at, > updated_at > > However, I observe that multiple rows are updated. I am certain that it's > a single query updating multiple rows, because I observed this in the > EXPLAIN output, and also configured my application to crash if multiple > rows were returned, and could reliably trigger an application crash. > > Here is the EXPLAIN output from a query when two rows were returned: > > Update on queued_jobs (cost=0.75..16.83 rows=1 width=120) (actual > time=3.011..67.515 rows=2 loops=1) > -> Nested Loop (cost=0.75..16.83 rows=1 width=120) (actual > time=2.974..67.458 rows=2 loops=1) > Join Filter: (queued_jobs.id = find_job.inner_id) > Rows Removed by Join Filter: 475 > -> Index Scan using queued_jobs_pkey on queued_jobs > (cost=0.38..8.39 rows=1 width=80) (actual time=0.011..1.326 rows=477 > loops=1) > Filter: (status = 'queued'::job_status) > Rows Removed by Filter: 1 > -> Subquery Scan on find_job (cost=0.38..8.42 rows=1 width=56) > (actual time=0.137..0.138 rows=1 loops=477) > -> Limit (cost=0.38..8.41 rows=1 width=22) (actual > time=0.136..0.136 rows=1 loops=477) > -> LockRows (cost=0.38..8.41 rows=1 width=22) > (actual time=0.136..0.136 rows=1 loops=477) > -> Index Scan using find_queued_job on > queued_jobs queued_jobs_1 (cost=0.38..8.40 rows=1 width=22) (actual > time=0.134..0.135 rows=2 loops=477) > Index Cond: ((name = $1) AND (run_after <= > now())) > Filter: (status = 'queued'::job_status) > > Here's the EXPLAIN output from a "normal" query that only gets one row: > > Update on queued_jobs (cost=0.41..8.53 rows=1 width=120) (actual > time=3.730..3.733 rows=1 loops=1) > -> Nested Loop (cost=0.41..8.53 rows=1 width=120) (actual > time=3.688..3.690 rows=1 loops=1) > -> Subquery Scan on find_job (cost=0.00..0.08 rows=1 width=56) > (actual time=3.672..3.673 rows=1 loops=1) > -> Limit (cost=0.00..0.07 rows=1 width=22) (actual > time=3.662..3.662 rows=1 loops=1) > -> LockRows (cost=0.00..2935.47 rows=42743 width=22) > (actual time=3.661..3.661 rows=1 loops=1) > -> Seq Scan on queued_jobs queued_jobs_1 > (cost=0.00..2508.04 rows=42743 width=22) (actual time=1.362..1.375 rows=5 > loops=1) > Filter: ((status = 'queued'::job_status) > AND (name = $1) AND (run_after <= now())) > Rows Removed by Filter: 1 > -> Index Scan using queued_jobs_pkey on queued_jobs > (cost=0.41..8.44 rows=1 width=80) (actual time=0.012..0.013 rows=1 loops=1) > Index Cond: (id = find_job.inner_id) > Filter: (status = 'queued'::job_status) > > For convenience, I've posted these (and a table schema) here: > https://gist.github.com/kevinburkeshyp/ba5fdac337b3793628261de5fb26d6a3 > > I'm running Postgres 9.4.6 on a Mac 10.10.5, installed via Homebrew, with > the read committed isolation level. The client is a Go application with 8 > concurrent database connections, using prepared statements with the > github.com/lib/pq client. > > I also observe that this only seems to occur when I am simultaneously > inserting rows into the table. The inserts occur from a different Go > application, running on a separate process with a separate connection pool. > > Any ideas? Maybe I don't understand SQL properly? I can reliably reproduce > this, please ping me if you'd like more information! > > -- > kevin > -- kevin
Re: UPDATE grabs multiple rows when it seems like it should only grab one
From
"David G. Johnston"
Date:
On Fri, Apr 22, 2016 at 3:56 PM, Kevin Burke <burke@shyp.com> wrote: > Hi, > I'm trying to write a job queue that grabs one job at a time from the > queue. I expect that the following query should update a maximum of one r= ow > in the table: > > UPDATE queued_jobs > SET status=3D'in-progress', > updated_at=3Dnow() > FROM ( > SELECT id AS inner_id > FROM queued_jobs > WHERE status=3D'queued' > AND name =3D $1 > AND run_after <=3D now() > LIMIT 1 > FOR UPDATE > ) find_job > WHERE queued_jobs.id =3D find_job.inner_id > AND status=3D'queued' > RETURNING id, > name, > attempts, > run_after, > expires_at, > status, > data, > created_at, > updated_at > > =E2=80=8BCan you alter this to do: RETURNING ctid, id, etc.... And then show the records that are returned? No promises it will work.... David J.=E2=80=8B
Kevin Burke <burke@shyp.com> writes:
> I'm trying to write a job queue that grabs one job at a time from the
> queue. I expect that the following query should update a maximum of one row
> in the table:
> UPDATE queued_jobs
> SET status='in-progress',
> updated_at=now()
> FROM (
> SELECT id AS inner_id
> FROM queued_jobs
> WHERE status='queued'
> AND name = $1
> AND run_after <= now()
> LIMIT 1
> FOR UPDATE
> ) find_job
> WHERE queued_jobs.id = find_job.inner_id
> AND status='queued'
I think you're assuming that the sub-query will always select the same
row, but it doesn't have to. LIMIT without an ORDER BY is ill-defined.
Another problem is that once the outer UPDATE has changed the status
of whichever row the sub-query selects initially, that row isn't
a candidate to be returned by later subquery runs, so it'd certainly
move on to another row. (I'm assuming here that FOR UPDATE allows
the sub-query to see the effects of the outer update immediately,
which might be wrong; I lack the time to go check right now.)
You might have better luck by putting the sub-query in a CTE, where
it will be executed at most once.
regards, tom lane
Re: UPDATE grabs multiple rows when it seems like it should only grab one
From
"David G. Johnston"
Date:
On Fri, Apr 22, 2016 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kevin Burke <burke@shyp.com> writes: > > I'm trying to write a job queue that grabs one job at a time from the > > queue. I expect that the following query should update a maximum of one > row > > in the table: > > > UPDATE queued_jobs > > SET status=3D'in-progress', > > updated_at=3Dnow() > > FROM ( > > SELECT id AS inner_id > > FROM queued_jobs > > WHERE status=3D'queued' > > AND name =3D $1 > > AND run_after <=3D now() > > LIMIT 1 > > FOR UPDATE > > ) find_job > > WHERE queued_jobs.id =3D find_job.inner_id > > AND status=3D'queued' > > I think you're assuming that the sub-query will always select the same > row, but it doesn't have to. =E2=80=8BActually, I assumed that the uncorrelated subquery would only be r= un a single time...=E2=80=8B =E2=80=8BThe documentation on update, to me, seems to support this interpre= tation. """ When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s) """=E2=80=8B =E2=80=8BThe understanding of JOIN that I hold is to take two complete rela= tions and combine them on some predicate. The from relation here, when complete, only has one row and given it is effectively a self-join on the PK the result of the join is guaranteed to be a single row. I do not follow how the sub-select is allowed to be evaluated multiple times.=E2=80=8B LIMIT without an ORDER BY is ill-defined. > Another problem is that once the outer UPDATE has changed the status > of whichever row the sub-query selects initially, that row isn't > a candidate to be returned by later subquery runs, so it'd certainly > move on to another row. (I'm assuming here that FOR UPDATE allows > the sub-query to see the effects of the outer update immediately, > which might be wrong; I lack the time to go check right now.) > > You might have better luck by putting the sub-query in a CTE, where > it will be executed at most once. > =E2=80=8BSince I presume that is the desired semantics here=E2=80=8B =E2=80=8Bthat seems like this is the best proper solution. Though now I'm = curious what people did before CTEs were available...this problem isn't new. David J. =E2=80=8B
Thanks both of you for your help; I can see why the first query was incorrect. On Fri, Apr 22, 2016 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > LIMIT without an ORDER BY is ill-defined. I moved to a CTE, but isn't LIMIT without an ORDER BY OK for this use case? A series of dequeuers are more or less trying to find any queued job; it doesn't really matter which one they get. I may be getting the indexes wrong, but as far as I can tell it's about twice as expensive to fetch a row with an ORDER BY as without it. (There's probably a better design here, where I do batch fetches and then distribute the work; let's ignore that for the moment). -- kevin
Where can I read more about the effects of that? I see this: When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY. Is there anything else I can read? On Fri, Apr 22, 2016 at 8:04 PM, Kevin Burke <burke@shyp.com> wrote: > Thanks both of you for your help; I can see why the first query was > incorrect. > > On Fri, Apr 22, 2016 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> LIMIT without an ORDER BY is ill-defined. > > > I moved to a CTE, but isn't LIMIT without an ORDER BY OK for this use > case? A series of dequeuers are more or less trying to find any queued job; > it doesn't really matter which one they get. I may be getting the indexes > wrong, but as far as I can tell it's about twice as expensive to fetch a > row with an ORDER BY as without it. > > (There's probably a better design here, where I do batch fetches and then > distribute the work; let's ignore that for the moment). > > -- > kevin > -- kevin
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The understanding of JOIN that I hold is to take two complete relations
> and combine them on some predicate. The from relation here, when complete,
> only has one row and given it is effectively a self-join on the PK the
> result of the join is guaranteed to be a single row. I do not follow how
> the sub-select is allowed to be evaluated multiple times.
You're not perceiving it correctly. In general, a join is defined as
"evaluate the join condition for each row pair in the cross-product
of the two relations, and take all the row pairs for which the join
condition succeeds". The conditions in the query are effectively all
join conditions from a semantic standpoint; the fact that some are
syntactically written as a sub-select doesn't change that. Kevin's query
will work as he expects only if the join condition is stable --- but here
it's volatile in two different ways, one being the underspecified LIMIT,
and the other one being the fact that FOR UPDATE allows the status
condition to see the effects of the outer UPDATE.
What's needed to make this work is to have a hard optimization boundary
between the sub-select and the outer query, so that these conditions don't
act like join conditions. Merely writing sub-select syntax doesn't
provide that (and users wouldn't like it if it did). I think that a CTE
should be a sufficient boundary, although even with a CTE, the FOR UPDATE
is a bit risky: if the outer update could change a row before the CTE's
scan had reached it, you'd still get funny results. Should be OK in this
usage though, because you'd never update a row that the CTE hadn't already
examined.
regards, tom lane