-----Original Message----- From: Kenneth Tilton [mailto:ktilton@xxxxxxxx] Sent: Tuesday, November 22, 2011 1:52 PM To: David Johnston Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: possible race condition in trigger functions on insert operations? On Tue, Nov 22, 2011 at 12:48 PM, David Johnston <polobo@xxxxxxxxx> wrote: > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Kenneth > Tilton > Sent: Tuesday, November 22, 2011 12:26 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
