Re: Simulating sequences

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(at)seaworthysys(dot)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
>