Обсуждение: PostgreSQL order of evaluation

I've been trying to find where the order of evaluation for SQL is
defined.  In short, I wounder if the following always gives the same
result:

        CREATE SEQUENCE counter
                start 1 increment 1 cache 1
                        minvalue 1
                        maxvalue 2147483647;
        SELECT NEXTVAL('counter'), NEXTVAL('counter');

        CREATE TABLE counts (
               value1 integer,
               value2 integer
        );
        INSERT INTO counts VALUES (NEXTVAL('counter'), NEXTVAL('counter'));

Will the first always return (1, 2) or are the SQL implementations
free to return (2,1).  Will every SQL implementation insert (3,4) in
the table, or will some insert (4,3).

Will PostgreSQL always be evaluating left-to-right?  Where in the
PostgreSQL manuals are this described?

BTW: I've implemented Oracles 'comment on' as functions.  Where should
  I send these functions and the perl tool to extract the comments?
--
##>  Petter Reinholdtsen  <##  |  pere@td.org.uit.no

>
> Date: Fri, 20 Aug 1999 01:25:54 +0200
> From: Petter Reinholdtsen <pere@hungry.com>
> Subject: PostgreSQL order of evaluation
>
> I've been trying to find where the order of evaluation for SQL is
> defined.  In short, I wounder if the following always gives the same
> result:
>
>         CREATE SEQUENCE counter
>                 start 1 increment 1 cache 1
>                         minvalue 1
>                         maxvalue 2147483647;
>         SELECT NEXTVAL('counter'), NEXTVAL('counter');
>
>         CREATE TABLE counts (
>                value1 integer,
>                value2 integer
>         );
>         INSERT INTO counts VALUES (NEXTVAL('counter'), NEXTVAL('counter'));
>
> Will the first always return (1, 2) or are the SQL implementations
> free to return (2,1).  Will every SQL implementation insert (3,4) in
> the table, or will some insert (4,3).
>

 Why are you so sure, that you may get numbers with differences of
one ? As I understand the backend caches several numbers in advance
and when running several backends you may also get numbers like
(1,10).

 After all: the only thing you can be sure: both numbers are unique !

 Marten

hi..

>> I've been trying to find where the order of evaluation for SQL is
>> defined.  In short, I wounder if the following always gives the same
>> result:
>>

<SNIP>

>>         INSERT INTO counts VALUES (NEXTVAL('counter'), NEXTVAL('counter'));
>>
>> Will the first always return (1, 2) or are the SQL implementations
>> free to return (2,1).  Will every SQL implementation insert (3,4) in
>> the table, or will some insert (4,3).
>>
>
> Why are you so sure, that you may get numbers with differences of
>one ? As I understand the backend caches several numbers in advance
>and when running several backends you may also get numbers like
>(1,10).
>
> After all: the only thing you can be sure: both numbers are unique !

this is only true if you have set CACHE to a value greater than one in your
sequence (and 1 is the default). but i still don't know if i'd trust it to
spit out two consecutive numbers all the time. especially with a lot of
activity. =)

my two cents worth of advice (if the goal is incremented numbers in pairs
of two) is to use a sequence that jumps by twos.. i.e.:

create sequence count_by_two increment 2 start 1;
INSERT INTO counts VALUES (NEXTVAL('counter'), CURRVAL('counter') + 1);

and no, currval doesn't seem to get fubarred when more than one backend
messes with the sequence (just tested that...)

this should get you whatchya want.


Aaron J. Seigo
Systems Analyst/Administrator