[Zope] Can I capture results from insert in Z SQL methods?

[Tino Wildenhain]

Hi Jim, hi Skip,

--On Sonntag, 3. November 2002 12:30 -0500 Jim Gallacher 
<jg.lists@sympatico.ca> wrote:

> Hi Skip,
>
>
> Skip Montanaro wrote:
>>     Andrew> This is really a general SQL problem rather than anything to
>>     do Andrew> with Zope/ZSQL.
>>
>> So ZSQL methods don't provide any special syntactic sugar other than
>> <dtml-sqlvar>?

Its nothing magic with this sqlvar. It only assembles the query which is 
then
send to the DB - as one block of text.
>>
>>     Andrew> Then do a select nextval('whatever'); to get the next id
>>     which Andrew> can then be used for the inserts.
>>
>> Thanks, I'll check it out.  This seems (naively) like it would be a race
>> to me though.  Let's say I do this:
>>
>>     insert into phone ( ... ) values ( ... );
>>
>> The new row in phone got a unique id (I do use the serial type for my
>> primary keys).  Now, I need the id that row got to use in another insert.
>> If I do something like:
>>
>>     insert into contact (
>>         ...
>>         phone,
>>         ...
>>     )
>>     values (
>>         ...
>>         (select currval(phone_id_seq)),
>>         ...
>>     );
>>
>> That will get the id of the record I just inserted assuming someone else
>> didn't sneak in and insert another row in the phone table before I
>> queried phone_id_seq.

You should consider consulting your database manual. This sequcence 
nextval/currval
is an FAQ. currval is only valid after nextval in the same transaction.
So currval, used correctly after nextval will under any circumstance give 
you the
same value as your last nextval in your transaction, no matter how many 
nextval()
calls are made in another transactions in the same time.

Here is the relevant section from Postgres documentation:
--------- cite --------
The available sequence functions are:

nextval
Advance the sequence object to its next value and return that value. This 
is done atomically: even if multiple server processes execute nextval 
concurrently, each will safely receive a distinct sequence value.

currval
Return the value most recently obtained by nextval for this sequence in the 
current server process. (An error is reported if nextval has never been 
called for this sequence in this process.) Notice that because this is 
returning a process-local value, it gives a predictable answer even if 
other server processes are executing nextval meanwhile.

setval
Reset the sequence object's counter value. The two-parameter form sets the 
sequence's last_value field to the specified value and sets its is_called 
field to true, meaning that the next nextval will advance the sequence 
before returning a value. In the three-parameter form, is_called may be set 
either true or false. If it's set to false, the next nextval will return 
exactly the specified value, and sequence advancement commences with the 
following nextval. For example,


SELECT setval('foo', 42);           Next nextval() will return 43
SELECT setval('foo', 42, true);     Same as above
SELECT setval('foo', 42, false);    Next nextval() will return 42

The result returned by setval is just the value of its second argument.


Important: To avoid blocking of concurrent transactions that obtain numbers 
from the same sequence, a nextval operation is never rolled back; that is, 
once a value has been fetched it is considered used, even if the 
transaction that did the nextval later aborts. This means that aborted 
transactions may leave unused "holes" in the sequence of assigned values. 
setval operations are never rolled back, either.

If a sequence object has been created with default parameters, nextval() 
calls on it will return successive values beginning with one. Other 
behaviors can be obtained by using special parameters in the CREATE 
SEQUENCE command; see its command reference page for more information.
-------------------

HTH
Tino Wildenhain