FAQ
Dear list,

I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.

Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)

All the best,
Jack

Search Discussions

  • Scott Marlowe at Jul 7, 2008 at 1:02 am

    On Sun, Jul 6, 2008 at 6:15 PM, Jack Brown wrote:
    Dear list,

    I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.
    This would actually be a perfectly awful sequence. :) Seriously,
    it's costly to lock the whole table, set the sequence to the last
    available value and lock it in terms of concurrency.
    Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)
    I believe it's called a "How to destroy concurrency" or something like that.
  • Berend Tober at Jul 7, 2008 at 2:04 am

    Jack Brown wrote:
    Dear list,

    I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure which combination would result in the most elegant implementation.

    Oh, and if you know the right term for what I just described, I'd be more than pleased to hear it! :-)
    This question comes up a lot. A term used in prior discussions is
    "gapless sequence".

    What would be really more interesting for discussion on this
    community forum is a detailed description or your actual use case
    and requirements.
  • Scott Marlowe at Jul 7, 2008 at 2:25 am

    On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober wrote:
    Jack Brown wrote:
    Dear list,

    I need some tips and/or pointers to relevant documentation implementing
    (what I chose to call) "a perfect sequence" i.e. a sequence that has no
    missing numbers in the sequence. I'd like it to auto increment on insert,
    and auto decrement everything bigger than its value on delete. There are
    many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure
    which combination would result in the most elegant implementation.

    Oh, and if you know the right term for what I just described, I'd be more
    than pleased to hear it! :-)
    This question comes up a lot. A term used in prior discussions is "gapless
    sequence".

    What would be really more interesting for discussion on this community forum
    is a detailed description or your actual use case and requirements.
    I will say that if you need a gapless serial numbering system it's
    still better to NOT try and do it with a pre-checked out number. For
    instance, you might have a system like a court document system that
    might have this requirement, that you hace CR-1 through CR-99999999 or
    whatever.

    In that case it's better to let the user start work, then hit CREATE
    DOCUMENT when they're ready. Then your business logic can put the
    data into the database, and if it goes in, then check out a number
    from the sequence. I.e. there are no deletes, only failed inserts. A
    system that requires you to show a number before the document has been
    "created" in the system but wants no gaps is flawed. Don't give them
    a number until they HAVE a document. reusing numbers already shown to
    a user is a recipe for a disaster. they write down the number, and
    two weeks later reference it, but it's not there.

    That's one use case. It's important here to look for the way that is
    less likely to lead to "oh crap!" moments.

    Adding gapless sequences increases the complexity. Better to let the
    complexity only live in a display layer of sorts than to rely on it
    for FK-PK type stuff.

    If there's any FK->PK relations involving these keys and they aren't
    fully cascaded, then allowing them to be renumbered is courting
    disaster. If you use a separate table for "user visible sequence
    number" and store the plain sequence, gaps and all in the db, then
    your actual core data is safer. You can recreate the user visible
    sequence number table without affecting the actual relationship of the
    data in the real data table.

    I hope I'm not rambling too much.
  • Jack Brown at Jul 7, 2008 at 11:54 am

    On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober wrote:
    This question comes up a lot. A term used in prior
    discussions is "gapless
    sequence".
    Thank you, i didn't know the term so when I'd searched the archives, i hadn't found much.
    What would be really more interesting for discussion
    on this community forum
    is a detailed description or your actual use case and
    requirements.
    it's sort of a formal document management system. the assigned gapless numbers are frozen at the end of each month. until then, an authorized user will be able to delete a document. but ...
    reusing numbers
    already shown to
    a user is a recipe for a disaster. they write down the
    number, and
    two weeks later reference it, but it's not there.
    ... when I mentioned them (management) this, they accepted that this was not a really good idea after all, thanks a lot :) i will assign numbers when closing the month, so everything will be fine.

    in the mean time i did some research for other rdbms and i guess, what i was looking for was more along the lines of e.g. the row_number() function of mssql [1]. fwiw this seems like the most efficient pagination solution, assuming, of course, the rdbms implements this functionality efficiently.

    [1]: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
  • Richard Broersma at Jul 7, 2008 at 12:23 pm

    On Mon, Jul 7, 2008 at 4:54 AM, Jack Brown wrote:

    Thank you, i didn't know the term so when I'd searched the archives, i hadn't found much.
    Here is the result of a discussion that came up a couple of years ago:

    http://www.varlena.com/GeneralBits/130.php

    I guess that german account laws require a gapless sequence for all documents.
    in the mean time i did some research for other rdbms and i guess, what i was looking for was more along the lines of e.g. the row_number() function of mssql [1]. fwiw this seems like the most efficient pagination solution, assuming, of course, the rdbms implements this functionality efficiently.

    [1]: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
    actually, this functionality is most commonly implemented in a
    reporting utility. But it can be done using SQL.



    --
    Regards,
    Richard Broersma Jr.

    Visit the Los Angles PostgreSQL Users Group (LAPUG)
    http://pugs.postgresql.org/lapug
  • André Volpato at Jul 7, 2008 at 1:10 pm

    On Mon, Jul 7, 2008 at 4:54 AM, Jack Brown wrote:

    Richard Broersma escreveu:
    in the mean time i did some research for other rdbms and i guess, what i was looking for was more along the lines of e.g. the row_number() function of mssql [1]. fwiw this seems like the most efficient pagination solution, assuming, of course, the rdbms implements this functionality efficiently.

    [1]: http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
    actually, this functionality is most commonly implemented in a
    reporting utility. But it can be done using SQL.
    Sure, there is a rownum() implementation from depesz [1] that works nice
    for reports.

    [1]
    http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

    --

    André Volpato
  • Scott Ribe at Jul 7, 2008 at 4:33 pm

    it's sort of a formal document management system. the assigned gapless numbers
    are frozen at the end of each month. until then, an authorized user will be
    able to delete a document. but ...
    Another way of dealing with this is to keep the association between the
    number and the document it was originally assigned to, and present instead
    of the document a message "document deleted", possibly with date & time &
    user. Whether you actually delete the document or not, is a detail dependent
    on the use case.


    --
    Scott Ribe
    scott_ribe@killerbytes.com
    http://www.killerbytes.com/
    (303) 722-0567 voice

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 7, '08 at 12:32a
activeJul 7, '08 at 4:33p
posts8
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2017 Grokbase