Thread: Behavior of nextval() and currval()
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
> 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
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
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
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
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
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/
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