主页 / coding / 问题 / 79184042
Accepted
Han Qi
Asked: 2024-11-13 16:48:40 +0800 CST

当单个查询同时进行写入和读取时，为什么数据库不读取新插入的数据？

  • 772
DROP TABLE IF EXISTS A;
CREATE TABLE a (id int);

CREATE or replace FUNCTION insert_and_return(int) 
    RETURNS int AS $$
    BEGIN
        INSERT INTO a VALUES ($1);
        RETURN $1;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM insert_and_return(10),A AS y;

上述代码通过函数在 SELECT 中具有数据插入副作用。

预期的

插入并返回 ID
10 10

我希望在进行交叉连接insert_and_return时插入一个新行，然后 SELECT 才会看到它并在结果中返回它。FROM insert_and_return(10),A

实际的：

空白结果集

问题：

为什么我看不到数据？我必须再次选择才能看到它。

我认为 SELECT 总是看到比实际少 1 行。

我希望了解幕后事件的顺序，数据是否已经插入但以某种方式隐藏在 SELECT 中，或者在执行 SELECT 时未插入。

相关问题

  • 这个问题与隔离级别有关吗？或者它无关紧要，因为隔离级别仅适用于多个事务？

  • 为什么可以使用 CASE 或类似的模式动态生成数据，SELECT price, price * 0.9但在这里却不起作用？

我尝试使用 CTE 创建分离，但看到相同的行为，即我只在第二次运行整个查询时才看到数据。这可以用上述相同的原因来解释吗，或者 CTE 有其自己的注意事项？

WITH inserted_data AS (
    INSERT INTO a (id) VALUES (10)
    RETURNING id
)
SELECT * FROM inserted_data,A;

为什么其他帮助都失败了

我在网上找到的所有资料都讨论了来自多个事务的并发读/写，我认为这是不相关的，因为这是一个隐式事务中的单个查询。

第一个查询的来源

我从这篇文章中对其进行了调整，该文章的重点是负载平衡而不是我的问题：https://www.cybertec-postgresql.com/en/why-select-from-table-is-not-a-read，请指出我的问题是过于极端而与实际不相关

sql

2 个回答

  1. 在 PostgreSQL 中，您观察到的行为是由于查询中语句的执行顺序造成的。当您运行：

    SELECT * FROM insert_and_return(10), A AS y;

    函数执行：首先调用 insert_and_return(10) 函数，将值 10 插入表 A。
    表 A 扫描：执行该函数后，PostgreSQL 尝试从 A（别名为 y）检索记录。

    但是，PostgreSQL 的事务隔离会阻止函数的插入操作立即对查询可见。这是因为 PostgreSQL 在插入任何实际行之前会有效地为查询的两个部分构建执行计划。由于 PostgreSQL 中的可见性规则，插入的行不可用于同一查询，该规则将单个语句中的数据修改与数据读取分开。

    DROP TABLE IF EXISTS a;
CREATE TABLE a (id int);

CREATE OR REPLACE FUNCTION insert_and_return_read(val int)
    RETURNS TABLE (inserted_id int, existing_id int) AS $$
BEGIN
    RETURN QUERY
    WITH ins AS (
        INSERT INTO a (id) VALUES (val) RETURNING id
    )
    SELECT ins.id AS inserted_id, a.id AS existing_id
    FROM ins
    LEFT JOIN a ON TRUE;  -- This will show the newly inserted value alongside any existing values in 'a'
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM insert_and_return_read(10);
    • 0
  2. Best Answer

    我原本期望在进行交叉连接insert_and_return时插入新行，但在结果中看到它并返回它。 实际：空白结果集FROM insert_and_return(10),ASELECT

    该函数确实会插入行，也会将其返回到您的查询。然后，它会与查询在表中看到的空集进行交叉连接a，从而使整个查询无效。EXPLAIN ANALYZE VERBOSE可以向您展示中间查询确实会产生结果rows=1，并且只有最后一个查询最终为空，因为该行已连接到零行：
    db<>fiddle 上的演示

    explain analyze verbose SELECT * FROM insert_and_return(10),A AS y;
    嵌套循环（成本=0.25..61.26 行=2550 宽度=8）（实际时间=0.195..0.196行=0循环=1）

      输出：insert_and_return.insert_and_return，y.id

      -> 在 public.insert_and_return 上进行函数扫描（成本=0.25..0.26行=1宽度=4）（实际时间=0.187..0.187 行=1 循环=1）

            输出：insert_and_return.insert_and_return

            函数调用：insert_and_return(10)

      -> 在 public.ay 上进行 Seq 扫描（成本=0.00..35.50 行=2550 宽度=4）（实际时间=0.006..0.006行=0循环=1）

            输出：y.id
    规划时间：0.106 毫秒
    执行时间：0.239 毫秒

    我不确定你在这里尝试什么以及为什么，但如果你想查看你插入的整行，而不仅仅是你为某一列提供的值，听起来你只是希望

    insert into a values(10)returning *;

    如果您想在插入后立即查看整个表的最终状态，您正在寻找union：

    table a
