postgresqlsql-updatelockingdatabase-concurrencypostgresql-9.6

Select for update query returns cardinality violation


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.


Solution

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