Re: SELECT FOR UDPATE behavior inside joins

"Khan, Tanzeel" <tzlkhan(at)amazon(dot)com> writes:
> I am trying to understand the SELECT FOR UPDATE behavior when it is not returning rows back to client.

> postgres=> CREATE TABLE t (col1 INT, col2 INT);
> postgres=> INSERT INTO t VALUES (1, 1);

> S1: BEGIN; UPDATE t SET col2 = col2 + 1 WHERE col1 = 1;
> S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 = 1 FOR UPDATE) UPDATE t SET col2 = t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 = t_self_join.col2);
> S1: COMMIT;
> S2: zero rows updated

> Why does session 2 update zero rows ?

Since the CTE has FOR UPDATE, it blocks and returns the updated-by-S1
version of the row. But the outer query initially reads the old
version of the row, so the join condition fails, and we never get
to the lock-row-and-recheck behavior of UPDATE.

I am not sure what you are hoping to accomplish with that self-join.
I suppose this is an oversimplified example, but it's too
oversimplified for anyone to see why you'd want to do it like that.

regards, tom lane