(using v6.5, Debian Linux 2.0.34, some sort of Pentium)
I have a PL/pgSQL function I want to run from many sessions. The function
essentially just grabs the next record and marks it as 'taken'. The idea is
that I can have multiple clients, all pulling unique records from a table.
Executing this function multiple times, from multiple sessions, seems to
upset Postgres something awful. Specifically, I get these errors:
NOTICE: Deadlock detected -- See the lock(l) manual page for a possible
cause.
(which causes my perl script to do this:
DBD::Pg::st execute failed: ERROR: WaitOnLock: error on wakeup - Aborting
this transaction Database handle destroyed without explicit disconnect.)
There doesn't seem to be a man page for lock(1), and lock(2) says that it's
an 'unimplemented system call'. Any idea why it's doing this, and is there
anything I can do about it?
The table I'm searching against is:
---
CREATE TABLE Attendees
(
Id_Number SERIAL,
Print_Status CHAR default 'N',
...
);
---
The function that's upsetting Postgres:
---
CREATE FUNCTION get_next_attendee() returns int4 AS '
DECLARE
attendee_rec RECORD;
BEGIN
FOR attendee_rec IN SELECT * FROM attendees WHERE print_status = ''R''
ORDER BY id_number FOR UPDATE OF attendees
LOOP
-- If more changes in 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 attendees SET print_status = ''P''
WHERE id_number = 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 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';
---
I'm running 4 copies of the following client scripts (I get the same error
if I use psql):
---
#!/usr/bin/perl
use DBI;
use English;
open(OUTFILE, ">$PID.tst");
for ($i = 0; $i < $ARGV[0]; ++$i)
{
my $conn = DBI->connect('DBI:Pg:dbname=register', 'register', '',
{RaiseError => 1, AutoCommit => 1} );
my $sql_query = $conn->prepare('select get_next_attendee();');
$sql_query->execute();
if (my @results = $sql_query->fetchrow_array()) {
print OUTFILE $results[0], "\n";
}
$sql_query->finish();
$conn->disconnect();
}
close(OUTFILE);
---
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com
