Thread: creating "a perfect sequence" column
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 everythingbigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm notsure 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
On Sun, Jul 6, 2008 at 6:15 PM, Jack Brown <zidibik@yahoo.com> 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 everythingbigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm notsure 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.
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 everythingbigger than its value on delete. There are many mechanisms (rules, triggers, sequences, locks etc.) but I'm notsure 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.
On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober <btober@ct.metrocast.net> 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.
> 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. untilthen, 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
On Mon, Jul 7, 2008 at 4:54 AM, Jack Brown <zidibik@yahoo.com> 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
On Mon, Jul 7, 2008 at 4:54 AM, Jack Brown <zidibik@yahoo.com> 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
> 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