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

From: Kenneth Tilton <ktilton(at)mcna(dot)net>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: possible race condition in trigger functions on insert operations?
Date: 2011-11-22 19:02:41
Message-ID: CAECCA8ZWD_VtfPzcMYcz=SyuR=GX8mEfEDtD4QAnSGUqk1cNkA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thanks, that's perfect.

-kenneth

On Tue, Nov 22, 2011 at 12:53 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Nov 22, 2011 at 11:25 AM, 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;
>>
>> 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.
>
> ACID does not bail you out -- you've put no synchonization in to
> prevent to concurrent counts coming at roughly the same time and
> getting the same answer.  A uniqueness constraint is definitely a good
> idea.  In terms of doing a gapless sequence generally, see here:
> http://www.varlena.com/GeneralBits/130.php.  Basically the general
> idea is to keep a counter field somewhere that you lock and update.
>
> merlin
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2011-11-22 19:05:40 Re: possible race condition in trigger functions on insert operations?
Previous Message Kenneth Tilton 2011-11-22 18:56:04 Re: possible race condition in trigger functions on insert operations?