Re: Simulating sequences - Mailing list pgsql-general

>   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