Simulating sequences

Greetings,

I'm getting a big performance problem and I would like to ask you what
would be the reason, but first I need to explain how it happens.

Let's suppose I can't use sequences (it seams impossible but my boss
doesn't like specific database features like this one).

For sequence simulation I had created a table called cnfg_key_generation
and each tuple holds information for one of my tables (tablename,
current_sequencial_number). Lets check :

CREATE TABLE cnfg_key_generation (
department integer NOT NULL,
table_name varchar(20) NOT NULL,
current_key integer NOT NULL,
CONSTRAINT XPKcnfg_key_generation PRIMARY KEY (department,
table_name)
);

Per example, for a table called 'my_test' I would have the following values
:
department = 1
table_name = 'my_test'
current_key = 1432

Everytime I want a new key to use in my_test primary-key I just increment
current_key value. For this job, I've created a simple stored procedure
called key_generation

CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer AS'
DECLARE
the_department ALIAS FOR $1;
the_table_name ALIAS FOR $2;
new_key_value integer;
err_num integer;
BEGIN
new_value := 0;

LOCK TABLE cnfg_key_generation IN ROW EXCLUSIVE MODE;

SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name;

IF NOT FOUND THEN
new_key_value := 1;
INSERT INTO cnfg_key_generation VALUES (the_department,the_table_name,
new_key_value);
ELSE
new_key_value := new_key_value + 1;

UPDATE cnfg_key_generation
SET current_key_value = new_key_value
WHERE department = the_department AND
table_name = the_table_name;
END IF;

RETURN new_key_value;

END;
'
LANGUAGE 'plpgsql';

Data insertion is done by the following way :

INSERT INTO my_test VALUES (key_generation(1, 'my_test'), ...other
fields...);

Ok, this works, but has a huge performance problem. I read in pg docs that
everytime I make an UPDATE, a new tuple is created and the old is marked as
invalid. For a tuple that holds sequencial key generation, everytime a new
key is generated, a new tuple is created inside cfg_key_generation. It means
after 2million key generations for same table, performance will be completly
degradated because there will be 2million of old versions of same tuple. For
instance, I have a table called 'cham_chamada' that actually holds
1.5Million of tuples. The response time for key_generation execution for
this table is more than 5seconds. In this same case if I execute
key_generation for a table that has just few values (current key = 5 per
example), response time is just some miliseconds (30 to 50ms).

I tryied to fix this problem with a VACUUM and it was completly ineffective.
After execution the problem was still there. Later, after execution of every
kind of vacuum I knew (with and without ANALYZE, especific for that table,
vacuumdb from shell ...) I gave up and tryied a COPY TO/DROP/CREATE/COPY
FROM. At this time, the performance problem was fixed.

What can I do to solve this problem without table reconstruction? Is there a
performance degradation in PostgreSQL that can't be solved? If a have a huge
table with millions of data being update constantly, what can I do to keep a
good performance if vacuum isn't 100%?

Does PostgreSQL sequences deal with these kinds performance questions? Is it
clean, fast and secury to use sequences? Maybe I still can make up my boss
mind about this subject if I get good arguments here to justify the use of
sequences.

Am I doing some stupid thing?

Best regards,

-----------------------------------------------------------------
José Vilson de Mello de Farias
Software Engineer

Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson(dot)farias(at)digitro(dot)com(dot)br
Tel.: +55 48 281 7158
ICQ 11866179