Re: possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> Kenneth Tilton <ktilton@mcna.net> wrote:
>
>> Bit of a trigger NOOB Q:
>>
>> I am trying to use a trigger function to automatically populate new
>> rows in a table with  a public ID of the form YYYY-NNN such that the
>> 42nd row created in 2011 would get the ID "2011-042". Each row is
>> associated via an iasid column with a row in an audit table that has a
>> timestamp column called created. This works OK, but I am worried about
>> two rows getting the same case_no if they come in at the same time
>> (whatever that means):
>>
>> declare
>>   case_yr integer;
>>   yr_case_count bigint;
>> begin
>>   select date_part('year', created) into case_yr
>>          from audit
>>          where audit.sid = NEW.iasid;
>>
>>   select count(*) into yr_case_count
>>     from fwa_case, audit
>>     where fwa_case.iasid=audit.sid
>>       and date_part('year', created) = case_yr;
>>
>>   NEW.case_no = to_char( case_yr, '9999' ) || '-' ||
>> to_char(1+yr_case_count, 'FM000');
>>   return NEW;
>> end;
>
> If i where you, i would not use such a column. What happens if you
> insert/delete a record?

We only do logical deletes. Not sure what you mean about inserts --
that is what I am working on, and they always should get the next
highest sequence number in a year.

>
> I would use something like to_char(row_number() over (...),'FM000') to
> count while select.

Unfortunately it must be within the year, not overall.

-kt