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

-----Original Message-----
From: Kenneth Tilton [mailto:ktilton(at)mcna(dot)net]
Sent: Tuesday, November 22, 2011 1:52 PM
To: David Johnston
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] possible race condition in trigger functions on
insert operations?

On Tue, Nov 22, 2011 at 12:48 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Kenneth
> Tilton
> Sent: Tuesday, November 22, 2011 12:26 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] possible race condition in trigger functions on
> insert operations?
>
> 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;
>
> Do I have to worry about this, or does ACID bail me out? If the
> former, what do I do? I am thinking first put a uniqueness constraint
> on the column and then figure out how to do retries in a trigger function.
>
> kenneth
> --------------------------------------------------------
>
> Why can't you just use a sequence?

The sequence has to be within the year. Someone suggested a cron job to
reset the sequence at the beginning of the year but I find that alternative
unappealing for some reason.

----------------------------------------------

Just create a single sequence for each year and then call the proper one
on-the-fly. You can create multiple sequences in advance and possible even
auto-create the sequence the first time one is attempted to be used in a
given year. If you can live with possible (but probably unlikely) gaps in
the sequence then all the concurrency will be handled for you and you can
focus on writing a function that, given a year, will return the proper
value.

David J.