Frank Millman wrote: > Dale Strickland-Clark wrote: > > Now that OIDs have been deprecated in PostgreSQL, how do you find the key of > > a newly inserted record? > >
> > I used to use 'select lastval()', but I hit a problem. If I insert a > row into table A, I want the id of the row inserted. If it is a complex > insert, which triggers inserts into various other tables, some of which > may also be auto-incrementing, lastval() returns the id of the row last > inserted into any auto-incrementing table. > > I therefore use the following - > cur.execute("select currval('%s_%s_Seq')" % (tableid, columnid) Aren't both of these inherently racey? It seems to me like you need some kind of atomic insert-and-return-id value; otherwise you don't know if another thread/process has inserted something else in the table in between when you call insert and when you select lastval/currval. Or does postgres have some transactional atomicity here? I'm very interested in an answer to the OP's question as well. As far as I can tell, the best you can do is make the ID a primary key, select the nextval _before you insert, explicitly set it on insert, and then you'll get an error if you lost a race and you can select nextval, set it, and try again (repeat until success). That's nasty and I'm sure there's a real solution but I haven't found it yet. -- http://mail.python.org/mailman/listinfo/python-list