Postgres Pro
Downloads
Home   >   mailing lists

Thread: VALUES() evaluation order

VALUES() evaluation order

From
Steve Atkins
Date:
Is the order in which the expressions in a VALUES() clause defined?

I'm doing this: INSERT INTO foo (a, b) VALUES (nextval('bar'), currval('bar'))

It works fine, but I'm wondering whether it's guaranteed to work or whether I'm relying on an artifact of the
implementation.

Cheers,
  Steve

Re: VALUES() evaluation order

From
Tom Lane
Date:
Steve Atkins <steve@blighty.com> writes:
> Is the order in which the expressions in a VALUES() clause defined?
> I'm doing this: INSERT INTO foo (a, b) VALUES (nextval('bar'), currval('bar'))

> It works fine, but I'm wondering whether it's guaranteed to work or whether I'm relying on an artifact of the
implementation.

I'd say it's an artifact.  It probably does work reliably at the moment,
but if we had a reason to change it we'd not feel much compunction about
doing so.  (The most obvious potential reason to change it is parallel
evaluation of expressions, which is a long way off, so you probably
don't have any near-term reason to worry.  But ...)

Consider sticking the nextval() into a WITH.

            regards, tom lane

Re: VALUES() evaluation order

From
Steve Atkins
Date:
On Jan 13, 2013, at 2:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Steve Atkins <steve@blighty.com> writes:
>> Is the order in which the expressions in a VALUES() clause defined?
>> I'm doing this: INSERT INTO foo (a, b) VALUES (nextval('bar'), currval('bar'))
>
>> It works fine, but I'm wondering whether it's guaranteed to work or whether I'm relying on an artifact of the
implementation.
>
> I'd say it's an artifact.  It probably does work reliably at the moment,
> but if we had a reason to change it we'd not feel much compunction about
> doing so.  (The most obvious potential reason to change it is parallel
> evaluation of expressions, which is a long way off, so you probably
> don't have any near-term reason to worry.  But ...)
>
> Consider sticking the nextval() into a WITH.

Thanks. WITH it is, then.

Cheers,
  Steve

Re: VALUES() evaluation order

From
"Daniel Verite"
Date:
    Tom Lane wrote:

> Consider sticking the nextval() into a WITH.

This is also a reminder that PG's nextval() differs on this from
the SQL standard, which says:

"If there are multiple instances of <next value expression>s
specifying the same sequence generator within a single
SQL-statement, all those instances return the same value for
a given row processed by that SQL-statement"

According to that sentence, we would write
INSERT INTO table(c1,c2) VALUES(nextval('s'), nextval('s'))
and get the same value in c1 and c2.
But we know that's not how nextval works in PG.
However that's how it work in Oracle, for instance.

To avoid depending on the evaluation order, or for
the sake of porting SQL queries from other DBs, it
would be nice to have in PG an alternate nextval operator
that would comply with this "one-value per-row" rule.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org