batch postgresql updates with a CTE

batch-update.sql

-- based on https://news.ycombinator.com/item?id=9018756
=# select *, 'foo'::text into test from generate_series(1,5) as id;
SELECT 5
Time: 77.975 ms
=# select * from test;
┌────┬──────┐
│ id │ text │
├────┼──────┤
│  1 │ foo  │
│  2 │ foo  │
│  3 │ foo  │
│  4 │ foo  │
│  5 │ foo  │
└────┴──────┘
(5 rows)

Time: 1.100 ms
=# with candidate_rows as (
(#   select id
(#   from test
(#   where text <> 'updated'
(#   limit 2
(#   for update nowait
(# ), update_rows as (
(#   update test
(#   set text = 'updated'
(#   from candidate_rows
(#   where candidate_rows.id = test.id
(#   returning test.id
(# )
-# select count(1) from update_rows;
┌───────┐
│ count │
├───────┤
│     2 │
└───────┘
(1 row)

Time: 19.918 ms
=# select * from test;
┌────┬─────────┐
│ id │  text   │
├────┼─────────┤
│  3 │ foo     │
│  4 │ foo     │
│  5 │ foo     │
│  1 │ updated │
│  2 │ updated │
└────┴─────────┘
(5 rows)

Time: 1.027 ms
=# with candidate_rows as (
(#   select id
(#   from test
(#   where text <> 'updated'
(#   limit 2
(#   for update nowait
(# ), update_rows as (
(#   update test
(#   set text = 'updated'
(#   from candidate_rows
(#   where candidate_rows.id = test.id
(#   returning test.id
(# )
-# select count(1) from update_rows;
┌───────┐
│ count │
├───────┤
│     2 │
└───────┘
(1 row)

Time: 19.674 ms
=# select * from test;
┌────┬─────────┐
│ id │  text   │
├────┼─────────┤
│  5 │ foo     │
│  1 │ updated │
│  2 │ updated │
│  3 │ updated │
│  4 │ updated │
└────┴─────────┘
(5 rows)

Time: 0.910 ms
=# with candidate_rows as (
  select id
  from test
  where text <> 'updated'
  limit 2
  for update nowait
), update_rows as (
  update test
  set text = 'updated'
  from candidate_rows
  where candidate_rows.id = test.id
  returning test.id
)
select count(1) from update_rows;
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
(1 row)

Time: 19.164 ms
=# select * from test;
┌────┬─────────┐
│ id │  text   │
├────┼─────────┤
│  1 │ updated │
│  2 │ updated │
│  3 │ updated │
│  4 │ updated │
│  5 │ updated │
└────┴─────────┘
(5 rows)

Time: 1.062 ms
=# with candidate_rows as (
  select id
  from test
  where text <> 'updated'
  limit 2
  for update nowait
), update_rows as (
  update test
  set text = 'updated'
  from candidate_rows
  where candidate_rows.id = test.id
  returning test.id
)
select count(1) from update_rows;
┌───────┐
│ count │
├───────┤
│     0 │
└───────┘
(1 row)

Time: 1.956 ms