Re: Simulating sequences

> 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).

I can't help you with the details of the performance problem, but I did
have a situation similar in that I had to maintain sequences "manually",
rather than use the PostgreSQL serial data type. The advice I got here
was to "update first, then select". Two important points I learned from
the gurus in this forum were

1) since in my case I was manipulating my simulated sequence inside a
trigger, there is an implicit transaction around the trigger associated
with the insert or update statement that fires the trigger

2) an update statement locks the record until the transaction commits.

With those items in mind, your function could become:

CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer
AS'
DECLARE
the_department ALIAS FOR $1;
the_table_name ALIAS FOR $2;
BEGIN
IF NOT EXISTS(SELECT 1 FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name) THEN
INSERT INTO cnfg_key_generation VALUES (the_department,the_table_name,0);
END IF;

UPDATE cnfg_key_generation
SET current_key_value = 1 + current_key_value
WHERE department = the_department AND
table_name = the_table_name;

RETURN (SELECT current_value INTO new_value
FROM cnfg_key_generation
WHERE the_department = department AND the_table_name = table_name);
END;

> 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...);
>

~Berend Tober