Re: [GENERAL] Re: PostgreSQL order of evaluation

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