Quick Fix: In PL/pgSQL, use PERFORM command to discard query results instead of saving them to a dummy variable. This is a more efficient and idiomatic approach.
The Problem:
In Postgres, when using SELECT … FOR UPDATE row-level locking in a function, I have two questions:
-
Does the selection of columns affect the data that is locked? Does it have to relate to the data that will be updated?
-
Is it necessary to store the selected data in a dummy variable to avoid an error, or is there a better approach?
The Solutions:
\n
Solution 1: Using SELECT … FOR UPDATE row-level locking in a Postgres function
\n
1. Column Selection:
It doesn’t matter which columns you select in the SELECT clause when using FOR UPDATE. Even if you select only one column (e.g., SELECT 1), the query still locks all rows that meet the WHERE conditions.
However, if your query involves a join and you want to lock rows only from specific tables, you can use the SELECT … FOR UPDATE OF list-of-tablenames syntax.
2. Saving Data to a Dummy Variable:
To avoid saving the query result to a dummy variable, you can use the PERFORM command in Pl/PgSql to discard the result.
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;
This will execute the query and lock the rows without saving the result.
