postgresql Postgres中是否有一个与FOR UPDATE锁等价的视图锁？

cxfofazt 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(129)

我在view上有一个INSTEAD OF UPDATE触发器：

CREATE VIEW usr_view AS
  SELECT
    us.id,
    uv.col1,
    uv.col2,
    uv.col3
  FROM usr_static AS us
  LEFT JOIN usr_version AS uv ON us.id = us.id AND uv.create_time = (
    SELECT MAX(uv2.create_time)
    FROM usr_version AS uv2
    WHERE uv2.id = us.id
  )

此触发器将新行插入视图的一个基础表中：

CREATE FUNCTION shared.on_update_view() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    view_name text := TG_TABLE_NAME;
    schema_name text := TG_TABLE_SCHEMA;
    version_table_name text := schema_name || '.' || view_name || '_version';
BEGIN
    -- Insert into the version table
    EXECUTE format('INSERT INTO %s (col1, col2, col3) SELECT col1, col2, col3 FROM (SELECT $1.*) AS new_table', version_table_name)
    USING NEW;

    RETURN NEW;
END;
$$;

我面临的问题是，当两个UPDATE查询同时在视图上运行时，会出现竞争条件：

// row 1 in usr_version
(id, col1, col2, col3, ...)  VALUES (1, 'A', 'B', 'C', ...)

-- query 1
UPDATE usr_view SET col1 = 'foo' WHERE id = 1;

-- query 2
UPDATE usr_view SET col2 = 'bar' WHERE id = 1;

query 1将在usr_version中插入以下行：

(id, col1, col2, col3, ...)  VALUES (1, 'foo', 'B', 'C', ...)

但是因为query 2同时运行，它会插入：

(id, col1, col2, col3, ...)  VALUES (1, 'A', 'Bar', 'C', ...)

由于view显示了usr_version中最新创建的行，因此视图如下所示：

-- if query 1 inserted faster:
(id, col1, col2, col3, ...)  VALUES (1, 'foo', 'B', 'C', ...)

-- if query 2 inserted faster:
(id, col1, col2, col3, ...)  VALUES (1, 'A', 'Bar', 'C', ...)

然而，预期的结果将是：

(id, col1, col2, col3, ...)  VALUES (1, 'Foo', 'Bar', 'C', ...)

据我所知，Postgres在更新table的行时设置了FOR UPDATE锁，以防止此类情况发生。不过，似乎Postgres没有使用INSTEAD OF UPDATE触发器更新view。
确保两个查询连续执行以获得预期结果的最佳方法是什么？
谢谢你！：）

postgresql

来源：https://stackoverflow.com/questions/76435225/is-there-an-equivalent-of-a-for-update-lock-for-views-in-postgres

1条答案

按热度按时间

dojqjjoe1#

只有当只有一个（逻辑）行时，视图定义才有效。这是你的用例吗？如果将视图重写为，则视图的性能会更好

CREATE VIEW usr AS
SELECT col1,
       col2,
       col3
FROM usr_version
ORDER BY create_time DESC
LIMIT 1;

并在usr_view.create_time上创建索引。
但我不认为有一个值得称赞的方式来更新你的观点是安全的种族条件。你将不得不在table本身上放一把沉重的锁，这是一个坏主意，因为它会阻止自动吸尘器完成它的工作。
使用不同的模型：

/* use a regular table, not a view */
CREATE TABLE usr (
   col1 text,
   col2 text,
   col3 text
);

/* make sure there can be only a single row */
CREATE UNIQUE INDEX ON usr ((1));

CREATE TABLE usr_version (
   col1 text,
   col2 text,
   col3 text,
   create_time timestamp with time zone NOT NULL
);

CREATE FUNCTION upd_usr() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO usr_version (col1, col2, col3, create_time)
   VALUES (NEW.col1, NEW.col2, NEW.col3, clock_timestamp());

   RETURN NEW;
END;$$;

CREATE TRIGGER upd_usr AFTER INSERT OR UPDATE ON usr FOR EACH ROW
EXECUTE FUNCTION upd_usr();

这是自动摆脱竞争条件。

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql Postgres中是否有一个与FOR UPDATE锁等价的视图锁？

1条答案

相关问题

热门标签

最新问答