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

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?

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

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°