PostgreSQL order of evaluation

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

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?

Patches list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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