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.

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.

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.

Thank
--
Kathy Lo

Search Discussions

  • Richard Huxton at Nov 21, 2007 at 8:19 am

    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
  • Kathy Lo at Nov 22, 2007 at 1:32 am

    On 11/21/07, Richard Huxton 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
  • Richard Huxton at Nov 22, 2007 at 7:47 am

    Kathy Lo wrote:
    On 11/21/07, Richard Huxton wrote:

    You probably shouldn't attach any meaning to the numbers from a sequence
    - they're just guaranteed to be unique, nothing else.
    What you say here contradicts the following.
    Actually, the sequence is formed by 4-digit of year and 6-digit of
    sequence.
    So you *are* attaching significance to the number (by adding the current
    year to the front of it).
    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.
    Don't block users - have multiple sequences. If you define my_seq_2007,
    my_seq_2008, my_seq_2009 etc and then wrap access to them in a function
    you can EXTRACT() the year from the CURRENT_DATE and use that to form
    your per-year unique value.

    --
    Richard Huxton
    Archonet Ltd
  • Harald Fuchs at Nov 22, 2007 at 10:13 am
    In article <47453419.1050901@archonet.com>,
    Richard Huxton <dev@archonet.com> writes:
    Kathy Lo wrote:
    On 11/21/07, Richard Huxton wrote:

    You probably shouldn't attach any meaning to the numbers from a sequence
    - they're just guaranteed to be unique, nothing else.
    What you say here contradicts the following.
    Actually, the sequence is formed by 4-digit of year and 6-digit of
    sequence.
    So you *are* attaching significance to the number (by adding the
    current year to the front of it).
    Don't block users - have multiple sequences. If you define
    my_seq_2007, my_seq_2008, my_seq_2009 etc and then wrap access to them
    in a function you can EXTRACT() the year from the CURRENT_DATE and use
    that to form your per-year unique value.
    Since sequences don't guarantee consecutivity anyway, why not just use
    one sequence and prepend the year, e.g. by a view?
  • Scott Marlowe at Nov 21, 2007 at 2:01 pm

    On Nov 21, 2007 1:39 AM, 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.

    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.
    revoke all privs on the sequence to anyone but the user about to reset it
    reset it
    grant the options back
  • Marco Colombo at Nov 21, 2007 at 5:44 pm

    Scott Marlowe wrote:
    revoke all privs on the sequence to anyone but the user about to reset it
    reset it
    grant the options back
    Quoting the OP:
    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 remove the privs, clients will get an error, unless I'm missing
    something.

    .TM.
    --
    ____/ ____/ /
    / / / Marco Colombo
    ___/ ___ / / Technical Manager
    / / / ESI s.r.l.
    _____/ _____/ _/ Colombo@ESI.it
  • Scott Marlowe at Nov 21, 2007 at 5:53 pm

    On Nov 21, 2007 11:44 AM, Marco Colombo wrote:
    Scott Marlowe wrote:
    revoke all privs on the sequence to anyone but the user about to reset it
    reset it
    grant the options back
    Quoting the OP:
    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 remove the privs, clients will get an error, unless I'm missing
    something.
    Good point. I'm guessing if you need a way to make other users wait,
    not get an error, you'll need to use a funtion with a security definer
    that will sleep or something during that period.

    hmmmmm.
  • Tom Lane at Nov 21, 2007 at 6:23 pm

    "Scott Marlowe" <scott.marlowe@gmail.com> writes:
    Good point. I'm guessing if you need a way to make other users wait,
    not get an error, you'll need to use a funtion with a security definer
    that will sleep or something during that period.
    What you'd want is to take out an exclusive lock on the sequence.

    [ fools around... ] Hmm, we don't let you do LOCK TABLE on a sequence,
    which is perhaps overly restrictive, but you can get the same effect
    with any ALTER TABLE command that works on a sequence. For instance
    a no-op ALTER OWNER:

    Session 1:

    regression=# create sequence s;
    CREATE SEQUENCE
    regression=# begin;
    BEGIN
    regression=# alter table s owner to postgres;
    ALTER TABLE

    Session 2;

    regression=# select nextval('s');
    [ hangs ... ]

    Session 1:

    regression=# alter sequence s restart with 42;
    ALTER SEQUENCE
    regression=# commit;
    COMMIT

    Session 2:

    nextval
    ---------
    42
    (1 row)


    regards, tom lane
  • Kathy Lo at Nov 22, 2007 at 1:37 am

    On 11/22/07, Tom Lane wrote:
    "Scott Marlowe" <scott.marlowe@gmail.com> writes:
    Good point. I'm guessing if you need a way to make other users wait,
    not get an error, you'll need to use a funtion with a security definer
    that will sleep or something during that period.
    What you'd want is to take out an exclusive lock on the sequence.

    [ fools around... ] Hmm, we don't let you do LOCK TABLE on a sequence,
    which is perhaps overly restrictive, but you can get the same effect
    with any ALTER TABLE command that works on a sequence. For instance
    a no-op ALTER OWNER:

    Session 1:

    regression=# create sequence s;
    CREATE SEQUENCE
    regression=# begin;
    BEGIN
    regression=# alter table s owner to postgres;
    ALTER TABLE

    Session 2;

    regression=# select nextval('s');
    [ hangs ... ]

    Session 1:

    regression=# alter sequence s restart with 42;
    ALTER SEQUENCE
    regression=# commit;
    COMMIT

    Session 2:

    nextval
    ---------
    42
    (1 row)


    regards, tom lane

    ---------------------------(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
    Thanks for your reply.
    But, the owner of the sequence originally is postgres. Does it work?

    --
    Kathy Lo
  • Kathy Lo at Nov 22, 2007 at 1:39 am

    On 11/22/07, Scott Marlowe wrote:
    On Nov 21, 2007 11:44 AM, Marco Colombo wrote:
    Scott Marlowe wrote:
    revoke all privs on the sequence to anyone but the user about to reset it
    reset it
    grant the options back
    Quoting the OP:
    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 remove the privs, clients will get an error, unless I'm missing
    something.
    Good point. I'm guessing if you need a way to make other users wait,
    not get an error, you'll need to use a funtion with a security definer
    that will sleep or something during that period.

    hmmmmm.

    ---------------------------(end of broadcast)---------------------------
    TIP 2: Don't 'kill -9' the postmaster
    Thanks for your help!

    Would you mind to give me an example on how to write this kind of function?

    --
    Kathy Lo

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedNov 21, '07 at 7:39a
activeNov 22, '07 at 10:13a
posts11
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase