Port Bug Report: I receive 'Deadlock detected' errors when running a row-locking query.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Mark Wright
Your email address      : mwright@pro-ns.net

Category                : runtime: back-end
Severity                : non-critical

Summary: I receive 'Deadlock detected' errors when running a row-locking query.

System Configuration
--------------------
  Operating System   : Linux 2.0.34 ELF, Debian Distribution

  PostgreSQL version : 6.5

  Compiler used      : gcc 2.7.2.3

Hardware:
---------
Pentium (686), 32meg

Versions of other tools:
------------------------
make 3.77
flex 2.5.4


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

Problem Description:
--------------------
When I have more than 2 clients performing the same query, which involves record
locking, I receive the following errors:

NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
ERROR:  WaitOnLock: error on wakeup - Aborting this transaction

The point of the query is to grab the next available record in such a way that
multiple clients will not grab the same record.  The query is contained inside a
PL/pgSQL function.  Source for the function is below.


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

Test Case:
----------
The following SQL script will create the tables, indices and function necessary
to reproduce the error.  If you then execute these commands, it should re-create
the problem:
    perl -e 'for ($i=0;$i<200;++$i){system \
        "psql -c \"select get_next_test_attendee();\" >> $$.txt";}'&
    perl -e 'for ($i=0;$i<200;++$i){system \
        "psql -c \"select get_next_test_attendee();\" >> $$.txt";}'&
    perl -e 'for ($i=0;$i<200;++$i){system \
        "psql -c \"select get_next_test_attendee();\" >> $$.txt";}'&

(The same error occurs if I use DBI+DBD::Pg in a Perl script instead of psql.)

===============================================================================
                                  Begin Script
===============================================================================
drop table test_attendees;
drop sequence test_attendees_id_number_seq;
create table test_attendees
(
    id_number        serial,
    print_status    char        default 'R',
    name            varchar(20)
);
create index idx_test_attendees_name on test_attendees(name);


DROP FUNCTION get_next_test_attendee ();
CREATE FUNCTION get_next_test_attendee() returns int4 AS '
DECLARE
    test_attendee_rec       RECORD;
BEGIN
    FOR test_attendee_rec IN SELECT * FROM test_attendees
                    WHERE print_status = ''R''
                    ORDER BY id_number FOR UPDATE OF test_attendees
    LOOP
        -- If more changes in test_attendee are to be made than just setting
        -- status to P, do them all in one UPDATE. The record is
        -- locked now and the lock will release only when our entire
        -- transaction commits or rolls back - not when we update it.
        UPDATE test_attendees SET print_status = ''Y''
            WHERE id_number = test_attendee_rec.id_number;

        -- Now we return from inside the loop at the first
        -- row processed. This ensures we will process one
        -- row at max per call.
        RETURN test_attendee_rec.id_number;
    END LOOP;

    -- If we reach here, we did not find any row (left) with
    -- print_status = R
    return -1;

END;' LANGUAGE 'plpgsql';

insert into test_attendees (name) values ('name1');
insert into test_attendees (name) values ('name2');
...

===============================================================================
                                   End Script
===============================================================================



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

Solution:
---------


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