SQL Error [0A000]: ERROR: FOR UPDATE cannot be applied on the nullable side of the outer join-CodePudding

SELECT
    TB1.ID AS USER_ID,
    TB1.USER_NAME AS USER_NAME,
    TB1.BIRTHDATE AS BIRTHDATE ,
    TB2.AGE AS AGE,
    TB3.GENDER AS GENDER ,
    TB4.SUBJECT AS SUBJECT ,
FROM
    TABLE1  AS TB1
LEFT JOIN TABLE2 AS TB2 ON
    TB1.ID= TB2.ID
LEFT JOIN TABLE3 AS  TB3 ON
    TB1.ID= TB3.ID
LEFT JOIN TABLE4  AS TB4 ON
    TB1.ID= TB4.ID FOR UPDATE OF TB1 NOWAIT ;

SELECT
    TB1.ID AS USER_ID,
    TB1.USER_NAME AS USER_NAME,
    TB1.BIRTHDATE AS BIRTHDATE ,
    TB2.AGE AS AGE,
    TB3.GENDER AS GENDER ,
    TB4.SUBJECT AS SUBJECT ,
FROM
    TABLE1  AS TB1
LEFT JOIN TABLE2 AS TB2 ON
    TB1.ID= TB2.ID
LEFT JOIN TABLE3 AS  TB3 ON
    TB1.ID= TB3.ID
LEFT JOIN TABLE4  AS TB4 ON
    TB1.ID= TB4.ID FOR UPDATE OF TB1,TB2,TB3,TB4 NOWAIT;

Why can't I lock the tables that are in the outer join. The SQL statement above can produce results, but the SQL statement below gives an error.

CodePudding user response：

The best explanation can be found in this source comment in src/backend/optimizer/plan/initsplan.c:

    /*
     * Presently the executor cannot support FOR [KEY] UPDATE/SHARE marking of
     * rels appearing on the nullable side of an outer join. (It's somewhat
     * unclear what that would mean, anyway: what should we mark when a result
     * row is generated from no element of the nullable relation?)  So,
     * complain if any nullable rel is FOR [KEY] UPDATE/SHARE.
     *
     * [...]
     */

That comment was added by Tom Lane in 2006, and here and here are mailing list posts that explain why. In essence, FOR UPDATE is there to prevent lost updates by locking the row so that it cannot change through subsequent concurrent data modifications. But the NULL row generated by an outer join can always change if new rows are inserted to the table on the nullable side of the join.

The SQL standard knows FOR UPDATE only in the context of a cursor, and the expectation is that such an updatable cursor allows updating the found row later with UPDATE ... WHERE CURRENT OF <cursor name>, which seems to back up Tom's interpretation.

Why don't you use an inner join?