Обсуждение: problem fetching currval of sequence

-- 

Charles,

> I have a script to load values into a table (gb) which contains a
> SEQUENCE ('gb_id').  For each entry into TABLE gb I need the value of
> 'gb_gb_id_seq' to update a relational table.   I am getting the
> following error msg:
>   ERROR: gb_gb_id_seq.currval is not yet defined in this session.

Sometime in the upgrade process for 7.1.0 --> 7.2.1, the CURRVAL
function changed so that it was rigorously session-specific.  I can see
a number of good reasons for doing this, but it has the unfortunate
side effect that CURRVAL is virtually useless for querying the current
value of the row you just inserted using a front-end that creates a new
session for each command.   

I'd love to hear Tom, Bruce, or Stephan's comments on the issue.  It is
an annoyance, and it would be nice to know the reason for the decrease
in functionality of CURRVAL.

All that aside, the first query you are sending to Postgres is an
UPDATE query, not an INSERT.  So what good was CURRVAL going to do you
anyway?   

-Josh Berkus

On Tue, 6 Aug 2002, Josh Berkus wrote:

> Charles,
>
> > I have a script to load values into a table (gb) which contains a
> > SEQUENCE ('gb_id').  For each entry into TABLE gb I need the value of
> > 'gb_gb_id_seq' to update a relational table.   I am getting the
> > following error msg:
> >   ERROR: gb_gb_id_seq.currval is not yet defined in this session.
>
> Sometime in the upgrade process for 7.1.0 --> 7.2.1, the CURRVAL
> function changed so that it was rigorously session-specific.  I can see
> a number of good reasons for doing this, but it has the unfortunate
> side effect that CURRVAL is virtually useless for querying the current
> value of the row you just inserted using a front-end that creates a new
> session for each command.
>
> I'd love to hear Tom, Bruce, or Stephan's comments on the issue.  It is
> an annoyance, and it would be nice to know the reason for the decrease
> in functionality of CURRVAL.

