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