时间戳

首页 > 解决方案 > SELECT 是否比嵌套 INSERT 的函数“更快”？

sql - SELECT 是否比嵌套 INSERT 的函数“更快”？

问题描述

我正在使用一个函数，如果它不存在，则将行插入到表中，然后返回该行的 id。

每当我将函数放入SELECT语句中时，表中尚不存在值，例如：

SELECT * FROM table WHERE id = function(123);

...它返回一个空行。但是，使用相同的值再次运行它将返回包含我想要查看的值的行。

为什么会这样？INSERT奔跑落后于SELECT速度吗？或者 PostgreSQL 是否在表不存在时对其进行缓存，并在下次运行时显示结果？

这是一个现成的示例，说明此问题是如何发生的：

CREATE TABLE IF NOT EXISTS test_table(
id INTEGER,
tvalue boolean
);

CREATE OR REPLACE FUNCTION test_function(user_id INTEGER)
    RETURNS integer
    LANGUAGE 'plpgsql'
AS $$
DECLARE
    __user_id INTEGER;

BEGIN
    EXECUTE format('SELECT * FROM test_table WHERE id = $1')
    USING user_id
    INTO __user_id;

    IF __user_id IS NOT NULL THEN
        RETURN __user_id;

    ELSE
        INSERT INTO test_table(id, tvalue)
        VALUES (user_id, TRUE) 
        RETURNING id
        INTO __user_id;
        RETURN __user_id;
    END IF;
END;
$$;

称呼：

SELECT * FROM test_table WHERE id = test_function(4);

要重现此问题，请传递表中尚不存在的任何整数。

标签： sqlpostgresqlconcurrencyupsert

解决方案

该示例在多个地方被破坏。

  • 无需使用EXECUTE.
  • SELECT *在函数中是错误的。
  • 您的表定义应该有一个UNIQUEorPRIMARY KEY约束(id)。

  • 最重要的SELECT是，最后的陈述必然会失败。由于该函数是VOLATILE（必须是），它对表中的每个现有行进行一次评估。即使这有效，这也将是一场性能噩梦。但事实并非如此。就像@user2864740 评论的那样，可见性也存在问题。Postgres 根据函数的结果检查每一行现有的行，然后添加 1 行或更多行，并且这些行尚未在SELECT正在操作的快照中。

    SELECT * FROM test_table WHERE id = test_function(4);

这会起作用（但见下文！）：

CREATE TABLE test_table (
  id     int PRIMARY KEY  --!
, tvalue bool
);

CREATE OR REPLACE FUNCTION test_function(_user_id int)
  RETURNS test_table LANGUAGE sql AS
$func$
   WITH ins AS (
      INSERT INTO test_table(id, tvalue)
      VALUES (_user_id, TRUE) 
      ON CONFLICT DO NOTHING
      RETURNING *
      )
   TABLE ins
   UNION ALL
   SELECT * FROM test_table WHERE id = _user_id
   LIMIT 1
$func$;

并将您的替换SELECT为：

SELECT * FROM test_function(1);

db<>在这里摆弄

有关的：

并发调用仍然存在竞争条件。如果发生这种情况，请考虑：

推荐阅读