Presumably because it was fairly useless the other way?  I must be missing
how it worked. I can't see what you could do with the value apart from
getting a value that was at one point the most recent value of the
sequence, but a random number up to its current value in its range would
do the same thing. ;)  It doesn't guarantee you that you can see a row
with that value (in the case of the default) - or even if such a row will
ever exist, it doesn't guarantee you that it's really the last row (since
nextval could be called after the value is gotten but before your query
results are returned.  If you're doing an insert in one session, closing
the session, and starting a new one wanting to know the insert value from
the previous one, I don't see how currval helps, it'll give you *a* most
recent value, but not necessarily yours (unless you only have one updating
session, in which case you don't really need a sequence since you don't
need the concurrency guarantees).

Charles Hauser <chauser@duke.edu> writes:
>      foreach $id(sort keys %ACCN) {
>     $conn->exec(
>                     "UPDATE gb SET accn_no = '$ACCN{$id}{gb}',gi_no = 
> '$ACCN{$id}{gi}' WHERE clone_id = '$ACCN{$id}{clone_id}'"
>                     );

>     $result = $conn->exec("SELECT currval('gb_gb_id_seq');");

This isn't inserting any new row, so nextval() isn't called, so there's
nothing for currval() to work on.  Are you sure you did not mean to do
an INSERT?

If you did want an UPDATE, seems like you could do
SELECT gb_id FROM gb WHERE clone_id = '$ACCN{$id}{clone_id}'
        regards, tom lane

"Josh Berkus" <josh@agliodbs.com> writes:
> Sometime in the upgrade process for 7.1.0 --> 7.2.1, the CURRVAL
> function changed so that it was rigorously session-specific.

Huh?  AFAIR it's always worked the way it does now (and a quick review
of the CVS logs for sequence.c backs up my recollection).

> ... it has the unfortunate
> side effect that CURRVAL is virtually useless for querying the current
> value of the row you just inserted using a front-end that creates a new
> session for each command.   

Such a front end would be too broken to consider using anyway: if you
cannot wrap several related commands into a transaction, you cannot use
a database the way it's intended to be used.  The connection-pooling
front ends that I know about all give you dedicated use of a connection
for at least the length of a transaction block, so the secret to using
currval is "do it in the same transaction block as you call nextval in".
        regards, tom lane

Charles Hauser <chauser@duke.edu> writes:
>      foreach $id(sort keys %ACCN) {
> $conn->exec(
>                     "UPDATE gb SET accn_no = '$ACCN{$id}{gb}',gi_no =
> '$ACCN{$id}{gi}' WHERE clone_id = '$ACCN{$id}{clone_id}'"
>                     );

> $result = $conn->exec("SELECT currval('gb_gb_id_seq');");

This isn't inserting any new row, so nextval() isn't called, so there's
nothing for currval() to work on.  Are you sure you did not mean to do
an INSERT?

If you did want an UPDATE, seems like you could do

      SELECT gb_id FROM gb WHERE clone_id = '$ACCN{$id}{clone_id}'

                    regards, tom lane

-- 

Charles Hauser <chauser@duke.edu> writes:
> Quite right, I did mean to do an INSERT.  Changing the script to do 
> so though I still get the error:
>         ERROR: gb_gb_id_seq.currval is not yet defined in this session.


>      foreach $id(sort keys %ACCN) {
>     $conn->exec(
>                            "INSERT INTO gb (clone_id,accn,gi) 
> VALUES ( '$ACCN{$id}{clone_id}','$ACCN{$id}{gb}','$ACCN{$id}{gi}')";
>                            );

>     $result = $conn->exec("SELECT currval('gb_gb_id_seq');");
>     die $conn->errorMessage unless PGRES_TUPLES_OK eq 
> $result->resultStatus;
>     $gb_id = $result->fetchrow;
>     $ACCN{$id}{gb_id} = $gb_id;
>      }


Hmm, that sure looks like it should work.  I can only counsel looking
for silly errors (typos, not running the same copy of the script that you
edited, etc).  Also double-check that gb.gb_id has the column default
nextval('gb_gb_id_seq')  (try psql's "\d gb" command).
        regards, tom lane

Tom,

> Huh?  AFAIR it's always worked the way it does now (and a quick review
> of the CVS logs for sequence.c backs up my recollection).

No, back in 7.1.0 I was able to call CURRVAL without having previously called
NEXTVAL, and it would give me the "current" sequence value; that is, the same
result value as (NEXTVAL() - 1);   I wouldn't mind having a new function that
does this; I can understand making a distinction between the pre-session
CURRVAL, and the general current value.

> Such a front end would be too broken to consider using anyway: if you
> cannot wrap several related commands into a transaction, you cannot use
> a database the way it's intended to be used.  The connection-pooling
> front ends that I know about all give you dedicated use of a connection
> for at least the length of a transaction block, so the secret to using
> currval is "do it in the same transaction block as you call nextval in".

Me, I use functions.


--
-Josh BerkusAglio Database SolutionsSan Francisco

Josh Berkus <josh@agliodbs.com> writes:
> No, back in 7.1.0 I was able to call CURRVAL without having previously
> called NEXTVAL, and it would give me the "current" sequence value;
> that is, the same result value as (NEXTVAL() - 1)

I don't believe it, and neither do the 7.1.3 and 7.0.2 databases that
I have handy to check with.  For example:

play=> select version();                            version
------------------------------------------------------------------PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by
gcc2.95.2
 
(1 row)

play=> create sequence seq;
CREATE
play=> select currval('seq');
ERROR:  seq.currval is not yet defined in this session
play=> select nextval('seq');nextval
---------      1
(1 row)

play=> select currval('seq');currval
---------      1
(1 row)

play=> \c play
You are now connected to database play.
play=> select currval('seq');
ERROR:  seq.currval is not yet defined in this session


Are you sure you were not thinking of "select last_value from seq"?
That's about the closest approximation I can think of to a
non-transaction-safe version of currval().
        regards, tom lane

Tom,

> Are you sure you were not thinking of "select last_value from seq"?
> That's about the closest approximation I can think of to a
> non-transaction-safe version of currval().

Well, you are the authority.   And I'm not willing to regress my database
install just to figure it out.   Maybe I am thinking of "Select last_value".

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco 

-- 

Charles,

> I know this script worked in the past, but I have not used it
> recently.  We are running PostgreSQL 7.1.3.  It would be great if
> this would work as I have several scripts which operate in a similar
> manner for different TABLES, but if it won't work I'll work around it.
>
> Suggestions for the best way to get the value for the gb_id after an INSERT?

I usually SELECT NEXTVAL(), and then pass that ID number to the INSERT
statement.   It's very reliable.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco