Обсуждение: Re: [SQL] abusing an aggregate funct
On Sun, 15 Nov 1998, M Simms wrote: > > Unfortunately, solution using sequences is not so good unless you don't > > suppose to use access to that table from some simultaneously running > > sessions. The problem is that every sessions accessing this additional > > sequence with nextval() will get its own pool of values for the > > sequence, so early or later you'll get some gaps in records numbering > > because of at least on of two reasons: > > >From the manpage of create sequence > > > Low-level locking is used to enable multiple simultaneous > calls to a generator. > > Doesnt this mean it will work for this task? > > Sure, you can access one sequence from a couple of sessions at once but it the only thing is guaranteed is UNIQUENESS of values you get from nestval() across one sequence. Although nobody can promise you that there will no gaps... There are lots of reasons, I mentioned just some of them (see my previous posting in this thread) Aleksey
On Sun, 15 Nov 1998, Postgres DBA wrote: > > > On Sun, 15 Nov 1998, M Simms wrote: > > > > Unfortunately, solution using sequences is not so good unless you don't > > > suppose to use access to that table from some simultaneously running > > > sessions. The problem is that every sessions accessing this additional > > > sequence with nextval() will get its own pool of values for the > > > sequence, so early or later you'll get some gaps in records numbering > > > because of at least on of two reasons: > > > > >From the manpage of create sequence > > > > > > Low-level locking is used to enable multiple simultaneous > > calls to a generator. > > > > Doesnt this mean it will work for this task? > > > > > > Sure, you can access one sequence from a couple of sessions at once but > it the only thing is guaranteed is UNIQUENESS of values you get from > nestval() across one sequence. Although nobody can promise you that there > will no gaps... There are lots of reasons, I mentioned just some of them > (see my previous posting in this thread) > > Aleksey > > > > My understanding is that if you do not enable the cache feature of the sequence, there will be no gaps. Of course, disabling the cache slows the transaction. Marc Zuckman marc@fallon.classyad.com _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ _ Visit The Home and Condo MarketPlace _ _ http://www.ClassyAd.com _ _ _ _ FREE basic property listings/advertisements and searches. _ _ _ _ Try our premium, yet inexpensive services for a real _ _ selling or buying edge! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
On Sun, 15 Nov 1998, Marc Howard Zuckman wrote: > On Sun, 15 Nov 1998, Postgres DBA wrote: > > > > > > > On Sun, 15 Nov 1998, M Simms wrote: > > > > > > Unfortunately, solution using sequences is not so good unless you don't > > > > suppose to use access to that table from some simultaneously running > > > > sessions. The problem is that every sessions accessing this additional > > > > sequence with nextval() will get its own pool of values for the > > > > sequence, so early or later you'll get some gaps in records numbering > > > > because of at least on of two reasons: > > > > > > >From the manpage of create sequence > > > > > > > > > Low-level locking is used to enable multiple simultaneous > > > calls to a generator. > > > > > > Doesnt this mean it will work for this task? > > > > > > > > > > Sure, you can access one sequence from a couple of sessions at once but > > it the only thing is guaranteed is UNIQUENESS of values you get from > > nestval() across one sequence. Although nobody can promise you that there > > will no gaps... There are lots of reasons, I mentioned just some of them > > (see my previous posting in this thread) > > > > Aleksey > > > > > > > > > > My understanding is that if you do not enable the cache feature > of the sequence, there will be no gaps. Of course, disabling the > cache slows the transaction. You are extremly right:-) Nevertheless, don't forget of another problem: you CAN'T call curval() before nextval(). So, every time you want to check the maximum row number in the table you should increase sequence counter using nextval() -- here is real source of gaps, and I don't know if it is possible to overcome it :_( The only idea I have for now is to check last_value field of the sequence instead of calling curval()... But again, what to do with nextval() calls during rollbacked transaction?? Aleksey.