Select for update query returns cardinality violation

Question

We are running this query in Postgres 9.6.10 in a Google managed cloud DB:

WITH update AS
  (UPDATE cart SET loyalty = loyalty || jsonb_insert('{}', '{coupon}', loyalty#>'{scan_coupon}' || $1) WHERE id = 
  (SELECT id FROM cart WHERE id = $2 AND status = $3 and item_version = $4 FOR UPDATE) returning *)
SELECT * FROM updated

cart is a table that has id as the primary key. loyalty is a jsonb column and item_version is a function that increments on some operations but several updates are expected to take place before item_version is updated. status is an enumerated type.

Under highly concurrent updates we rarely get the following error:

Cardinality_violation, file: "nodeSubplan.c", line: "1127", message: "more than one row returned by a subquery used as an expression", pg_code: "21000", routine: "ExecSetParamPlan", severity: "ERROR", unknown: "ERROR"

I've confirmed that $2 is actually an integer and points to an existing row and since id is the primary key I don't see how this could ever return more than one row.

Is it the SELECT FOR UPDATE that is the offending query? How could that query return more than one row if id is the primary key.

What is your Postgres version? There have been some bugs which could lead to corrupted unique indexes allowing duplicate values. — , Jun 03 '19 at 23:50
The point release would be relevant to see the patch level: 9.6.??? — Erwin Brandstetter, Jun 04 '19 at 16:36
[9.6.13 is current as of writing.](https://www.postgresql.org/support/versioning/) Google is a bit behind. Wouldn't know of any bugs affecting UNIQUE indexes in that version, though. Maybe @a_horse knows more? — Erwin Brandstetter, Jun 04 '19 at 20:47
@ErwinBrandstetter: 9.6.10 or .13 should be fine - it was just a wild guess. — , Jun 05 '19 at 00:55

Erwin Brandstetter · Accepted Answer · 2019-06-04T16:43:33.587

3

Looks like you can simplify to:

UPDATE cart
SET    loyalty = loyalty || jsonb_build_object('coupon', loyalty->'scan_coupon') || $1
WHERE  id  =  $2
AND    status = $3
AND    item_version = $4
RETURNING *;

The UPDATE locks the row in just the same way as your nested SELECT ... FOR UPDATE would.

And jsonb_build_object() is simpler, doing the same as your jsonb_insert(). Or maybe simpler, yet:

SET    loyalty = jsonb_insert(loyalty, '{coupon}', loyalty->'scan_coupon') || $1

I am just as surprised as you are, that the subquery (you don't need) would return more than one row somehow. Seems impossible. Are you sure that's the source of the error message?

edited Jun 04 '19 at 16:43

answered Jun 03 '19 at 23:40

Erwin Brandstetter

605,456
145
1,078
1,228

I'll try this and see if I can reproduce it. It's a little difficult to reproduce it, but I'll definitely try it out. – CallMeNorm Jun 04 '19 at 16:18
We've wrapped it in a `WITH` because it wasn't returning the most up-to-date `loyalty` column when doing highly concurrent writes to that column. I'm not sure why we're using a subquery instead of where directly. – CallMeNorm Jun 04 '19 at 20:31
The CTE wrapper (`WITH ...`) should not have any bearing whatsoever on the returned column, which is the same either way. That's also very odd. You might try my simplified version and see if the problems persist. – Erwin Brandstetter Jun 04 '19 at 20:43
I've used you're version and cannot reproduce the same issue. Which solves my real problem. Thanks. – CallMeNorm Jun 04 '19 at 21:25

Select for update query returns cardinality violation

1 Answers1