Use of nextval, currval

Поиск
Список
Период
Сортировка
Искать

Use of nextval, currval

От:
Matt Clark <mclark@rushe.aero.org>
Дата:
I'm attempting to convert a code which uses Oracle to Postgres.

I am concerned about a couple of points in the SQL, which I should be able to do 
in Postgres.

Say I have defined a sequence DataId;

The Oracle uses the sequence as follows:

insert into TABLE (id1,.......,id2) 
            values (DataId.NextVal,......,DataId.Currval);
            
I believe that I can replace this in Postgres with:

insert into TABLE (id1,.......,id2)
            values (nextval('DataId'),.....,currval('DataId');
            
            
What I think is expected is that the values of id1 and id2 will be equal.
I have been told that Oracle will guarantee this; will that be assumption be 
true for Postgres, also? (My concern is that, with multiple/many users of the 
same database, there may be instances where DataId increments before the currval 
happens, such that it's possible that id1 != id2, above.)

Is there a simple way to write the SQL such that I can only use nextval once, 
keeping its value for both id1 and id2?  (I have no idea why the values are 
duplicated.....)

For that matter, is that a realistic assumption for Oracle's treatment of 
sequences?

Thanks,

Matt

Re: Use of nextval, currval

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Josh Berkus"  writes:
>> I believe that I can replace this in Postgres with:
>> 
>> insert into TABLE (id1,.......,id2)
>> values (nextval('DataId'),.....,currval('DataId');
>> 
>> What I think is expected is that the values of id1 and id2 will be
>> equal.

> This is correct.

Well ... it's relying on the assumption that the elements of the VALUES
list will be evaluated left-to-right.  Which is true at the moment,
and I don't really expect it to change, but it still could be called an
unsafe dependency on undocumented implementation details.  I doubt you
will find anything in the SQL spec that requires implementations to
behave that way.

			regards, tom lane

Re: Use of nextval, currval

От:
"Josh Berkus" <josh@agliodbs.com>
Дата:
Matt,

> I'm attempting to convert a code which uses Oracle to Postgres.
> 
> I am concerned about a couple of points in the SQL, which I should be
> able to do 
> in Postgres.
> 
> Say I have defined a sequence DataId;
> 
> The Oracle uses the sequence as follows:
> 
> insert into TABLE (id1,.......,id2) 
>             values (DataId.NextVal,......,DataId.Currval);
>             
> I believe that I can replace this in Postgres with:
> 
> insert into TABLE (id1,.......,id2)
>             values (nextval('DataId'),.....,currval('DataId');
>             
>             
> What I think is expected is that the values of id1 and id2 will be
> equal.

This is correct.   Please post if you for some reason find them not
equal.

-Josh Berkus
FAQ