Re: possible race condition in trigger functions on insert operations?
От | Kenneth Tilton |
---|---|
Тема | Re: possible race condition in trigger functions on insert operations? |
Дата | |
Msg-id | CAECCA8afmNVETOCvedpaNGqjnmLE+nLk3zuE-JyzWEmnbwwp6g@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: possible race condition in trigger functions on insert operations? ("David Johnston" <polobo@yahoo.com>) |
Список | pgsql-general |
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@yahoo.com> wrote: > -----Original Message----- > From: Kenneth Tilton [mailto:ktilton@mcna.net] > Sent: Tuesday, November 22, 2011 1:52 PM > To: David Johnston > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] possible race condition in trigger functions on > insert operations? > > 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. > > ---------------------------------------------- > > 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. > > >
В списке pgsql-general по дате отправления:
Предыдущее
От: Merlin MoncureДата:
Сообщение: Re: possible race condition in trigger functions on insert operations?
Следующее
От: Kenneth TiltonДата:
Сообщение: Re: possible race condition in trigger functions on insert operations?