On Tue, Nov 22, 2011 at 12:48 PM, David Johnston <polobo@yahoo.com> wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Kenneth Tilton
> Sent: Tuesday, November 22, 2011 12:26 PM
> To: pgsql-general@postgresql.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.
-kt