union all
select insert_and_return(10);

    该table命令只是select*fromPostgreSQL 中的 的简写形式。这是无条件涉及表的唯一合理形式a- 否则，如果该表中有任何内容，逗号,交叉连接将以您刚刚插入的行数、您之前拥有的行数的倍数、所有行与其他所有行配对并列出，如此多次。

    如果您尝试将传入的有效载荷与触发所有triggers（以及rules，有一些警告）后最终写入表中的内容进行比较，这正是所returning显示的：

    如果目标表上有触发器（第 37 章），则可用的数据RETURNING是触发器修改的行。因此，检查触发器计算的列是 的另一个常见用例RETURNING。

    这意味着如果值以某种方式被处理，returning将不会显示最初输入的内容，而只会显示最终进入表格的内容，因此returning *会忽略原始输入。要查看它，您可以将其作为常量重复：

    insert into a values('your_input_value')returning 'your_input_value',*;

    演示中的示例：

    create function trg1_f()returns trigger as $f$
begin
   NEW.id = NEW.id + 1; --incoming payload gets increased here
   return NEW;
end $f$ language plpgsql;

create trigger trg1 before insert on a--happens before the write
for each row execute function trg1_f();

insert into a values(10)returning 10 as payload,*;--payload added as constant
    有效载荷 ID
    10 11

    问题：为什么我看不到数据？我必须再次执行 SELECT 才能看到数据。
    我认为 SELECT 总是显示比实际少 1 行。
    我希望了解事件背后的顺序，数据是否已插入但以某种方式隐藏在 SELECT 中，或者在执行 SELECT 时未插入。

    这些操作实际上没有任何确定的顺序。函数扫描和表扫描可能会按某种顺序执行，也可能不会，或者同时并行执行 -规划器/优化器可以随意重新排列它们，无论它认为合适与否。

    您可以使用子查询和lateralCTE 在单个多语句查询中定义某种流，但该流不能涉及表状态，它不能写入表，然后从表写入，然后再写入表。由于MVCC，查询中的所有语句在查询的整个生命周期内都会看到该表的完全相同的快照。

    相关问题
    这个问题与隔离级别有关吗？或者它无关紧要，因为隔离级别仅适用于多个事务？

    完全正确。事务隔离与单个查询的语句无关。

    为什么可以使用 CASE 或类似的模式动态生成数据，SELECT price, price * 0.9但在这里却不起作用？

    它在这里确实有效，您确实动态生成了数据：您指定了一个常量10，它确实被写入表中并从表中返回。只是您决定cross join使用空集，所以您得到了一个空集。另一个例子：

    select price, price*0.9 
from (values(1)) as v(price)
cross join (select 2 limit 0) as unrelated_empty_set(col1);

    无论你与空集cross join是否相关，都无关紧要。

    我尝试使用 CTE 创建分离，但看到相同的行为，即我只在第二次运行整个查询时才看到数据。这可以用上述相同的原因来解释吗，或者 CTE 有其自己的注意事项？

    WITH inserted_data AS (
   INSERT INTO a (id) VALUES (10)
   RETURNING id
)
SELECT * FROM inserted_data,A;

    没关系。cross join A旧的隐式逗号连接语法,A会抹去您的结果，inserted_data因为您使用空集进行交叉连接：在此查询的整个生命周期中，快照A不会反映查询旨在作为其结果应用于它的任何更改。

    如果你真的希望一切都并排，那么就用合格的连接来交换它：

    WITH inserted_data AS (
    INSERT INTO a (id) VALUES (10)
    RETURNING id
)
SELECT * 
FROM inserted_data
FULL JOIN a USING(id);

    这将获取您刚刚在左侧插入的所有内容，如果是全新的，则将其与右侧的空值配对，或者与id您之前在表中已有的任何匹配项配对，然后列出右侧a的所有其他内容。与 不同，它只会乘以在目标表中找到多个匹配项的新来者。across joinid

    您还可以使用前面提到的方法一次性列出所有内容union：

    WITH inserted_data AS (
    INSERT INTO a (id) VALUES (10)
    RETURNING id
)
SELECT*FROM a
UNION ALL
SELECT*FROM inserted_data;
    • 0

相关问题