Skip site navigation (1) Skip section navigation (2)

Bug found in beta version

From: "Mark Wright" <mwright(at)pro-ns(dot)net>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Bug found in beta version
Date: 1999-06-07 16:51:29
Message-ID: 000301beb106$0115d240$c62812ac@markw_compaq (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name  : Mark Wright
Your email address : mwright(at)pro-ns(dot)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(at)pro-ns(dot)net
mark_wright(at)datacard(dot)com



pgsql-bugs by date

Next:From: Michael RichardsDate: 1999-06-10 03:36:33
Subject: Re: [BUGS] General Bug Report: prefix stringsearch doesn't return results
Previous:From: Donald JacksonDate: 1999-06-02 23:08:47
Subject: bug? Coercing float8 to text sticks number in the middle of a date

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group