Re: Restart a sequence regularly - Mailing list pgsql-general

On 11/21/07, Richard Huxton <dev@archonet.com> wrote:
> Kathy Lo wrote:
> > Hi,
> >
> > I am using Postgresql 8.0.3 in Fedora Core 4.
> >
> > In my database, it contains a sequence. And, I need to alter the range
> > of this sequence and restart it to the start of the new range at
> > 00:00:00 on 1st January on every year. 5 seconds before and after that
> > time, I need to prevent users from calling nextval() to retrieve the
> > next number from this sequence.
>
> You probably shouldn't attach any meaning to the numbers from a sequence
> - they're just guaranteed to be unique, nothing else.
Yes, the sequence is just for guaranted that every users can get a
unique number. It does not relate to any tables
>
> > I can write a Perl script to alter the sequence and schedule to run
> > this script at 23:59:55 on 31st December on every year.
> >
> > But, I don't know how to lock the sequence to prevent others from
> > accessing this sequence to get next number and Postgresql does not
> > support to lock a sequence.
>
> That would defeat the point of a sequence.
>
> > How can I prevent others from accessing the sequence, like locking a
> > table? That means, when others want to access the sequence between
> > 31-Dec 23:59:55 and 1-Jan 00:00:05, they are waiting instead of
> > getting an error.
>
> If you just want a new range of numbers to start 1st Jan, you could wrap
> nextval() in another function that adds a base-value in depending on the
> current date. Something like:
>
> SELECT EXTRACT('YEAR' FROM CURRENT_DATE)*1000 + nextval(...)
>
> If you really need to lock the sequence again, wrap it in another
> function and have that function sleep for the required changeover period.
>
> Of the top of my head it sounds awkward though - can you explain more
> about how you're using this?
Actually, the sequence is formed by 4-digit of year and 6-digit of
sequence. So, it is required to change and restart the range of
sequence at the beginning of every year. For example, at the beginning
of 2008, the sequence should be changed to the range of 2008000001 -
2008999999 and restart at 2008000001. In the time of changing the
sequence, it does not allow any users to get the unique number from
this sequence. However, our staff don't want to do it manually because
it is difficult for them to make sure no one accessing the sequence
and our service cannot stop at that time. Therefore, I need to let the
users to wait in the period of changing the sequence.
>
> --
>   Richard Huxton
>   Archonet Ltd
>


--
Kathy Lo