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.
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?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
