Is there a benefit to using FOR SHARE in this PostgreSql simple select?

Posted on

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

Leave a Reply

Your email address will not be published. Required fields are marked *