Postgres SELECT ... FOR UPDATE in functions

PostgresqlPlpgsqlPostgresql 9.1Select for-Update

Postgresql Problem Overview


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;

Postgresql Solutions


Solution 1 - Postgresql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDan TaylorView Question on Stackoverflow
Solution 1 - PostgresqlkrokodilkoView Answer on Stackoverflow