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

Pre-created sequences per year. Not a bad idea at all. I had not been
worrying about gaps but I checked with the user and they definitely do
not want gaps. But as long as we do not delete, is there still a risk
of gaps?

I am leaning towards a serial column maintained by postgres, using
row_number partitioned by year ordered by the serial column. No gaps
since we never delete, and no concurrency issues. I think. :)

Might also go for allocating the IDs from a separate table.

-kenneth

On Tue, Nov 22, 2011 at 3:43 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> -----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.
>
>
>