postgresql Postgres SELECT ... FOR UPDATE 在函数中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18879584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres SELECT ... FOR UPDATE in functions
提问by Dan Taylor
I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:
我有两个关于在 Postgres 函数中使用 SELECT ... FOR UPDATE 行级锁定的问题：
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?
我选择哪些列有关系吗？它们与我需要锁定然后更新的数据有任何关系吗？
SELECT * FROM table WHERE x=y FOR UPDATE;对比
SELECT 1 FROM table WHERE x=y FOR UPDATE;如果不将数据保存在某处，我就无法在函数中进行选择，因此我将其保存为虚拟变量。这看起来很hacky；这是做事的正确方法吗？
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;
回答by krokodilko
Does it matter which columns I select?
我选择哪些列有关系吗？
No, it doesn't matter. Even if SELECT 1 FROM table WHERE ... FOR UPDATEis 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-tablenamessyntax can be usefull:
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE
不，没关系。即使SELECT 1 FROM table WHERE ... FOR UPDATE使用了，查询也会锁定所有满足 where 条件的行。
如果查询从联接中检索行，并且我们不想锁定联接中涉及的所有表中的行，而只想锁定特定表中的行，则SELECT ... FOR UPDATE OF list-of-tablenames语法可能很有用：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?
如果不将数据保存在某处，我就无法在函数中进行选择，因此我将其保存为虚拟变量。这看起来很hacky；这是做事的正确方法吗？
In Pl/PgSql use a PERFORMcommand to discard query result:
http://www.postgresql.org/docs/9.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
Instead of:
在 Pl/PgSql 中使用PERFORM命令丢弃查询结果：
http: //www.postgresql.org/docs/9.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
而不是：
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;
