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

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 ...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°