How to use FOR UPDATE with a view that is built using LEFT JOIN?

Question

Assuming a simple table, e.g.

\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()

Assuming a simple view that selects tasks that either do not have parent task or the parent task is complete:

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;

I am attempting to use this view with FOR UPDATE:

SELECT *
FROM outstanding_task
LIMIT 1
FOR UPDATE SKIP LOCKED;

However, in postgres v10.4 this produces an error:

ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join

How to use FOR UPDATE with a view that is built using LEFT JOIN?

Note 1: The question is specific to views. Without a view I could use FOR UPDATE OF t1.

Answer 1

Untested, as I don't currently have postgres working, but the concept should be clear:

Try using the view in another inner join against the base table, then update the base table.

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

Answer 2

It might not make much sense if you want to lock the rows only seldom, but it is technically possible to define the view with FOR UPDATE OF in it.

In my example, task has only one column, and I use a simple join condition, but the overall mechanism is the same:

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;

Then, in one session, one can do

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

While in another, this is what you get:

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

It might be important that if you happen to use an ORDER BY in the query (SELECT * FROM task_fu ORDER BY id LIMIT 5;), the second session will return 0 rows. The same is to be expected when the potential WHERE clauses would lock the same set of rows.