Thread: Behavior of nextval() and currval()

Behavior of nextval() and currval()

From
Mike Mascari
Date:
The following works in 7.1. Is this behavior guaranteed or is it the
subject of possible change in the future? Or am I just "getting
lucky" that nextval() is evaluated before currval():

CREATE SEQUENCE s;

CREATE TABLE test (
key1 int4,
key2 int4
);

INSERT INTO test
SELECT nextval('s'), currval('s');

SELECT * FROM test;

 key1 | key2
------+------
    1 |    1

Thanks for any information,

Mike Mascari
mascarm@mascari.com

Re: Behavior of nextval() and currval()

From
Bruce Momjian
Date:
> The following works in 7.1. Is this behavior guaranteed or is it the
> subject of possible change in the future? Or am I just "getting
> lucky" that nextval() is evaluated before currval():
>
> CREATE SEQUENCE s;
>
> CREATE TABLE test (
> key1 int4,
> key2 int4
> );
>
> INSERT INTO test
> SELECT nextval('s'), currval('s');
>
> SELECT * FROM test;
>
>  key1 | key2
> ------+------
>     1 |    1
>
> Thanks for any information,

So you are asking if the order of column function evaluations is
reliable.  I tend to think so.  I think the only thing that could
reorder it is rules.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Behavior of nextval() and currval()

From
Tom Lane
Date:
Mike Mascari <mascarm@mascari.com> writes:
> The following works in 7.1. Is this behavior guaranteed or is it the
> subject of possible change in the future? Or am I just "getting
> lucky" that nextval() is evaluated before currval():

> SELECT nextval('s'), currval('s');

Hmm.  SELECT target expressions are indeed evaluated left to right at
present, and I don't see any reason to change that --- but it's not
guaranteed by the spec AFAIK.  I think you're right to be wary of
depending on it.  Why would you need to do this anyway?

            regards, tom lane

Re: Behavior of nextval() and currval()

From
Mike Mascari
Date:
Bruce Momjian wrote:
>
> So you are asking if the order of column function evaluations is
> reliable.  I tend to think so.  I think the only thing that could
> reorder it is rules.

and

Tom Lane wrote:
>
> Mike Mascari <mascarm@mascari.com> writes:
> > The following works in 7.1. Is this behavior guaranteed or is it the
> > subject of possible change in the future? Or am I just "getting
> > lucky" that nextval() is evaluated before currval():
>
> > SELECT nextval('s'), currval('s');
>
> Hmm.  SELECT target expressions are indeed evaluated left to right at
> present, and I don't see any reason to change that --- but it's not
> guaranteed by the spec AFAIK.  I think you're right to be wary of
> depending on it.  Why would you need to do this anyway?

It's an odd scenario, where I need to generate primary keys from an
INSERT..SELECT and the secondary field should match the primary key
in this instance. The secondary field represents a "parent" which
refers to itself if the record is not a child (as opposed to say,
0). I've switched to using a CREATE TEMPORARY TABLE AS SELECT..,
INSERT..SELECT to avoid the scenario.

Thanks,

Mike Mascari
mascarm@mascari.com

>
>                         regards, tom lane

Re: Behavior of nextval() and currval()

From
Tom Lane
Date:
Mike Mascari <mascarm@mascari.com> writes:
> I've switched to using a CREATE TEMPORARY TABLE AS SELECT..,
> INSERT..SELECT to avoid the scenario.

Uh, you mean something like

    select a, a from (select nextval('foo') as a) as b;

That might surprise you even more :-(

Perhaps the planner shouldn't pull up subqueries whose targetlists
include any noncachable functions.  This needs more thought.

            regards, tom lane

Re: Behavior of nextval() and currval()

From
Mike Mascari
Date:
Tom Lane wrote:
>
> Mike Mascari <mascarm@mascari.com> writes:
> > I've switched to using a CREATE TEMPORARY TABLE AS SELECT..,
> > INSERT..SELECT to avoid the scenario.
>
> Uh, you mean something like
>
>         select a, a from (select nextval('foo') as a) as b;

I'm not that clever. I just did:

CREATE TEMPORARY TABLE foo AS
SELECT nextval('foo') as a, 0 as b, ...
FROM source;

INSERT INTO bar
SELECT a, a
FROM foo;

instead of:

INSERT INTO bar
SELECT nextval('foo'), currval('foo'), ...
FROM source;

>
> That might surprise you even more :-(
>

You mean:

test=# create table test (key int4 not null);
CREATE
test=# insert into test values (1);
INSERT 803954 1
test=# insert into test values (2);
INSERT 803955 1
test=# select a, a from (select nextval('foo') as a) as b, test;
 a | a
---+---
 4 | 4
 4 | 4 <--- That should be 5?

>
> Perhaps the planner shouldn't pull up subqueries whose targetlists
> include any noncachable functions.  This needs more thought.
>
>                         regards, tom lane

Mike Mascari
mascarm@mascari.com

XML/PostgreSQL Application server now available

From
"Command Prompt, Inc."
Date:
Hey guys,

Hot on the heals of our book (Practical PostgreSQL) being sent to final
edit, we have released version 0.8 of our PostgreSQL application server.
It is available in the book (on the CD) as well as online at:

http://www.commandprompt.com/products_lxp.lxp

You can read the documentation here:

http://www.postgresql.info/c19970.htm

Sincerely,

Joshua Drake

--
by way of pgsql-general@commandprompt.com
http://www.postgresql.info/
http://www.commandprompt.com/


Re: Behavior of nextval() and currval()

From
Tom Lane
Date:
Mike Mascari <mascarm@mascari.com> writes:
> Tom Lane wrote:
>> That might surprise you even more :-(

> You mean:

> test=# create table test (key int4 not null);
> CREATE
> test=# insert into test values (1);
> INSERT 803954 1
> test=# insert into test values (2);
> INSERT 803955 1
> test=# select a, a from (select nextval('foo') as a) as b, test;
>  a | a
> ---+---
>  4 | 4
>  4 | 4 <--- That should be 5?

That works okay, but there are variant cases where the planner
will collapse the inner and outer selects, replacing each reference
in the outer select with a copy of the expression from the inner
select.  For example,

regression=# select key, a, a from (select key, nextval('foo') as a from test) ss;
 key | a | a
-----+---+---
   1 | 2 | 3
   2 | 4 | 5
(2 rows)

regression=#

This is probably not good when the inner expression contains noncachable
functions ... but I'm hesitant to disable the potential optimization
from collapsing the selects in "harmless" cases such as use of
timestamp-dependent functions.  We really need a finer-grain notion
of function cachability...

            regards, tom lane