Обсуждение: currval() in insert statements

Hi,

I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
following statement stopped working:

  insert into test (b) select currval('test_a_seq'::regclass)

given the following table:

  create table test (a serial, b int)

The error I'm getting is: "ERROR:  currval of sequence "test_a_seq" is not yet
defined in this session". With a values clause instead of a select, it still
works as expected, by inserting two identical values:

  insert into test (b) values (currval('test_a_seq'::regclass))

Although it's easy enough to work around this problem by also explicitly
inserting nextval('test_a_seq'::regclass) into column "a", I'm wondering why
it did work in 8.1 and doesn't work anymore in 8.3.

Is this a bug in either version, was is never supposed to work like this, or
have I missed a change in the release notes somewhere?

Thanks,

Onno

nextval.
currval only has value, when sequence wwas used in the session before,
you would use it to obtain last value returened by nextval. read the
manual .


On Sat, Feb 14, 2009 at 11:25 AM, Onno Molenkamp <onno@flox.org> wrote:
> Hi,
>
> I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
> following statement stopped working:
>
>  insert into test (b) select currval('test_a_seq'::regclass)
>
> given the following table:
>
>  create table test (a serial, b int)
>
> The error I'm getting is: "ERROR:  currval of sequence "test_a_seq" is not yet
> defined in this session". With a values clause instead of a select, it still
> works as expected, by inserting two identical values:
>
>  insert into test (b) values (currval('test_a_seq'::regclass))
>
> Although it's easy enough to work around this problem by also explicitly
> inserting nextval('test_a_seq'::regclass) into column "a", I'm wondering why
> it did work in 8.1 and doesn't work anymore in 8.3.
>
> Is this a bug in either version, was is never supposed to work like this, or
> have I missed a change in the release notes somewhere?
>
> Thanks,
>
> Onno
>



--
GJ

Hi,

I know that, and nextval -is- being called because it's the default value of
column "a". The problem is that apparently the order in which it's called has
changed for the form with a select, not that I don't know the difference
between currval and nextval.

Onno

Op Saturday 14 February 2009 14:41:44 schreef Grzegorz Jaśkiewicz:
> nextval.
> currval only has value, when sequence wwas used in the session before,
> you would use it to obtain last value returened by nextval. read the
> manual .

On 14/02/2009 11:25, Onno Molenkamp wrote:
> I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
> following statement stopped working:
>
>   insert into test (b) select currval('test_a_seq'::regclass)

I don't think this was ever guaranteed to work - the docs say pretty
clearly that nextval() has to be called before currval() can be expected
to be defined. My feeling is that is was just good fortune that it
worked for you before.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Onno Molenkamp <onno@flox.org> writes:
> I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
> following statement stopped working:
>   insert into test (b) select currval('test_a_seq'::regclass)

It doesn't work in 8.1 either:

Welcome to psql 8.1.16, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

regression=# create table test (a serial, b int);
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for serial column "test.a"
CREATE TABLE
regression=# insert into test (b) select currval('test_a_seq'::regclass);
ERROR:  currval of sequence "test_a_seq" is not yet defined in this session
regression=#

There must be something else that you changed about your application.

            regards, tom lane

Onno Molenkamp wrote:
> Hi,
>
> I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
> following statement stopped working:
>
>   insert into test (b) select currval('test_a_seq'::regclass)

It's generally a REALLY bad idea to mix `nextval' and `currval' use on
the same sequence in a single SQL statement.

Personally, in the one case in the project I'm working on where I DO
need to INSERT a generated ID in two places I use a trigger to take care
of it.

--
Craig Ringer