PostgreSQL, psycopg2 and OID-less tables

[Steve Holden]

sjdevnull at yahoo.com wrote:
> 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?
> 
currval(sequence) is alleged to be connection-specific in the PostgreSQL 
documentation for currval: """Return the value most recently obtained by 
nextval for this sequence in the current session. (An error is reported 
if nextval has never been called for this sequence in this session.) 
Notice that because this is returning a session-local value, it gives a 
predictable answer whether or not other sessions have executed nextval 
since the current session did."""

> 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.
> 
The real solution has been given already: you just haven't brought 
yourself to believe it yet ;-)

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb       http://holdenweb.blogspot.com
Recent Ramblings     http://del.icio.us/steve.holden