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