On 5/10/07, Spiegelberg, Greg <[EMAIL PROTECTED]> wrote:
List,
Check out the value for t1_s_seq on connection #1.
1: db=# select nextval('t1_s_seq');
nextval
---------
1
(1 row)
And check the value for t1_s_seq on connection #2.
2: db=# select nextval('t1_s_seq');
nextval
---------
2
(1 row)
So far, so good. Now start a transaction on connection #1, advance
t1_s_seq by 10 but don't commit;
1: db=# begin;
BEGIN
1: db=# select setval('t1_s_seq', currval('t1_s_seq')+10);
setval
--------
11
(1 row)
And check the current value for t1_s_seq on connection #2.
2: db=# select currval('t1_s_seq');
nextval
---------
2
(1 row)
That's expected since the transaction on connection #1 hasn't been
commited. Now commit the transaction on connection #1 and check it's
current value.
1: db=# commit;
COMMIT
1: db=# select currval('t1_s_seq');
currval
---------
11
(1 row)
Again, expected. Now let's check the current value on connection #2
again.
2: db=# select currval('t1_s_seq');
currval
---------
2
(1 row)
This is where I take issue with the output. I'm not sure what benefit
cache value has as the client should, in this case, consulted with the
backend as too the value of the sequence.
Sequences are suppose to be unique but in this case it seems that may not
always be the case. Sequences have some kind of odd relationship (no pun
intended) with transactions in that they are in some cases in sync
regardless of the connection or query and in other situations, such as
above, are out of sync.
Just for fun, select on connection #2 the nextval of the sequence.
2: db=# select nextval('t1_s_seq');
nextval
---------
12
(1 row)
*boogle*
What have I done wrong here? Does it have any bearing on my unique
constraint error? I wouldn't think so but I haven't found any other
possible explanation.
TIA,
Greg
The documentation says:
currval
Return the value most recently obtained by nextval for this sequence in the
current session. (An error is reported if nextval has never been called for
this sequence in this session.) Notice that because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.
So currval is session specific. So I'd say this is expected behaviour.
Regards
MP
