PostgreSQL, psycopg2 and OID-less tables
frank at chagford.com
Sun Sep 17 08:18:30 CEST 2006
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.
Did you read my extract from the PostgreSQL docs -
"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."
For some reason Google Groups stuck a '>' at the beginning, so you may
have thought that it related to a previous message, but it was actually
part of my reply and refers specifically to 'select currval()'.
More information about the Python-list