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.

• Lennart - Slava Ukraini: Have you investigated INSTEAD OF triggers? Here's an blog-article: vibhorkumar.blog/2011/10/28/instead-of-trigger and here's the official documentation: postgresql.org/docs/10/static/sql-createtrigger.html

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

• Gajus: While this makes sense (as far as I can tell), it did not work as expected. I started to get duplicate tasks at a millisecond concurrency. I ended up rewriting my VIEW to avoid the use of LEFT JOIN.

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.