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

On Tue, Nov 22, 2011 at 3:52 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Nov 22, 2011 at 2:43 PM, David Johnston <polobo@yahoo.com> wrote:
>> 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.
>
> I personally think the 'record the next to be inserted value' in a
> table somewhere is better unless you are trying to support a lot of
> concurrent operations.  Also the gap issue is more likely to come up
> than you're letting on -- a rolled back transaction is all it takes.

Yeah, using a table seems to have the advantage of being about the
only thing that would work, though I think row_number properly used
(with an invariant ordering and no deletes) also works.

-kenneth