PostgreSQL, psycopg2 and OID-less tables

[sjdevnull at yahoo.com]

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.