Thread: target list evaluation wrt sequences

I have an insert using a select of sequences.

insert into ...
    select
        nextval('n') as a,
        currval('n') as b,
        ...
    from lalala
;

Is the order of the target list guaranteed?
That is, will the a and b in the above selection
*always* be the same?

My experiments show this to be true, but I
would like to know that the evaluation of the
target list is never rearranged so that the
values are always predictable.

Thanks,

elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.

On Sat, Apr 24, 2004 at 04:03:24PM -0700, elein wrote:

> insert into ...
>     select
>         nextval('n') as a,
>         currval('n') as b,
>         ...
>     from lalala
> ;
>
> Is the order of the target list guaranteed?
> That is, will the a and b in the above selection
> *always* be the same?

No, it is not guaranteed to be the same.

PS: did you get my previous emails?  I sent #70 to you, and a lot of
questions ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

On Sat, Apr 24, 2004 at 16:03:24 -0700,
  elein <elein@varlena.com> wrote:
>
> I have an insert using a select of sequences.
>
> insert into ...
>     select
>         nextval('n') as a,
>         currval('n') as b,
>         ...
>     from lalala
> ;
>
> Is the order of the target list guaranteed?
> That is, will the a and b in the above selection
> *always* be the same?

No. You can do effectively this by joining a select nextval to whatever
you main select is. Something like:
insert into ...
       select a.n as a, a.n as b, ....
             from (select nextval('n') as n) as a, lalala

>   elein <elein@varlena.com> wrote:
>> Is the order of the target list guaranteed?

AFAIR, all current and past Postgres versions evaluate target lists
left-to-right.  This is not guaranteed to remain true forever,
since neither the SQL spec nor our own docs promise it anywhere...
but offhand I can't think of a reason to break it.

Bruno Wolff III <bruno@wolff.to> writes:
> No. You can do effectively this by joining a select nextval to whatever
> you main select is. Something like:
> insert into ...
>        select a.n as a, a.n as b, ....
>              from (select nextval('n') as n) as a, lalala

Urgh ... I'd not want to promise that nextval() will always be evaluated
just once in the above example ... this really seems *much* more fragile
than assuming left-to-right targetlist evaluation :-(

            regards, tom lane

On Fri, Apr 30, 2004 at 01:01:25 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bruno Wolff III <bruno@wolff.to> writes:
> > No. You can do effectively this by joining a select nextval to whatever
> > you main select is. Something like:
> > insert into ...
> >        select a.n as a, a.n as b, ....
> >              from (select nextval('n') as n) as a, lalala
>
> Urgh ... I'd not want to promise that nextval() will always be evaluated
> just once in the above example ... this really seems *much* more fragile
> than assuming left-to-right targetlist evaluation :-(
>

Thanks for the heads up. I have so far only used that technique to speed up
some queries with respect to using subselects, where the subquery would
always evaluate to the same value anyway.
If I need a single value from a volatile calculation to be used more than
once, I will remember to use a separate query to save the value in a table and
then refer to that value later.