pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedsequence question
27+ messages / 15 participants
[nested] [flat]
* sequence question
@ 2002-02-05 02:11 Timothy Reaves <treaves@silverfields.com>
0 siblings, 1 reply; 27+ messages in thread
From: Timothy Reaves @ 2002-02-05 02:11 UTC (permalink / raw)
To: pgsql-general
What is the max size of a sequence? Is it a bigint? I can't seem to
find this info.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: sequence question
@ 2002-02-05 05:04 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Timothy Reaves <treaves@silverfields.com>
0 siblings, 0 replies; 27+ messages in thread
From: Stephan Szabo @ 2002-02-05 05:04 UTC (permalink / raw)
To: Timothy Reaves <treaves@silverfields.com>; +Cc: pgsql-general
On Mon, 4 Feb 2002, Timothy Reaves wrote:
> What is the max size of a sequence? Is it a bigint? I can't seem to
> find this info.
In 7.1 and early it's int sized, in 7.2 it's bigint.
^ permalink raw reply [nested|flat] 27+ messages in thread
* sequence question
@ 2003-01-10 03:16 Williams, Travis L, NPONS <tlw@att.com>
0 siblings, 1 reply; 27+ messages in thread
From: Williams, Travis L, NPONS @ 2003-01-10 03:16 UTC (permalink / raw)
To: pgsql-general
Can you manually update the last_value in a sequence? I moved some data around and manually put in some of the sequence numbers.. I fixed the problem.. but was wondering for furture reference.
Travis
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: sequence question
@ 2003-01-10 04:35 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Williams, Travis L, NPONS <tlw@att.com>
0 siblings, 0 replies; 27+ messages in thread
From: Stephan Szabo @ 2003-01-10 04:35 UTC (permalink / raw)
To: Williams, Travis L, NPONS <tlw@att.com>; +Cc: pgsql-general
On Thu, 9 Jan 2003, Williams, Travis L, NPONS wrote:
> Can you manually update the last_value in a sequence? I moved some
> data around and manually put in some of the sequence numbers.. I fixed
> the problem.. but was wondering for furture reference.
You can use setval() on the sequence to change the next value generated.
^ permalink raw reply [nested|flat] 27+ messages in thread
* sequence question
@ 2003-01-27 20:06 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 1 reply; 27+ messages in thread
From: Marie G. Tuite @ 2003-01-27 20:06 UTC (permalink / raw)
To: pgsql-general
Hey there,
Does anybody have a nice way to identify what table.column is using a
particular sequence on a database-wide basis?
--Thanks.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: sequence question
@ 2003-01-27 20:07 Dan Langille <dan@langille.org>
parent: Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 1 reply; 27+ messages in thread
From: Dan Langille @ 2003-01-27 20:07 UTC (permalink / raw)
To: Marie G. Tuite <marie.tuite@edisonaffiliates.com>; pgsql-general
On 27 Jan 2003 at 14:06, Marie G. Tuite wrote:
> Does anybody have a nice way to identify what table.column is using a
> particular sequence on a database-wide basis?
This makes me think you have a method you think is not nice. What is
that method?
--
Dan Langille : http://www.langille.org/
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: sequence question
@ 2003-01-27 20:17 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 27+ messages in thread
From: Marie G. Tuite @ 2003-01-27 20:17 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-general
A perl script on a schema dump, but I was hoping to script a procedure or
something like that.
> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: Monday, January 27, 2003 2:08 PM
> To: Marie G. Tuite; Pgsql-General@Postgresql. Org
> Subject: Re: [GENERAL] sequence question
>
>
> On 27 Jan 2003 at 14:06, Marie G. Tuite wrote:
>
> > Does anybody have a nice way to identify what table.column is using a
> > particular sequence on a database-wide basis?
>
> This makes me think you have a method you think is not nice. What is
> that method?
> --
> Dan Langille : http://www.langille.org/
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Sequence question.
@ 2003-12-17 01:12 Anthony Best <abest@digitalflex.net>
0 siblings, 1 reply; 27+ messages in thread
From: Anthony Best @ 2003-12-17 01:12 UTC (permalink / raw)
To: pgsql-general
I'm working on an idea that uses sequences.
I'm going to create a table like this:
id serial,
sequence int,
keyword varchar(32),
text text
for every keyword there will be a uniq sequence for it eg:
id, sequence, keyword
1, 1, foo, ver1
2, 1, bar, bar ver1
3, 2, foo, ver2
4, 2, bar, bar ver2
etc...
I could have one sequence for all keyword which would be 1,3, etc... I
would be prefer to have them in sequence. I'm sure someone has ran into
this before, any ideas?
Anthony.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question.
@ 2003-12-17 07:57 John Sidney-Woollett <johnsw@wardbrook.com>
parent: Anthony Best <abest@digitalflex.net>
0 siblings, 1 reply; 27+ messages in thread
From: John Sidney-Woollett @ 2003-12-17 07:57 UTC (permalink / raw)
To: Anthony Best <abest@digitalflex.net>; +Cc: pgsql-general
How about using two tables; one to hold the keyword and its (last
allocated) sequence value, and the second to store your data as below.
create table Keyword (
keyword varchar(32),
sequence integer,
primary key(keyword)
)
create table Data (
id serial,
sequence int,
keyword varchar(32),
text text
)
Add a trigger to the Data table for Insert so that it joins to the
(parent) keyword table and increments the keyword.sequence value, and
places that into the Data.sequence value.
You will get 'holes' in the keyword sequencing when you delete data from
the Data table. If that's a problem then you will need an alternative
design.
Hope that helps.
John Sidney-Woollett
Anthony Best said:
> I'm working on an idea that uses sequences.
>
> I'm going to create a table like this:
>
> id serial,
> sequence int,
> keyword varchar(32),
> text text
>
> for every keyword there will be a uniq sequence for it eg:
>
> id, sequence, keyword
> 1, 1, foo, ver1
> 2, 1, bar, bar ver1
> 3, 2, foo, ver2
> 4, 2, bar, bar ver2
> etc...
>
> I could have one sequence for all keyword which would be 1,3, etc... I
> would be prefer to have them in sequence. I'm sure someone has ran into
> this before, any ideas?
>
> Anthony.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2003-12-17 08:08 John Sidney-Woollett <johnsw@wardbrook.com>
parent: John Sidney-Woollett <johnsw@wardbrook.com>
0 siblings, 0 replies; 27+ messages in thread
From: John Sidney-Woollett @ 2003-12-17 08:08 UTC (permalink / raw)
To: johnsw@wardbrook.com; +Cc: Anthony Best <abest@digitalflex.net>; pgsql-general
Sorry I should have added that the trigger needs to create a new keyword
record if the join in the trigger fails to locate the keyword in the
keyword table.
Hopefully you can create the trigger yourself.
The keyword table is effectively a distinct list of all keywords inserted
into the data table with the associated last allocated sequence number for
the keyword.
John
John Sidney-Woollett said:
> How about using two tables; one to hold the keyword and its (last
> allocated) sequence value, and the second to store your data as below.
>
> create table Keyword (
> keyword varchar(32),
> sequence integer,
> primary key(keyword)
> )
>
> create table Data (
> id serial,
> sequence int,
> keyword varchar(32),
> text text
> )
>
> Add a trigger to the Data table for Insert so that it joins to the
> (parent) keyword table and increments the keyword.sequence value, and
> places that into the Data.sequence value.
>
> You will get 'holes' in the keyword sequencing when you delete data from
> the Data table. If that's a problem then you will need an alternative
> design.
>
> Hope that helps.
>
> John Sidney-Woollett
>
> Anthony Best said:
>> I'm working on an idea that uses sequences.
>>
>> I'm going to create a table like this:
>>
>> id serial,
>> sequence int,
>> keyword varchar(32),
>> text text
>>
>> for every keyword there will be a uniq sequence for it eg:
>>
>> id, sequence, keyword
>> 1, 1, foo, ver1
>> 2, 1, bar, bar ver1
>> 3, 2, foo, ver2
>> 4, 2, bar, bar ver2
>> etc...
>>
>> I could have one sequence for all keyword which would be 1,3, etc... I
>> would be prefer to have them in sequence. I'm sure someone has ran into
>> this before, any ideas?
>>
>> Anthony.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Sequence question
@ 2004-08-04 23:20 Oscar Tuscon <obtuse@bmwe30.net>
0 siblings, 0 replies; 27+ messages in thread
From: Oscar Tuscon @ 2004-08-04 23:20 UTC (permalink / raw)
To: pgsql-general
I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once.
Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25% improvement in average time.
SO... is the following approach safe? That is, will this be atomic, or is there a possibility that another connection could squeeze in a select nextval() between the select nextval() and the setval below?
If it's safe I'd do this and take the sequences as the new currval - #I asked for (1500 or whatever).
mydb=# select setval('my_id_seq', (select nextval('my_id_seq')+1500));
Thanks
Oscar T
_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net
^ permalink raw reply [nested|flat] 27+ messages in thread
* Sequence Question
@ 2004-08-05 04:19 Oscar Tuscon <obtuse@bmwe30.net>
0 siblings, 2 replies; 27+ messages in thread
From: Oscar Tuscon @ 2004-08-05 04:19 UTC (permalink / raw)
To: pgsql-general
I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once.
Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25% improvement in average time.
SO... is the following approach safe? That is, will this be atomic, or is there a possibility that another connection could squeeze in a select nextval() between the select nextval() and the setval below?
If it's safe I'd do this and take the sequences as the new currval - #I asked for (1500 or whatever).
mydb=# select setval('my_id_seq', (select nextval('my_id_seq')+1500));
Thanks
Oscar
_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence Question
@ 2004-08-05 06:54 Prabu Subroto <prabu_subroto@yahoo.com>
parent: Oscar Tuscon <obtuse@bmwe30.net>
1 sibling, 0 replies; 27+ messages in thread
From: Prabu Subroto @ 2004-08-05 06:54 UTC (permalink / raw)
To: pgsql-general
kv=# create sequence sales_salesid_seq;
CREATE SEQUENCE
kv=# select setval('sales_salesid_seq', (select
max(salesid) from sales)+1);
setval
--------
16
(1 row)
kv=# alter table sales alter column salesid set
default nextval('sales_serialid_
seq');
ALTER TABLE
Oscar Tuscon <obtuse@bmwe30.net> wrote:
> I'm looking at ways to get batches of sequence
> values s faster. I don't want to set cache or
> increment to a large number for a variety of
> reasons. I need to grab id's in batches of varying
> numbers at various times, from 1 to several thousand
> at once.
> Doing it 1 at a time works, but more time goes into
> it than I'd like. I tried setting cache_value high
> but the database roundtrips were eating more time
> that I wanted to see; I only saw a 25% improvement
> in average time.
>
> SO... is the following approach safe? That is, will
> this be atomic, or is there a possibility that
> another connection could squeeze in a select
> nextval() between the select nextval() and the
> setval below?
> If it's safe I'd do this and take the sequences as
> the new currval - #I asked for (1500 or whatever).
>
> mydb=# select setval('my_id_seq', (select
> nextval('my_id_seq')+1500));
>
> Thanks
> Oscar
>
>
>
_____________________________________________________________
> The BMW E30 community on the web--->
> http://www.bmwe30.net
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence Question
@ 2004-08-05 15:07 Greg Stark <gsstark@mit.edu>
parent: Oscar Tuscon <obtuse@bmwe30.net>
1 sibling, 0 replies; 27+ messages in thread
From: Greg Stark @ 2004-08-05 15:07 UTC (permalink / raw)
To: pgsql-general
Oscar Tuscon <obtuse@bmwe30.net> writes:
> I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once.
> Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25% improvement in average time.
>
> SO... is the following approach safe? That is, will this be atomic, or is there a possibility that another connection could squeeze in a select nextval() between the select nextval() and the setval below?
> If it's safe I'd do this and take the sequences as the new currval - #I asked for (1500 or whatever).
>
> mydb=# select setval('my_id_seq', (select nextval('my_id_seq')+1500));
I'm not sure but I don't think that's safe since nextval doesn't lock the
sequence until the setval occurs. Though it might be unlikely to actually
occur in real life.
You could create a table with as many entries as you will ever need and then
select nextval() from that table and read all the records you get. That will
avoid the many round trips but it will still be slow since you will still have
to move all those individual values to your client.
Perhaps you should give up on the idea of using sequences at all.
If you have a table with one record (or one record per similar application).
You could "SELECT n FROM counters FOR UPDATE" the value in the record, then
"UPDATE counters SET n = n+?". It would serialize your accesses which would be
bad if you had lots of clients doing small increments, but if you have few
clients doing large increments it shouldn't be a problem.
Do make sure to vacuum this table frequently though.
--
greg
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence Question
@ 2004-08-05 19:50 Oscar Tuscon <obtuse@bmwe30.net>
0 siblings, 0 replies; 27+ messages in thread
From: Oscar Tuscon @ 2004-08-05 19:50 UTC (permalink / raw)
To: pgsql-general
>>>>
I'm not sure but I don't think that's safe since nextval doesn't lock the
sequence until the setval occurs. Though it might be unlikely to actually
occur in real life.
You could create a table with as many entries as you will ever need and then
select nextval() from that table and read all the records you get. That will
avoid the many round trips but it will still be slow since you will still have
to move all those individual values to your client.
Perhaps you should give up on the idea of using sequences at all.
If you have a table with one record (or one record per similar application).
You could "SELECT n FROM counters FOR UPDATE" the value in the record, then
"UPDATE counters SET n = n+?". It would serialize your accesses which would be
bad if you had lots of clients doing small increments, but if you have few
clients doing large increments it shouldn't be a problem.
Do make sure to vacuum this table frequently though.
--
greg
<<<<
Thanks, I figured that, but was hoping otherwise. I realize that the timing would make it unlikely, but unfortunately I need 100% guaranteed. I have an alternative in that I control the accessing clients (my app) and can apply a lock to prevent it from happening.
I found the average select nextval() call was taking 2ms, which seems a bit slow to me. Throw in the fsync I suppose and that'd explain it.
Interestingly, in the tests I ran the minimum select nextval() was 400us, and the max was 35ms, with an average of 2ms. This was on a DL380 dual 2.4G processors, 2.5G RAM, 5x10k SCSI drives, and no load - pretty much idle (well, a processes checking for entries in a command table 10 times per second).
Oscar
_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net
^ permalink raw reply [nested|flat] 27+ messages in thread
* Sequence question
@ 2004-10-18 23:16 Eric E <whalesuit@bonbon.net>
0 siblings, 1 reply; 27+ messages in thread
From: Eric E @ 2004-10-18 23:16 UTC (permalink / raw)
To: pgsql-general
Hi,
I have a question about sequences. I need a field to have values with
no holes in the sequence. However, the values do not need to be in order.
My users will draw a number or numbers from the sequence and write to
the field. Sometimes, however, these sequence numbers will be discarded
(after a transaction is complete), and thus available for use. During
the transaction, however, any drawn numbers need to be unavailable.
I would like the next user who draws a number to draw the lowest number
she can, starting with the holes in the sequence.
This continuous sequence is absolutely required by our company, as the
fact that the sequence has no holes is used to check for much more
serious problems.
So my question is:
what's the most effective way to get the next available number?
My present method is to do a query that finds the first and last number
in each of the holes, step through those holes, and then start
generating new numbers. Unfortunately, this involves doing a table scan
each time - before I generate the number, and does not produce the
transaction-safety I want.
Does anyone have any better ideas? Places I should look?
Thanks,
Eric
^ permalink raw reply [nested|flat] 27+ messages in thread
* Sequence question
@ 2004-10-19 15:19 Eric E <whalesuit@bonbon.net>
0 siblings, 1 reply; 27+ messages in thread
From: Eric E @ 2004-10-19 15:19 UTC (permalink / raw)
To: pgsql-general
Hi,
I have a question about sequences. I need a field to have values
with no holes in the sequence. However, the values do not need to be in
order.
My users will draw a number or numbers from the sequence and write to
the field. Sometimes, however, these sequence numbers will be discarded
(after a transaction is complete), and thus available for use. During
the transaction, however, any drawn numbers need to be unavailable.
I would like the next user who draws a number to draw the lowest number
she can, starting with the holes in the sequence.
This continuous sequence is absolutely required by our company, as the
fact that the sequence has no holes is used to check for much more
serious problems.
So my question is:
what's the most effective way to get the next available number?
My present method is to do a query that finds the first and last number
in each of the holes, step through those holes, and then start
generating new numbers. Unfortunately, this involves doing a table scan
each time - before I generate the number, and does not produce the
transaction-safety I want.
Does anyone have any better ideas? Places I should look?
Thanks,
Eric
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 15:16 David Ecker <david@familie-ecker.net>
0 siblings, 0 replies; 27+ messages in thread
From: David Ecker @ 2004-10-20 15:16 UTC (permalink / raw)
To: pgsql-general
Far from being a perfect idea but a faster solution than stepping through
all holes:
1) Create a second table containing only one field of type of your key.
2) When you delete an entry place the delete key value in your second table
3) If you insert a new entry into your old table and your new table contains
a value, take the minimum value in the new table as your new key and delete
that entry from the new table. If the new table is empty just use the
sequence to get the new key value.
Hope that helps
David Ecker
Eric E wrote:
> Hi,
> I have a question about sequences. I need a field to have values
> with no holes in the sequence. However, the values do not need to be in
> order.
>
> My users will draw a number or numbers from the sequence and write to
> the field. Sometimes, however, these sequence numbers will be discarded
> (after a transaction is complete), and thus available for use. During
> the transaction, however, any drawn numbers need to be unavailable.
> I would like the next user who draws a number to draw the lowest number
> she can, starting with the holes in the sequence.
>
> This continuous sequence is absolutely required by our company, as the
> fact that the sequence has no holes is used to check for much more
> serious problems.
>
> So my question is:
> what's the most effective way to get the next available number?
>
> My present method is to do a query that finds the first and last number
> in each of the holes, step through those holes, and then start
> generating new numbers. Unfortunately, this involves doing a table scan
> each time - before I generate the number, and does not produce the
> transaction-safety I want.
>
> Does anyone have any better ideas? Places I should look?
>
> Thanks,
>
> Eric
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 15:35 Tino Wildenhain <tino@wildenhain.de>
parent: Eric E <whalesuit@bonbon.net>
0 siblings, 1 reply; 27+ messages in thread
From: Tino Wildenhain @ 2004-10-20 15:35 UTC (permalink / raw)
To: Eric E <whalesuit@bonbon.net>; +Cc: pgsql-general
Hi,
On Tue, 2004-10-19 at 01:16, Eric E wrote:
> Hi,
> I have a question about sequences. I need a field to have values with
> no holes in the sequence. However, the values do not need to be in order.
>
> My users will draw a number or numbers from the sequence and write to
> the field. Sometimes, however, these sequence numbers will be discarded
> (after a transaction is complete), and thus available for use. During
> the transaction, however, any drawn numbers need to be unavailable.
> I would like the next user who draws a number to draw the lowest number
> she can, starting with the holes in the sequence.
>
> This continuous sequence is absolutely required by our company, as the
> fact that the sequence has no holes is used to check for much more
> serious problems.
I would recheck this requirement. What should actually be achieved
with the check for no holes in the numbering?
Remember you can always enumerate using a set returning function
or by means of a temporary sequence for a query.
> So my question is:
> what's the most effective way to get the next available number?
There is none.
> My present method is to do a query that finds the first and last number
> in each of the holes, step through those holes, and then start
> generating new numbers. Unfortunately, this involves doing a table scan
> each time - before I generate the number, and does not produce the
> transaction-safety I want.
You cannot eat the cake and keep it - either you have holes
or you have transaction security or you have bad performance
by locking the whole table on insert.
Regards
Tino
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 15:57 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Eric E <whalesuit@bonbon.net>
0 siblings, 2 replies; 27+ messages in thread
From: Andrew Sullivan @ 2004-10-20 15:57 UTC (permalink / raw)
To: pgsql-general
On Tue, Oct 19, 2004 at 11:19:05AM -0400, Eric E wrote:
> My users will draw a number or numbers from the sequence and write to
> the field. Sometimes, however, these sequence numbers will be discarded
> (after a transaction is complete), and thus available for use. During
> the transaction, however, any drawn numbers need to be unavailable.
> I would like the next user who draws a number to draw the lowest number
> she can, starting with the holes in the sequence.
There are two ways I've seen to do this. One is the low-concurrency
way. Another is a sort of clever approach that isn't theoretically
perfect, but which provides slightly better concurrency.
The low-concurrency approach is pretty much what you'd expect: keep
the value in a table which is locked by each transaction which is
incrementing it, and complete the incrementing in the transaction
scope. That way, if it rolls back, the value hasn't been
incremented, and is ready for the next user. The problem, of course,
is that this forces every transaction to stand in line.
An alternative approach I've heard is to pre-allocate numbers from a
sequence into a table:
create table seq_allocation (
serialno int8 not null unique,
grant_status int
constraint status_limiter check (grant_status in
(1,2,3)) );
The idea is that a grant_status of 1 means the serial number is
unallocated, a grant_status of 2 means it's pending, and 3 means it's
granted. When you start, in one transaction you pick the next
available serialno with a status of 1. Then you update that row to
set it to 2 (make sure you use "where grant_status = 1" to avoid a
race condition), and then commit. Now you have your serial number.
Use it, and then at the end of your transaction where you are
committing, set the grant_status to 3, so you know it's really used.
Now, how do you handle the cases where either the transaction fails
so you can't set it to 3? Simple: your client captures errors and
then sets the value back to 1 later. For client errors, you need yet
another process which will go around periodically and check for
grant_status = 2, and make sure nobody's actually in the middle of
trying to use them. (You could refine the seq_allocation table by
storing the pid of the allocating back end. Then your maintenance
script could look for such a back end while cleaning up.)
The savepoints features of 8.0 will make some of this even easier for
you.
Note that this second method is not completely bulletproof, but it
might be good enough for the cases you want. I have a feeling,
however, that you're creating a new problem for yourself by not being
able to skip sequence values. My bet is that you actually need to
find a better way to solve the "other serious problems" you have
rather than banging on sequences to get them to fit your intended
use.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 17:00 Alvaro Herrera <alvherre@dcc.uchile.cl>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 27+ messages in thread
From: Alvaro Herrera @ 2004-10-20 17:00 UTC (permalink / raw)
To: pgsql-general
On Wed, Oct 20, 2004 at 11:57:42AM -0400, Andrew Sullivan wrote:
> Now, how do you handle the cases where either the transaction fails
> so you can't set it to 3? Simple: your client captures errors and
> then sets the value back to 1 later.
Has anyone read "the Sagas paper" by Garcia-Molina? They present a way
to handle "extended transaction models", trying to cope sort-of
automatically with this kind of situations. More generally, AFAIU the
idea is to have multi-transaction recoverability and rollback-ability.
It seems interesting.
http://portal.acm.org/citation.cfm?doid=38713.38742
I have only skimmed through it, but it sounds somewhat interesting.
I'd love to know what do people think of this.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Los dioses no protegen a los insensatos. Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 17:52 Eric E <whalesuit@bonbon.net>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 2 replies; 27+ messages in thread
From: Eric E @ 2004-10-20 17:52 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-general
Hi Tino,
Many thanks for helping me.
I know that the sequence issue is a troubling one for many on the list.
Perhaps if I explain the need for a continuous sequence I can circumvent
some of that:
This database is for a laboratory, and the numbers in sequence
determine storage locations for a sample. Having a physical space in
our storage boxes tells us something has happened - the sample was used
up, broken, in use, etc - and account for that missing sample. If the
generated sequence has holes in it, we cannot tell if a sample is
properly not in the rack, or if that hole was simply generated by the
database. Allowing empties would also fill up limited box space with
spaces generated by the database.
If anyone has a brilliant idea for how a non-continuous sequence could
address the needs, I'd be delighted to hear it, but short of that I
think I have to keep this requirement.
One thought I had, and I'd love to hear what people think of this, is to
build a table of storage location numbers that are available for use.
That way the search for new numbers could be pushed off until some
convenient moment well after the user requests them.
Thanks again for any ideas.
Cheers,
Eric
Tino Wildenhain wrote:
>Hi,
>
>On Tue, 2004-10-19 at 01:16, Eric E wrote:
>
>
>>Hi,
>> I have a question about sequences. I need a field to have values with
>>no holes in the sequence. However, the values do not need to be in order.
>>
>>My users will draw a number or numbers from the sequence and write to
>>the field. Sometimes, however, these sequence numbers will be discarded
>>(after a transaction is complete), and thus available for use. During
>>the transaction, however, any drawn numbers need to be unavailable.
>>I would like the next user who draws a number to draw the lowest number
>>she can, starting with the holes in the sequence.
>>
>>This continuous sequence is absolutely required by our company, as the
>>fact that the sequence has no holes is used to check for much more
>>serious problems.
>>
>>
>
>I would recheck this requirement. What should actually be achieved
>with the check for no holes in the numbering?
>Remember you can always enumerate using a set returning function
>or by means of a temporary sequence for a query.
>
>
>
>>So my question is:
>>what's the most effective way to get the next available number?
>>
>>
>
>There is none.
>
>
>
>>My present method is to do a query that finds the first and last number
>>in each of the holes, step through those holes, and then start
>>generating new numbers. Unfortunately, this involves doing a table scan
>>each time - before I generate the number, and does not produce the
>>transaction-safety I want.
>>
>>
>
>You cannot eat the cake and keep it - either you have holes
>or you have transaction security or you have bad performance
>by locking the whole table on insert.
>
>Regards
>Tino
>
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 18:29 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Eric E <whalesuit@bonbon.net>
1 sibling, 0 replies; 27+ messages in thread
From: Andrew Sullivan @ 2004-10-20 18:29 UTC (permalink / raw)
To: pgsql-general
On Wed, Oct 20, 2004 at 01:52:59PM -0400, Eric E wrote:
> One thought I had, and I'd love to hear what people think of this, is to
> build a table of storage location numbers that are available for use.
> That way the search for new numbers could be pushed off until some
> convenient moment well after the user requests them.
That very application is how I heard the idea for the "second method"
I sent in another email. In the case I was thinking of, someone used
it for room allocation. It worked pretty well, as long as you can
tolerate occasional periods where you _do_ have gaps.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 18:59 Eric E <whalesuit@bonbon.net>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 27+ messages in thread
From: Eric E @ 2004-10-20 18:59 UTC (permalink / raw)
To: pgsql-general
Hi Andrew,
I had basically started working on an idea like the second approach,
but had not been able to put the status element so clearly. I really
like the statuses of available, pending, and granted.
There's one more twist I think I can use to optimize this: once a number
is assigned, it cannot be reassigned. So I think I can add:
- have the sequence preallocation table hold only numbers with status
being available or pending, i.e., delete numbers once they have been
allocated. This leaves on two possible statuses: available and pending.
- push off getting new numbers into the preallocation table with a
full-table search until convenient times
I also liked your point about the atomicity of :
get number, change status to pending, commit
After that one can proceed with writing the number into my data table.
My thought was that the you could set the status ussing sessionID. That
way a server-side job could look for expired sessions and remark those
numbers available.
Any thoughts?
This is point is definitely important
> I have a feeling,
> however, that you're creating a new problem for yourself by not being
> able to skip sequence values. My bet is that you actually need to
> find a better way to solve the "other serious problems" you have
> rather than banging on sequences to get them to fit your intended
> use.
I just haven't really seen anyway around the need to use all of our
storage rows that doens't involve a complicated mapping to boxes.
Thanks,
Eric
Andrew Sullivan wrote:
> On Tue, Oct 19, 2004 at 11:19:05AM -0400, Eric E wrote:
>
>
>>My users will draw a number or numbers from the sequence and write to
>>the field. Sometimes, however, these sequence numbers will be discarded
>>(after a transaction is complete), and thus available for use. During
>>the transaction, however, any drawn numbers need to be unavailable.
>>I would like the next user who draws a number to draw the lowest number
>>she can, starting with the holes in the sequence.
>
>
> There are two ways I've seen to do this. One is the low-concurrency
> way. Another is a sort of clever approach that isn't theoretically
> perfect, but which provides slightly better concurrency.
>
> The low-concurrency approach is pretty much what you'd expect: keep
> the value in a table which is locked by each transaction which is
> incrementing it, and complete the incrementing in the transaction
> scope. That way, if it rolls back, the value hasn't been
> incremented, and is ready for the next user. The problem, of course,
> is that this forces every transaction to stand in line.
>
> An alternative approach I've heard is to pre-allocate numbers from a
> sequence into a table:
>
> create table seq_allocation (
> serialno int8 not null unique,
> grant_status int
> constraint status_limiter check (grant_status in
> (1,2,3)) );
>
> The idea is that a grant_status of 1 means the serial number is
> unallocated, a grant_status of 2 means it's pending, and 3 means it's
> granted. When you start, in one transaction you pick the next
> available serialno with a status of 1. Then you update that row to
> set it to 2 (make sure you use "where grant_status = 1" to avoid a
> race condition), and then commit. Now you have your serial number.
> Use it, and then at the end of your transaction where you are
> committing, set the grant_status to 3, so you know it's really used.
>
> Now, how do you handle the cases where either the transaction fails
> so you can't set it to 3? Simple: your client captures errors and
> then sets the value back to 1 later. For client errors, you need yet
> another process which will go around periodically and check for
> grant_status = 2, and make sure nobody's actually in the middle of
> trying to use them. (You could refine the seq_allocation table by
> storing the pid of the allocating back end. Then your maintenance
> script could look for such a back end while cleaning up.)
>
> The savepoints features of 8.0 will make some of this even easier for
> you.
>
> Note that this second method is not completely bulletproof, but it
> might be good enough for the cases you want. I have a feeling,
> however, that you're creating a new problem for yourself by not being
> able to skip sequence values. My bet is that you actually need to
> find a better way to solve the "other serious problems" you have
> rather than banging on sequences to get them to fit your intended
> use.
>
> A
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 19:29 Tino Wildenhain <tino@wildenhain.de>
parent: Eric E <whalesuit@bonbon.net>
1 sibling, 1 reply; 27+ messages in thread
From: Tino Wildenhain @ 2004-10-20 19:29 UTC (permalink / raw)
To: Eric E <whalesuit@bonbon.net>; +Cc: pgsql-general
Hi,
Am Mi, den 20.10.2004 schrieb Eric E um 19:52:
> Hi Tino,
> Many thanks for helping me.
>
> I know that the sequence issue is a troubling one for many on the list.
> Perhaps if I explain the need for a continuous sequence I can circumvent
> some of that:
>
> This database is for a laboratory, and the numbers in sequence
> determine storage locations for a sample. Having a physical space in
> our storage boxes tells us something has happened - the sample was used
> up, broken, in use, etc - and account for that missing sample. If the
> generated sequence has holes in it, we cannot tell if a sample is
> properly not in the rack, or if that hole was simply generated by the
> database. Allowing empties would also fill up limited box space with
> spaces generated by the database.
> If anyone has a brilliant idea for how a non-continuous sequence could
> address the needs, I'd be delighted to hear it, but short of that I
> think I have to keep this requirement.
Maybe you skip the sequence thingy alltogether in this case and
use an approach like this:
initialize a table with all possible locations and mark them
as empty.
CREATE TABLE locations (location_id int2,taken bool);
(you might want to have a timestamp for changes too)
Whenever you change state of a location, do it like this
(perhaps in a function)
SELECT INTO loc_id location_id FROM locations WHERE taken
FOR UPDATE;
IF FOUND THEN
UPDATE location SET taken=true WHERE location_id=loc_id;
ELSE
RAISE EXCEPTION 'no free location anymore';
...
AND the other way round for freeing a location.
The SELECT ... FOR UPDATE should lock the candidate
position in the table so concurrent
transactions have to wait then then find another
free cell when they wake up.
Advantage: not a full table scan. Only the first
matching row should be used and locked.
Not this is only a rough sketch and you should
look for the actual syntax and more flesh for
the function.
Regards
Tino
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 19:37 Eric E <whalesuit@bonbon.net>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 0 replies; 27+ messages in thread
From: Eric E @ 2004-10-20 19:37 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-general
Hmm.... that's a really intesting idea, Tino. Since we're probably
talking about 1000000 numbers max, a query on this table would work
fairly fast, and operationally simple. I'll think about that.
Thanks,
Eric
Tino Wildenhain wrote:
>Hi,
>
>Am Mi, den 20.10.2004 schrieb Eric E um 19:52:
>
>
>>Hi Tino,
>> Many thanks for helping me.
>>
>>I know that the sequence issue is a troubling one for many on the list.
>>Perhaps if I explain the need for a continuous sequence I can circumvent
>>some of that:
>>
>> This database is for a laboratory, and the numbers in sequence
>>determine storage locations for a sample. Having a physical space in
>>our storage boxes tells us something has happened - the sample was used
>>up, broken, in use, etc - and account for that missing sample. If the
>>generated sequence has holes in it, we cannot tell if a sample is
>>properly not in the rack, or if that hole was simply generated by the
>>database. Allowing empties would also fill up limited box space with
>>spaces generated by the database.
>>If anyone has a brilliant idea for how a non-continuous sequence could
>>address the needs, I'd be delighted to hear it, but short of that I
>>think I have to keep this requirement.
>>
>>
>
>Maybe you skip the sequence thingy alltogether in this case and
>use an approach like this:
>
>initialize a table with all possible locations and mark them
>as empty.
>
>CREATE TABLE locations (location_id int2,taken bool);
>
>(you might want to have a timestamp for changes too)
>
>Whenever you change state of a location, do it like this
>(perhaps in a function)
>
>SELECT INTO loc_id location_id FROM locations WHERE taken
> FOR UPDATE;
>IF FOUND THEN
> UPDATE location SET taken=true WHERE location_id=loc_id;
>ELSE
> RAISE EXCEPTION 'no free location anymore';
>
>...
>
>AND the other way round for freeing a location.
>The SELECT ... FOR UPDATE should lock the candidate
>position in the table so concurrent
>transactions have to wait then then find another
>free cell when they wake up.
>
>Advantage: not a full table scan. Only the first
>matching row should be used and locked.
>
>Not this is only a rough sketch and you should
>look for the actual syntax and more flesh for
>the function.
>
>Regards
>Tino
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Sequence question
@ 2004-10-20 19:38 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Eric E <whalesuit@bonbon.net>
0 siblings, 0 replies; 27+ messages in thread
From: Andrew Sullivan @ 2004-10-20 19:38 UTC (permalink / raw)
To: pgsql-general
On Wed, Oct 20, 2004 at 02:59:27PM -0400, Eric E wrote:
> - have the sequence preallocation table hold only numbers with status
> being available or pending, i.e., delete numbers once they have been
> allocated. This leaves on two possible statuses: available and pending.
I would argue that you're best to record everything. That is, your
proposal moves from a table which covers all the possible states --
granted, pending, and available -- to a table which entails ambiguous
answers to some questions. Since you're not going to preallocate
every logically possible id, then if an id isn't in the table, you
can't tell if it simply has never been allocated, or if it has
already been used. If you have that stored elsewhere, though, you
can get it from a join, so perhaps there's no need for this. (I note
that a real normalisation freak, like the one I occasionally play on
TV, would require you to use a REFERENCES constraint on the status
value, and use the referenced table as a control for what status
values you can use. This has the not inconsiderable benefit that if
you have a new status -- say, "storage burned down" or "impounded by
SCO for copyright violation" or something else -- you have a
completely trivial way to add it. It's certainly the way I'd
actually do this.)
> I also liked your point about the atomicity of :
> get number, change status to pending, commit
The real problem with it is that you do have the possibility of
orphaned pending actions.
> My thought was that the you could set the status ussing sessionID. That
> way a server-side job could look for expired sessions and remark those
> numbers available.
That's something like what I'd do, yes. It mostly depends on what's
available to your application. I tend to be very belt-and-suspenders
about this sort of thing. Probably I'd put a wall-clock timestamp on
the field, too, to give me clues about when things might be going
wrong, &c.
> > find a better way to solve the "other serious problems" you have
> > rather than banging on sequences to get them to fit your intended
> > use.
>
> I just haven't really seen anyway around the need to use all of our
> storage rows that doens't involve a complicated mapping to boxes.
I was more concerned that you were trying to do this for invoice
numbers, another place where people often require serial numbers. In
that case, I usually think they're wrong, because I can think of
plenty of better ways to solve that one (unless it's a legal
requirement, which is sometimes is). But mapping data points to
places in space is one of those cases where you probably _do_ need
this sort of preallocation mechanism. It's what hotels do, after
all.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2004-10-20 19:38 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-05 02:11 sequence question Timothy Reaves <treaves@silverfields.com>
2002-02-05 05:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-10 03:16 sequence question Williams, Travis L, NPONS <tlw@att.com>
2003-01-10 04:35 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-27 20:06 sequence question Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2003-01-27 20:07 ` Dan Langille <dan@langille.org>
2003-01-27 20:17 ` Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2003-12-17 01:12 Sequence question. Anthony Best <abest@digitalflex.net>
2003-12-17 07:57 ` Re: Sequence question. John Sidney-Woollett <johnsw@wardbrook.com>
2003-12-17 08:08 ` Re: Sequence question John Sidney-Woollett <johnsw@wardbrook.com>
2004-08-04 23:20 Sequence question Oscar Tuscon <obtuse@bmwe30.net>
2004-08-05 04:19 Sequence Question Oscar Tuscon <obtuse@bmwe30.net>
2004-08-05 06:54 ` Re: Sequence Question Prabu Subroto <prabu_subroto@yahoo.com>
2004-08-05 15:07 ` Re: Sequence Question Greg Stark <gsstark@mit.edu>
2004-08-05 19:50 Re: Sequence Question Oscar Tuscon <obtuse@bmwe30.net>
2004-10-18 23:16 Sequence question Eric E <whalesuit@bonbon.net>
2004-10-20 15:35 ` Re: Sequence question Tino Wildenhain <tino@wildenhain.de>
2004-10-20 17:52 ` Re: Sequence question Eric E <whalesuit@bonbon.net>
2004-10-20 18:29 ` Re: Sequence question Andrew Sullivan <ajs@crankycanuck.ca>
2004-10-20 19:29 ` Re: Sequence question Tino Wildenhain <tino@wildenhain.de>
2004-10-20 19:37 ` Re: Sequence question Eric E <whalesuit@bonbon.net>
2004-10-19 15:19 Sequence question Eric E <whalesuit@bonbon.net>
2004-10-20 15:57 ` Re: Sequence question Andrew Sullivan <ajs@crankycanuck.ca>
2004-10-20 17:00 ` Re: Sequence question Alvaro Herrera <alvherre@dcc.uchile.cl>
2004-10-20 18:59 ` Re: Sequence question Eric E <whalesuit@bonbon.net>
2004-10-20 19:38 ` Re: Sequence question Andrew Sullivan <ajs@crankycanuck.ca>
2004-10-20 15:16 Re: Sequence question David Ecker <david@familie-ecker.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox