As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join. However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records. Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?
UPDATE "users" SET touched_at = now()
FROM (
SELECT "some_table".*
FROM "some_table"
INNER JOIN "users" ON "users"."id" = "some_table"."user_id"
WHERE "some_table"."handled_at" IS NULL
AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
ORDER BY "some_table"."created_at" ASC
LIMIT 1
FOR UPDATE OF "users"
) dt
WHERE "users".id = dt.user_id
AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
RETURNING dt.*
for updateis not needed as the followingupdatewill lock the row anyway. And I think the derived table is wrong. In Postgres you should not repeat the target table in the from clause. As far as I can tell you can replace that with something like:update users set .. from some_table where some_table.handled_at is null and users.id = some_table.user_id (and users.touched_at is null ...) ...– a_horse_with_no_name Dec 21 '16 at 20:10users.idis a primary key. This allows to eliminate the second("users".touched_at ... ), because this condition has already been checked in yourSELECT. – joanolo Dec 21 '16 at 22:05CREATE TABLE some_table ( id uuid DEFAULT uuid_generate_v1() NOT NULL, user_id uuid NOT NULL, handled_at timestamp without time zone, ); ALTER TABLE ONLY some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY (id); CREATE TABLE users ( id uuid DEFAULT uuid_generate_v1() NOT NULL, touched_at timestamp without time zone ); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);– Eric Jensen Jan 10 '17 at 20:19