pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAtomic query and update of sequence generators
3+ messages / 3 participants
[nested] [flat]
* Atomic query and update of sequence generators
@ 2004-01-15 00:51 Jeffrey Tenny <jeffrey.tenny@comcast.net>
2004-01-15 03:30 ` Re: Atomic query and update of sequence generators Tom Lane <tgl@sss.pgh.pa.us>
2004-01-15 12:47 ` Re: Atomic query and update of sequence generators Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jeffrey Tenny @ 2004-01-15 00:51 UTC (permalink / raw)
To: pgsql-sql
If I want to allocate a block of adjacent values from a sequence generator,
is the following statement atomic with respect to the time between
when the call to nextval() and setval()?
SELECT setval('foo', nextval()+20) ...
The goal is to get a sequence of 20 values that are all +1 from each other.
If the above isn't safe in concurrent environments, is there something
else I can
do to achieve the effect safely?
I saw a similar unanswered question on one of the PostgreSQL newsgroups,
and have a need to do the same thing from my JDBC app.
Tips appreciated.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Atomic query and update of sequence generators
2004-01-15 00:51 Atomic query and update of sequence generators Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2004-01-15 03:30 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-01-15 03:30 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: pgsql-sql
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> If I want to allocate a block of adjacent values from a sequence generator,
> is the following statement atomic with respect to the time between
> when the call to nextval() and setval()?
> SELECT setval('foo', nextval()+20) ...
Nope.
> The goal is to get a sequence of 20 values that are all +1 from each other.
Do you always want to pull exactly 20 values? If so you could set the
sequence's "cache" parameter to 20 (see the CREATE SEQUENCE man page for
details). I don't think there's any way at present to get varying sizes
of consecutively-allocated blocks. If you need that, it would likely
not be real hard to implement a "next_n_vals(seq, n)" variant of
nextval() to grab N consecutive values and return the first. But it's
not there at the moment.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Atomic query and update of sequence generators
2004-01-15 00:51 Atomic query and update of sequence generators Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2004-01-15 12:47 ` Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Sabino Mullane @ 2004-01-15 12:47 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> The goal is to get a sequence of 20 values that are
> all +1 from each other.
If you don't care about the rest of the values being +1
from each other, you could create the sequence with an
INTERVAL of 20. Other than that, you would probably have
to create your own "sequence" generator. Another
alternative would be a function like this:
get nextval as x
set sequence to x+20
get nextval as y
repeat if y != x+20 (i.e. someone else grabbed a value)
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200401150746
-----BEGIN PGP SIGNATURE-----
iD8DBQFABovdvJuQZxSWSsgRAnxeAKCWK3tFCE3u8NfXG5LG3H0smDLyhACglrr7
08ke6k8B8MSKVipRb2aSWQg=
=PSzM
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-15 12:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 00:51 Atomic query and update of sequence generators Jeffrey Tenny <jeffrey.tenny@comcast.net>
2004-01-15 03:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-15 12:47 ` Greg Sabino Mullane <greg@turnstep.com>
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