Re: 7.3 LOCK TABLE problem - Mailing list pgsql-general

The queue table is simple:

CREATE TABLE queue (id serial8, data text) WITH OIDS;

The program goes something like this:

root@dbs:~# cat fifo
#!/usr/bin/perl

use PgSQL;
use PgSQL::Cursor;

my $rs;
my $dbh = new PgSQL(DBName => 'test', User => 'postgres', Password => '', Host
=> 'localhost');

while() {
  my $mysql_update = <<EOS;
    BEGIN;
    LOCK TABLE queue IN EXCLUSIVE MODE;
    UPDATE queue
      set status=$$
    WHERE id = (SELECT min(id)
       FROM queue
       WHERE status=0);
    COMMIT;
EOS

  my $rs_update = $dbh->do($mysql_update);

  #
  # Attempt to get a row from the queue
  #
  my $mysql_get = <<EOS;
    SELECT id, data
    FROM queue
    WHERE status=$procid;
EOS
  my $rs_get = $dbh->do($mysql_get);

  #
  # We've claimed a record in the queue.
  #
  while ($row_get = $rs_get->fetch) {
    my @row = @$row_get if $row_get;
    my $id = @row[0];
    my $data = @row[1];
    print "Got Record $data\n";
    sleep(1);
    my $mysql_complete = <<EOS;
UPDATE queue SET status='1' where id=$id;
EOS
    my $rs_complete = $dbh->do($mysql_complete);
  }
  $rs_get->finish;
  sleep(1);
}


Any one of the instances of this program would mark an unprocessed tuple
(status=0) in the queue with its process ID, and then pull the data out,
process the data, then mark the item complete (status=1). By locking the table,
concurrent selects wouldn't be blocked, but updates would be blocked (LOCK
TABLE queue IN EXCLUSIVE MODE;) This worked flawlessly in 7.2.1 and 7.2.3.

I compiled 7.3.1, dumped the 7.2 data with the 7.3.1 pg_dumpall, reimported the
data, and ran this program to test. When it executed, multiple seperate
instances of the program would somehow mark and select the same row. It was as
though there were no locks on the table.

I wish that you HAD changed something in table locking! :) That way we'd have
somewhere to start looking! It would be just my luck that I've overlooked a
small detail in the upgrade process, yes?

CG

--- Neil Conway <neilc@samurai.com> wrote:
> On Mon, 2003-01-27 at 11:20, Chris Gamache wrote:
> > Tom's suggested solution worked GREAT in 7.2.x ... I tried taking the
> plunge
> > into 7.3.1 tonight. In 7.3.1, when my FIFO queue program goes to grab a
> row,
> > TWO processes grab the same row, almost without fail. I even changed my
> locking
> > statement to the dreaded
>
> Without knowing anything about "your FIFO queue program" or the queries
> it is executing, it's kind of difficult to help you out. Exactly what
> queries are being executed, in what order, and how does the result
> differ from what you expect (and 7.2's behavior)?
>
> AFAIK there weren't any significant changes to table-level locks in 7.3.
>
> Cheers,
>
> Neil
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com