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

On Tue, Nov 22, 2011 at 2:05 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> Kenneth Tilton <ktilton(at)mcna(dot)net> wrote:
>
>> On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer
>> <akretschmer(at)spamfence(dot)net> wrote:
>> > Kenneth Tilton <ktilton(at)mcna(dot)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 --
>
> inserts that change the numbering...
>
>
>> 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.
>
> That is not a problem - over (...) with, for instance, PARTITION BY ...

Ah, OK, I did not understand what they meant by "partition" when I
checked out row_number. I'll give that a try.

First Q: does a row have a row_number at the time of insert/before?
Or will I be updating the row in the insert/after? I should know
soon... :)

-kenneth