BUG #8464: Update with subquery with limit occasionally updates too many rows - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8464
Logged by:          Joel Turkel
Email address:      jturkel@salsify.com
PostgreSQL version: 9.2.4
Operating system:   Ubunutu 12.04 (64-bit)/Kernel version 3.2
Description:

An update statement in my application that uses a limit subquery to select a
single primary key value for a row to update occasionally updates multiple
rows. We've only noticed this problem in our test automation runs and it
only occurs roughly every 25-50 test runs with no discernible pattern for
when it occurs. Attempts to isolate the problem into a consistently
reproducible test case have been unsuccessful. The schema for the relevant
tables in question are:


CREATE TABLE delayed_jobs
(
  id serial NOT NULL,
  priority integer DEFAULT 0,
  attempts integer DEFAULT 0,
  handler text,
  last_error text,
  run_at timestamp without time zone,
  locked_at timestamp without time zone,
  failed_at timestamp without time zone,
  locked_by character varying(255),
  queue character varying(255),
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  job_group_id integer,
  blocked boolean NOT NULL DEFAULT false,
  CONSTRAINT delayed_jobs_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);


CREATE INDEX delayed_jobs_priority
  ON delayed_jobs
  USING btree
  (priority, run_at)
  WHERE failed_at IS NULL AND blocked = false;


CREATE INDEX index_delayed_jobs_on_job_group_id
  ON delayed_jobs
  USING btree
  (job_group_id);


CREATE TABLE delayed_job_groups
(
  id serial NOT NULL,
  on_completion_job text,
  on_completion_job_options text,
  queueing_complete boolean NOT NULL DEFAULT false,
  blocked boolean NOT NULL DEFAULT false,
  CONSTRAINT delayed_job_groups_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);




Here's a log of the SQL statements run during a recent test failure (there
were hundreds of tests run before this so including all statements run since
startup would be a bit unwieldy):


SAVEPOINT active_record_1
INSERT INTO "delayed_job_groups" ("blocked", "on_completion_job",
"on_completion_job_options", "queueing_complete") VALUES ($1, $2, $3, $4)
RETURNING "id" [["blocked", false], ["on_completion_job", "---
!ruby/object:CompletionJob {}\n"], ["on_completion_job_options", nil],
["queueing_complete", false]]
RELEASE SAVEPOINT active_record_1




SAVEPOINT active_record_1
INSERT INTO "delayed_jobs" ("attempts", "blocked", "created_at",
"failed_at", "handler", "job_group_id", "last_error", "locked_at",
"locked_by", "priority", "queue", "run_at", "updated_at") VALUES ($1, $2,
$3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id"
[["attempts", 0], ["blocked", false], ["created_at", Fri, 20 Sep 2013
00:08:47 UTC +00:00], ["failed_at", nil], ["handler", "---
!ruby/object:FailingJob {}\n"], ["job_group_id", 920], ["last_error", nil],
["locked_at", nil], ["locked_by", nil], ["priority", 0], ["queue", nil],
["run_at", Fri, 20 Sep 2013 00:08:47 UTC +00:00], ["updated_at", Fri, 20 Sep
2013 00:08:47 UTC +00:00]]
-- Inserts row with 254
RELEASE SAVEPOINT active_record_1


SAVEPOINT active_record_1
INSERT INTO "delayed_jobs" ("attempts", "blocked", "created_at",
"failed_at", "handler", "job_group_id", "last_error", "locked_at",
"locked_by", "priority", "queue", "run_at", "updated_at") VALUES ($1, $2,
$3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id" [0m
[["attempts", 0], ["blocked", false], ["created_at", Fri, 20 Sep 2013
00:08:47 UTC +00:00], ["failed_at", nil], ["handler", "---
!ruby/object:NoOpJob {}\n"], ["job_group_id", 920], ["last_error", nil],
["locked_at", nil], ["locked_by", nil], ["priority", 0], ["queue", nil],
["run_at", Fri, 20 Sep 2013 00:08:47 UTC +00:00], ["updated_at", Fri, 20 Sep
2013 00:08:47 UTC +00:00]]
-- Inserts row with id 254
RELEASE SAVEPOINT active_record_1


SAVEPOINT active_record_1
SELECT "delayed_job_groups".* FROM "delayed_job_groups" WHERE
"delayed_job_groups"."id" = $1 LIMIT 1 FOR UPDATE  [["id", 920]]
UPDATE "delayed_job_groups" SET "queueing_complete" = 't' WHERE
"delayed_job_groups"."id" = 920
SELECT 1 AS one FROM "delayed_jobs" WHERE "delayed_jobs"."job_group_id" IN
(920) AND "delayed_jobs"."failed_at" IS NULL LIMIT 1
RELEASE SAVEPOINT active_record_1


SELECT COUNT(*) FROM "delayed_jobs" WHERE "delayed_jobs"."failed_at" IS
NULL
SELECT COUNT(*) FROM "delayed_job_groups"


UPDATE "delayed_jobs" SET locked_at = '2013-09-20 00:08:47.540592',
locked_by = 'host:box239 pid:11983' WHERE id IN (SELECT id FROM
"delayed_jobs" WHERE "delayed_jobs"."blocked" = 'f' AND ((run_at <=
'2013-09-20 00:08:47.540120' AND (locked_at IS NULL OR locked_at <
'2013-09-19 20:08:47.540135') OR locked_by = 'host:box239 pid:11983') AND
failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE)
RETURNING *
-- Updates both rows 253 and 254 but the limit should result in only one row
being updated


There is no concurrent access to the database during the test in question. I
realize I haven't given you guys a ton of information to work off but any
help in tracking this down would be greatly appreciated.


Thanks,
Joel