============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Mark Wright
Your email address : mwright@pro-ns.net
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium (686)
Operating System (example: Linux 2.0.26 ELF) : Linux 2.0.34 - Debian
Distribution
PostgreSQL version (example: PostgreSQL-6.4) : PostgreSQL-6.5
Compiler used (example: gcc 2.8.0) : gcc 2.7.2.3
Please enter a FULL description of your problem:
------------------------------------------------
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.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
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
============================================================================
===
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com
