如何將 FOR UPDATE 與使用 LEFT JOIN 建構的視圖一起使用？

假設一個簡單的表，例如

\d+ task

     Column       |           Type           |                     Modifiers                     | Storage  |
-------------------+--------------------------+---------------------------------------------------+----------+
id                | integer                  | not null default nextval('task_id_seq'::regclass) | plain    |
parent_task_id    | integer                  |                                                   | plain    |
cinema_id         | integer                  | not null                                          | plain    |
venue_id          | integer                  |                                                   | plain    |
movie_id          | integer                  |                                                   | plain    |
event_id          | integer                  |                                                   | plain    |
result            | json                     |                                                   | extended |
context           | json                     |                                                   | extended |
guide             | json                     |                                                   | extended |
started_at        | timestamp with time zone |                                                   | plain    |
ended_at          | timestamp with time zone |                                                   | plain    |
created_at        | timestamp with time zone | not null default now()                            | plain    |
updated_at        | timestamp with time zone | not null default now()                            | plain    |
Indexes:
   "task_pkey" PRIMARY KEY, btree (id)
   "public_task_cinema_id1_idx" btree (cinema_id)
   "public_task_event_id4_idx" btree (event_id)
   "public_task_movie_id3_idx" btree (movie_id)
   "public_task_parent_task_id0_idx" btree (parent_task_id)
   "public_task_started_at6_idx" btree (started_at)
   "public_task_venue_id2_idx" btree (venue_id)
Foreign-key constraints:
   "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
   "task_cinema_id_fkey" FOREIGN KEY (cinema_id) REFERENCES cinema(id) ON DELETE CASCADE
   "task_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id) ON DELETE CASCADE
   "task_movie_id_fkey" FOREIGN KEY (movie_id) REFERENCES movie(id) ON DELETE CASCADE
   "task_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES venue(id) ON DELETE CASCADE
Referenced by:
   TABLE "task" CONSTRAINT "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
Triggers:
   update_task_updated_at BEFORE UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column()

假設選擇沒有父任務或父任務已完成的任務的簡單視圖：

CREATE VIEW outstanding_task AS
 SELECT
   t1.*
 FROM task t1
 LEFT JOIN task t2 ON t2.id = t1.parent_task_id
 WHERE
   t2.id IS NULL OR
   t2.result IS NOT NULL;

我正在嘗試將此視圖用於FOR UPDATE：

SELECT *
FROM outstanding_task
LIMIT 1
FOR UPDATE SKIP LOCKED;

但是，在postgres v10.4 中，這會產生錯誤：

錯誤：FOR UPDATE 不能應用於外連接的可空端

如何與使用FOR UPDATE建構的視圖一起使用LEFT JOIN？

注1：該問題特定於視圖。如果沒有視圖，我可以使用FOR UPDATE OF t1.

未經測試，因為我目前沒有 postgres 工作，但這個概念應該很清楚：

嘗試對基表使用另一個內部聯接中的視圖，然後更新基表。

select *
from task as t
inner join outstanding_tasks as o on t.id=o.id
for update of task

如果您只想很少鎖定行，這可能沒有多大意義，但在技術上可以在FOR UPDATE OF其中定義視圖。

在我的範例中，task只有一列，並且我使用了簡單的連接條件，但總體機制是相同的：

CREATE OR REPLACE VIEW task_fu AS 
   SELECT t1.id 
     FROM task AS t1 
     LEFT JOIN task AS t2 ON t1.id * 10 = t2.id 
      FOR UPDATE OF t1 SKIP LOCKED;

然後，在一個會話中，一個人可以做

BEGIN;
BEGIN
SELECT * FROM task_fu LIMIT 5;
id 
────
 1
 2
 3
 4
 5

而在另一個，這就是你得到的：

SELECT * FROM task_fu LIMIT 5;
id 
────
 6
 7
 8
 9
10

如果您碰巧ORDER BY在查詢 ( SELECT * FROM task_fu ORDER BY id LIMIT 5;) 中使用了 ，那麼第二個會話將返回 0 行可能很重要。當潛在WHERE子句將鎖定同一組行時，也會出現同樣的情況。

引用自：https://dba.stackexchange.com/questions/208173