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

-----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? Yes, gaps end up being possible but that
usually is not a big issue and you will avoid any possibility of collision.

David J.