SELECT FOR UPDATE differs inside and outside a pl/pgsql function (7.4) - Mailing list pgsql-bugs

Should there be a difference between the behavior of a "select for
update" typed into psql directly versus "select into variable_name for
update" done within a function?

In other words:  Is this a bug or a user misunderstanding:

1.  Run the following commands to set up a table called
    mytable and a function called myfunction:

    ------------------------------

    create table mytable (a int);
    insert into mytable values (1);
    insert into mytable values (2);
    insert into mytable values (3);

    create or replace function myfunction() returns integer as '
    DECLARE
    myrow     mytable%ROWTYPE;
    BEGIN
    LOOP
      select * into myrow from mytable limit 1 for update;
      if found then exit;
      end if;
    END LOOP;
    return myrow.a;
    end; ' language 'plpgsql';
    ------------------------------

2.  Then open up two psql sessions and run the following commands:

    +-----------------------------------------+----------------------+
    | psql Session A                          | psql Session B       |
    +-----------------------------------------+----------------------+
    |begin transaction;                       |                      |
    |                                         | begin transaction;   |
    |select * from mytable limit 1 for update;|                      |
    |                                         | select myfunction(); |
    |delete from mytable where a=1;           |                      |
    |commit;                                  |                      |
    +-----------------------------------------+----------------------+

Session B's "select myfunction();" will hang, and it will continue to
hang even after session A commits.  Is this expected behavior?

There are two ways to have session B not hang after the commit:

1.  Don't do the "delete from mytable where a=1;".

    Session B's "select myfunction();" will then return after
    Session A commits, and with a value of 1.

    Or,

2.  Instead of running "select myfunction();" in Session B,
    run two manual "select * from mytable limit 1 for update;"s.

    The first manual select-for-update will hang until Session A's
    transaction commits, after which the second manual select-for-update
    in session A will succeeds.

    This one really confuses me--should a function not be able
    to find a row when a manual command can?

So I guess I'm curious as to:

1.  Whether this is a bug or not.  I'm guessing yes.

    (I expected the multiple select-for-update attempts in the
    function to the same behavior as multiple select-for-update's
    done manually.  That is, I expected both types of B's
    select-for-updates selecting locked rows to hang until session A's
    commit, immediately fail to find any row, and then succeed on the
    next try.  It would be nice if session B's first select were to
    have transparently succeeded on the a=2 row, something which I think
    would be a legal thing to happen, but as it's not what the
    documentation implies would happen I didn't expect that.  In any
    event I did not expect the select-for-update within pgsql to
    continually fail to find a row.)

2.  If there's a better way to have multiple transactions lock rows
    with select-for-update without any chance of the transaction
    erroring out, (such as would occur with serializable--and that
    would mean I'd have to have to move more of the logic to the
    application.)

    (I don't know of a way to avoid even the busy-looping--though I
    could call sleep functions from plperl or something to lower the
    cpu load.  BTW, it would be nice to have some plpgsql-native sleep
    function just to more easily test for problems like this.)

3.  If there's some really elegant solution out there, such as a
    way to do a "select for update where not locked" to search for
    rows no one has a conflicting lock on.  (To me this would seem
    to be the best of all possible solutions.)

I'm running version() "PostgreSQL 7.4 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)".

--
Mark Shewmaker
mark at primefactor dot com