Re: Stuck Spinlock Error Message

Hi:

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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