I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:
Does it matter which columns I select? Do they have any relation to what data I need to lock and then update?
SELECT * FROM table WHERE x=y FOR UPDATE;vs
SELECT 1 FROM table WHERE x=y FOR UPDATE;I can't do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?
Here is my function:
CREATE OR REPLACE FUNCTION update_message(v_1 INTEGER, v_timestamp INTEGER, v_version INTEGER)
RETURNS void AS $$
DECLARE
v_timestamp_conv TIMESTAMP;
dummy INTEGER;
BEGIN
SELECT timestamp 'epoch' + v_timestamp * interval '1 second' INTO v_timestamp_conv;
SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
UPDATE my_table SET (timestamp) = (v_timestamp_conv) WHERE userid=v_1 AND version < v_version;
END;
$$ LANGUAGE plpgsql;
select .. for updatedoesn't serve any purpose. TheUPDATEwill lock the row just as well. If you really don't do any work between theSELECTand theUPDATEyou can leave out theSELECTcompletely and make your function faster.