Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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;
share|improve this question
1  
If that is really your complete function, then the select .. for update doesn't serve any purpose. The UPDATE will lock the row just as well. If you really don't do any work between the SELECT and the UPDATEyou can leave out the SELECT completely and make your function faster. –  a_horse_with_no_name Sep 18 '13 at 21:22
add comment

1 Answer

up vote 2 down vote accepted

Does it matter which columns I select?

No, it doesn't matter. Even if SELECT 1 FROM table WHERE ... FOR UPDATE is used, the query locks all rows that meet where conditions.

If the query retrieves rows from a join, and we don't want to lock rows from all tables involved in the join, but only rows from specific tables, a SELECT ... FOR UPDATE OF list-of-tablenames syntax can be usefull:
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE


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?

In Pl/PgSql use a PERFORM command to discard query result:
http://www.postgresql.org/docs/9.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

Instead of: 

SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;

use:

PERFORM 1 FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.