主页 / dba / 问题 / 2760
Accepted
youngthing
Asked: 2011-05-18 03:11:53 +0800 CST

Plpgsql 似乎正在删除和插入而不是更新 - 为什么？

  • 772

我正在使用 postgreSQL 7.4。

我有一张大桌子，称之为 table_a：

key1 INT NOT NULL, 
key2 INT NOT NULL, 
data INT NOT NULL, 
itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::timestamp without time zone))::INTEGER

以及总结 key1 的最后更新时间的表，将其称为 table_b：

key1        INT NOT NULL,
max_itstamp INT NOT NULL

我在 plpgsql 中创建了一个触发器函数来根据需要更新或插入 table_b 中的行：

CREATE OR REPLACE FUNCTION table_b_update() RETURNS TRIGGER AS '
 DECLARE
  l_key1 INT;
  l_itstamp INT;
 BEGIN
  l_key1 := new.key1;
  l_itstamp := new.itstamp;
  PERFORM TRUE FROM table_b WHERE key1=l_key1;
  IF NOT FOUND THEN 
   INSERT INTO table_b(key1, max_itstamp) values (l_key1, l_itstamp);
  ELSE
   UPDATE table_b SET max_itstamp=l_itstamp WHERE key1=l_key1;
  END IF;
  RETURN NULL;
 END'
LANGUAGE plpgsql IMMUTABLE;

然后我将触发器附加到 table_a：

CREATE TRIGGER table_a_trigger1 AFTER INSERT OR UPDATE ON table_a FOR EACH ROW
EXECUTE PROCEDURE table_b_upate();

现在，将新数据插入 table_a 的时间逐渐增加。table_b 的文件足迹稳步增长。

我在函数中使用 RAISE NOTICE 命令来确认 If 语句在第一次调用每个键后导致更新而不是插入。

由于每个 INSERT 的 table_a 插入时间都会增加，因此我在 table_b 上尝试了 VACUUM FULL。table_a 的插入时间大大减少了。table_b 的文件大小已大大减少。在 VACUUM FULL 之后，table_a 插入时间再次开始增长。我不想在每次插入 table_a 后都执行 VACUUM FULL。

UPDATE 是否有可能实际上在 table_b 中执行 DELETE 和 INSERT？

postgresql plpgsql

2 个回答

  1. PostgreSQL 并没有真正进行更新，就其操作方式而言，它相当于删除和插入。它保留行的旧版本并创建所有未来事务都会看到的新版本。最终 vacuum 回收旧的行空间以供重用。一句忠告，立即更新到受支持的版本。7.4 非常旧，不再受支持，并且已知存在永远无法修复的数据吞噬错误。我建议直接升级到 9.0，并研究删除的隐式强制转换的问题，这将是最大的问题。当我们进入 8.3 第一个版本以删除隐式转换时，我们必须在 out app 中修复三个查询。

    • 3
  2. Best Answer

    我没有 7.4 可以测试，但我猜：

    • 每次你做一个vacuum full表格紧凑
    • 每次你update，新版本的行（见MVCC）在旧版本被vacuum

    请参阅此处以获取更详细地解释这一点的文档，但简单的解决方案是根本不运行vacuum full- 只是vacuum。然后你的表可能会进入稳定状态，数据中会留下“漏洞”，供以后更新使用。

    至于“插入时间”，我对你的结果感到惊讶。我的期望是在 a 之后insert时间会变慢vacuum full- 但如果所有块都在缓存中，则在当前块内查找可用空间的开销可能高于在堆末尾添加新行，即使数量访问的块数更高

    • 1

相关问题