更新语句使用FROM和ctids。

huangapple go评论58阅读模式
英文:

update statement with FROM and ctids

问题

I have an update statement of the following (simplified) form:

UPDATE changes.table1 a
SET checked_out_at = a.checked_out_at
FROM unified.view2 b
WHERE a.ctid = '(4,48)'
AND b.some_pkey = a.some_pkey
RETURNING *;

这个语句将会更新失败。当我运行它时,我看到 0 行UPDATE 0。但是,如果我通过删除连接的表来修改它,更新将会成功进行(只有一次,毕竟 ctid 在此之后会改变,我需要找到新的 ctid):

UPDATE changes.table1 a
SET checked_out_at = a.checked_out_at
WHERE a.ctid = '(4,48)'
RETURNING *;

不幸的是,我需要连接的表在 RETURNING 子句中的某些数据。

为什么这两个功能不兼容?连接的行是否与单个表不同的 ctid?我知道我对所有低级系统列都没有充分的了解,但这似乎非常直接。是否可以使这个或类似的功能工作?

英文:

I have an update statement of the following (simplified) form:

     UPDATE changes.table1 a
        SET checked_out_at = a.checked_out_at
       FROM unified.view2 b
      WHERE a.ctid = '(4,48)'
        AND b.some_pkey = a.some_pkey
  RETURNING *;

This statement will just fail to update. I see 0 rows and UPDATE 0 when I run it. But if I modify it by dropping the joined table, the update will go through (just once, the ctid changes after all, and I'd need to find the new one):

     UPDATE changes.table1 a
        SET checked_out_at = a.checked_out_at
      WHERE a.ctid = '(4,48)'
  RETURNING *;

Unfortunately, I need the joined table for some of the data in the RETURNING clause.

Why are these two features incompatible? Do the joined rows somehow have ctids different from the single table? I know that I don't have a proper understanding of all the low-level system columns, but this still seems rather straight forward. Can this or something similar be made to work?

答案1

得分: 1

如果目标是限制更新的行数,请考虑使用如下子查询:

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
  FROM (SELECT c.ctid AS original_ctid, b.*
          FROM changes.table1 c
          JOIN unified.view2 b
            ON b.some_pkey = a.some_pkey
         LIMIT 10) d
 WHERE a.ctid = d.original_ctid
RETURNING *;

这将限制更新的行数,同时避免额外的表格或索引扫描,因为将使用 TID 扫描来找到要更新的行。它还消除了在单独查询中确定 CTID 的需求。

英文:

If the goal is to limit the number of rows that get updated, consider using a subquery as follows:

UPDATE changes.table1 a
   SET checked_out_at = a.checked_out_at
  FROM (SELECT c.ctid AS original_ctid, b.*
          FROM changes.table1 c
          JOIN unified.view2 b
            ON b.some_pkey = a.some_pkey
         LIMIT 10) d
 WHERE a.ctid = d.original_ctid
RETURNING *;

This will limit the number of updated rows while avoiding an extra table or index scan since a TID scan will be used to find the rows to be updated. It also eliminates the need to determine CTIDs in separate queries.

huangapple
  • 本文由 发表于 2023年6月1日 05:39:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76377467.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定