Greetings,
I have a table with only three tuples. I've been using it as primary key container
for tables, like the Sequence from postgresql. 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 for a 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 called from 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. But I noticed
the time to update was slowly growing for each update. Now update time os about
5-10sec. I tested a single "UPDATE cnfg_gerachave SET valor = 154406 WHERE cod_cad = 1
AND cod_gerachave = 'serv_acaoserv'; " and I noticed the problem happens 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 LOCK TABLE cnfg_gerachave IN ROW EXCLUSIVE
MODE affect performance? I would like to know too how this lock is released. Is it
after stored proc is terminated or after the commit? And if I'm not inside a
transaction? I searched at documentation and could no find answers to these questions.
Best regards,
José Vilson de Mello de Farias.
Dígitro Tecnologia Ltda - Brazil
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
