Hi:

--- Tom Lane <[EMAIL PROTECTED]> wrote:
> >      What can cause a stuck spinlock?
> In theory, that shouldn't ever happen.  Can you
> reproduce it?
> >                     regards, tom lane

   I could not reproduce it, but I'll describe how
error happen. I have a program that read a file large
file which 20,000 records and spawn a process that
execute a PLPGSQL stored function based on the content
of the file. 

    The following is a table of the SQL statement
generated:
    process 1         SELECT f1(120,  123.3);       
process 2         SELECT f1(120,   53.3);       
process 3         SELECT f1(120,   31.3);
    ..
    ..
    process n         SELECT f1(120,    2.3);

  the function f1 is basically defined as
  CREATE OR REPLACE FUNCTION f1(integer, float8)
  RETURN INTEGER
  AS'
  DECLARE
     -- some variable declaration  BEGIN
     -- Lock the table based on the first parameter
    -- of the stored function (use record level lock) 
   SELECT * 
     FROM   t1
     WHERE  field1 = $1
     FOR UPDATE;
     --a batch of SQL statements here --
  END;'
  LANGUAGE 'plpgsql';

  As you noticed the the first parameter of the called
function is the same (Due to bug on our program).
Since it performs a record level lock on the record,
the processes will queue (i.e. will execute if  only a
process relinquish its lock).   I'm guessing that the
there was just to many postmaster process trying to
concurrently trying to access the same record being
lock by a record-lock. When I execute the "top"
command in linux there are a lot of postmaster process
in the process list

   Is the spinlock error possible given that scenario?
Is this error related to the following error messages:
   fatal 2: cannot write block 3 of 16556/148333 blind
: too many open files in sysytem.

   Note : I was able to correct the above error
messages by increasing the file-max parameter in the
"sysctl.conf".

   I'm guessing that the spinlock error occurs after
there are around hundreds (or thousands) of queued
postmaster processes.

best regards,
ludwig


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to