Re: Simulating sequences - Mailing list pgsql-general

wouldn't a better situation be ADDING a record that is one higher, and then doing a select MAX()?

The different triggers could do delete on the old records.

btober@seaworthysys.com wrote:

>>  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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>