如何使用使用左联接构建的视图进行更新？

Question

假设一个简单的表，例如。

\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;

但是，在波斯特格斯 v10.4中，这会产生一个错误：

错误:因为更新不能应用于外部联接的可空侧。

如何在使用FOR UPDATE构建的视图中使用LEFT JOIN？

注1:这个问题是针对意见的。没有视图，我可以使用FOR UPDATE OF t1。

Answer 1

没有经过测试，因为我目前还没有postgres的工作，但是这个概念应该是明确的：

尝试对基表使用另一个内部联接中的视图，然后更新基表。

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