Postgresql update with a subquery limit 1 that has joins sometimes doesn’t respect the limit

concurrencypostgresqlupdate

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.*

Best Answer

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:

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)

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

Related Solutions

How to Concurrently Update with Limit in MySQL

This depends on table engine?

Yup. MyISAM will lock the table, InnoDB will lock rows

If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page

The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables:

Major gotcha: Rows are locked during updates using indexes. For example, UPDATE tab SET col1=3 WHERE col2=17; will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

and

To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table

Extra curricular reading:

TL;DR: InnoDB index lock is major architectural performance flaw, and that is why you hear that large tables are slower. There’s a big chance that there’re more scalable engines for on-disk writes out there, and all the large InnoDB write/insert benchmarks were severely hit by this.

PostgreSQL – LEFT JOIN with Recurring Dates and Users

You have to do with users the same thing you're doing with days:

WITH
  days AS (
    SELECT generate_series(current_date-7, current_date, '1d')::date AS day
    ),
  eves AS (
    SELECT user_id, created_at::date AS full_day, COUNT(*) as evs
    FROM logged_events
    WHERE logged_events.created_at >= current_date-6
    GROUP BY user_id, full_day
    ),
  users AS (
    SELECT DISTINCT user_id FROM eves
    )
SELECT eves.full_day, days.day, users.user_id, eves.evs
FROM days
CROSS JOIN users
LEFT JOIN eves ON (eves.full_day = days.day AND eves.user_id = users.user_id)
GROUP BY users.user_id, days.day;

Best Answer

Related Solutions

How to Concurrently Update with Limit in MySQL

PostgreSQL – LEFT JOIN with Recurring Dates and Users

Related Question