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@xxxxxxxxx> wrote: > -----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
