PostgreSQL, psycopg2 and OID-less tables

[Frank Millman]

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()'.

Frank