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() 
   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') 
  • 2
    The for update is not needed as the following update will 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:10
  • 2
    Could you gives some minimum structure and data samples? I've invented some data to try to reproduce your problem, with no success. I have assumed that users.id is a primary key. This allows to eliminate the second ("users".touched_at ... ), because this condition has already been checked in your SELECT. – joanolo Dec 21 '16 at 22:05
  • Is users.id a primary key? Or unique constraint? – jjanes Dec 25 '16 at 19:36
  • i can't reproduce this on my mac or amazon rds production with the exact same postgresql version but it happens ~10% of the time on travis: CREATE 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

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

  1. an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now() WHERE users.id = (SELECT id FROM users LIMIT 1)


  1. if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now() WHERE users.id = cte.id
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.