Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C268A9FB7B7 for ; Thu, 10 May 2007 12:30:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 32282-01 for ; Thu, 10 May 2007 12:30:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id 644DC9FB6E5 for ; Thu, 10 May 2007 12:30:28 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id a73so514108pye for ; Thu, 10 May 2007 08:30:27 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=V7m7m7PsFeQMqdz2gCs1U+oa0VlWUK8ugjYkcu/dQ39FCDrjChiyPmmbyPuWPT9OqnSWmVHcPfz+rHbjfKBZfrm1KDts3nQs7eIl+rvHiRqSpRRXzMhr+5Uw0i9cF9WzFFEidA9yjoSED3ubxU70IKRIbraVDRUd7c/+QbjfEYI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=k94WN9L4Recj0btYNou5dJQq39+IXcTWceLJ3pR8f5QaANgNeRdElE8CKNsXGm5m756/9d+sEGEzfd0FBgJpAgtAru08TY68j5mxLB0u7siWsxYB0l1t3FmELz7X+HnPkfTe/wvSey3hNOauPsPB45vsvppBvDqgziO+tjGsI/I= Received: by 10.35.9.15 with SMTP id m15mr3207854pyi.1178811027582; Thu, 10 May 2007 08:30:27 -0700 (PDT) Received: by 10.35.64.17 with HTTP; Thu, 10 May 2007 08:30:27 -0700 (PDT) Message-ID: <2ca799770705100830w5990a8f1qbc2423a2f03c90bd@mail.gmail.com> Date: Thu, 10 May 2007 18:30:27 +0300 From: "Mikko Partio" To: "Spiegelberg, Greg" Subject: Re: duplicate key violates unique constraint Cc: pgsql-admin@postgresql.org In-Reply-To: <82E74D266CB9B44390D3CCE44A781ED90B6B15@POSTOFFICE.cranel.local> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_33210_9183106.1178811027346" References: <82E74D266CB9B44390D3CCE44A781ED90B6B15@POSTOFFICE.cranel.local> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/87 X-Sequence-Number: 25407 ------=_Part_33210_9183106.1178811027346 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 5/10/07, Spiegelberg, Greg 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 ------=_Part_33210_9183106.1178811027346 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 5/10/07, Spiegelberg, Greg <gspiegelberg@isodxsolutions.com> 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

------=_Part_33210_9183106.1178811027346--