Question :
In the following PgSql stored function:
CREATE OR REPLACE FUNCTION get_offer_from_id(
offer_id bigint)
RETURNS json AS $$
DECLARE
res "entity_data_result";
r "get_offer_data";
BEGIN
FOR r IN SELECT * FROM "get_offer_data"
WHERE "id" = offer_id
LIMIT 1
FOR SHARE
LOOP
res.found := true;
res.data := row_to_json(r);
END LOOP;
RETURN row_to_json(res);
END; $$
LANGUAGE plpgsql;
is there a real benefit to using FOR SHARE or can one remove it from the query?
Answer :
That depends on what else happens in the transaction.
With the for share
there, nobody can update the select
ed get_offer_data
row until the transaction commits or rolls back.
Without the context, it’s really impossible to say what the original intended purpose of adding the lock there was.