"Khan, Tanzeel" <tzlkhan@amazon.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_joinWHERE (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
