Re: possible race condition in trigger functions on insert operations? - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: possible race condition in trigger functions on insert operations?
Date
Msg-id 20111122175320.GA25748@tux
Whole thread Raw
In response to possible race condition in trigger functions on insert operations?  (Kenneth Tilton <ktilton@mcna.net>)
Responses Re: possible race condition in trigger functions on insert operations?
List pgsql-general
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?

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°

pgsql-general by date:

Previous
From: "David Johnston"
Date:
Subject: Re: possible race condition in trigger functions on insert operations?
Next
From: Merlin Moncure
Date:
Subject: Re: possible race condition in trigger functions on insert operations?