Postgresql – benefit to using FOR SHARE in this PostgreSql simple select

lockingpostgresqlstored-procedures

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?

Best Answer

That depends on what else happens in the transaction.

With the for share there, nobody can update the selected 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.