slow line insert - Mailing list pgsql-general

Greetings,

    I have a table with only three tuples. I've been using it as primary key container for tables, like the Sequence
frompostgresql. I'm not using sequences because my application was originally created for MSSQL 6.5. 

    My problem is very specific, I have a stored procedure for primary key generation, it checks if there is a tuple
fora given table and increase count if exists, otherwise it creates a new tuple begining with 1. 

  Here is my table definition :

bxs=# \d cnfg_gerachave
         Table "cnfg_gerachave"
   Attribute   |    Type     | Modifier
---------------+-------------+----------
 cod_cad       | integer     | not null        (it's like a department - always 1 here)
 cod_gerachave | varchar(20) | not null       (table name)
 valor         | integer     | not null    (first available primary key value for cod_gerachave table)
Index: xpkcnfg_gerachave

Here are the tuples (all) :

bxs=# select * from cnfg_gerachave;
 cod_cad |    cod_gerachave    | valor
---------+---------------------+--------
       1 | rel__impressao      |     10
       1 | rel__relatorio      |    167
       1 | serv_acaoserv       | 154406
(4 rows)

Here is the stored procedure wich updates this table :
CREATE FUNCTION gerachave(INT4, VARCHAR(20)) RETURNS INT4 AS'
DECLARE
  CAD       ALIAS FOR $1;
  tabela    ALIAS FOR $2;
  novovalor INT4;
  err_num   INT4;
BEGIN
  novovalor := 0;

  LOCK TABLE cnfg_gerachave IN ROW EXCLUSIVE MODE;

  SELECT valor INTO novovalor
    FROM cnfg_gerachave
    WHERE cod_cad = CAD AND cod_gerachave = tabela;

  IF NOT FOUND THEN
    novovalor := 1;
    INSERT INTO cnfg_gerachave VALUES (cad,tabela,novovalor);
  ELSE
    novovalor := novovalor + 1;

    UPDATE cnfg_gerachave
      SET valor = novovalor
      WHERE cod_cad = cad AND
            cod_gerachave = tabela;
  END IF;

  RETURN novovalor;

END;
'
LANGUAGE 'plpgsql';


The problem is realted with "1 | serv_acaoserv       | 154406" tuple, wich takes 5-10 secs to be updated, whether it's
calledfrom this stored procedure or from a simple UPDATE clause. All other tuples goes fine, update time is about
10-30ms.

The first time I noticed this was happening it was taking about 3-5secs to update. I tried a lot of things to solve the
problem.The last was recreate table. After the table was recreated, the update time for that tuple gone back to 50ms.
ButI noticed the time to update was slowly growing for each update. Now update time os about 5-10sec. I tested a single
"UPDATEcnfg_gerachave SET valor = 154406 WHERE cod_cad = 1 AND cod_gerachave = 'serv_acaoserv'; " and I noticed the
problemhappens outside the stored proc too. I did not tried a vacuumdb yet.  

Does anyone knows what could be happening here? How could a single line has it perfomance so higher than others? Could
LOCKTABLE cnfg_gerachave IN ROW EXCLUSIVE MODE affect performance? I would like to know too how this lock is released.
Isit after stored proc is terminated or after the commit? And if I'm not inside a transaction? I searched at
documentationand could no find answers to these questions. 

Best regards,

José Vilson de Mello de Farias.
Dígitro Tecnologia Ltda - Brazil