PostgreSQL, psycopg2 and OID-less tables

[Dale Strickland-Clark]

Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
a newly inserted record?

I've tried three Python client libraries, including psycopg2, and where they
support cursor attribute 'lastrowid' (Python DB API 2.0), it is always
zero.

Anyone figured this out?

Thanks.
--
Dale Strickland-Clark
Riverhall Systems - www.riverhall.co.uk

[Tim N. van der Leeuw]

Dale Strickland-Clark wrote:

Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
a newly inserted record?

I've tried three Python client libraries, including psycopg2, and where they
support cursor attribute 'lastrowid' (Python DB API 2.0), it is always
zero.

Anyone figured this out?

Hi,

It's of course strictly a PostgreSQL question, but have you tried

select lastval();

?

According to my reading of the postgress manuals, that should do the
trick.

Cheers,

--Tim

Thanks.
--
Dale Strickland-Clark
Riverhall Systems - www.riverhall.co.uk

[GHUM]

Dale,

Now that OIDs have been deprecated in PostgreSQL, how do you find the keyof
a newly inserted record?

using OIDs as primary key was no good idea for some PostgreSQL versions
allready ... i.e. they really make dump & restore much more
challenging.

So usually us have something along:

CREATE TABLE feuser
(
id_user serial NOT NULL,
name text,
CONSTRAINT feuser_pkey PRIMARY KEY (id_user),
CONSTRAINT feuser_name_key UNIQUE (name)
)
WITHOUT OIDS;

which automatically creates a sequence for you and rewrites the serial
"id_user" with a default of

nextval('feuser _id_user_seq':: regclass)

So, to get the key of a record inserted, basically there are two
methods. (from now on "cs" is a DB-API 2.0 compliant cursor object,
i.e. from psycopg2

cn=psycopg2.con nect(....)
cs=cn.cursor()

a) get id first, then insert
cs.execute("sel ect nextval('feuser _id_user_seq':: regclass)")
newid=cs.fetcho ne()[0]

cs.execute("ins ert into feuser (id_user, name) values (%(id_user)s,
%(name)s)",
dict(id_user=ne wid, name="Karl Napf")

cs.commit()
-now newid contains your new id.

b) create a serverside function on PostgreSQL:
CREATE OR REPLACE FUNCTION insfeuser (text)
RETURNS integer AS
$BODY$
DECLARE
result int4;
BEGIN

select nextval('feuser _id_user_seq':: regclass) into result;
insert into feuser (id_user, name) values (result, $1);

RETURN result;
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

within python:
cs.execute("sel ect insfeuser(%(nam e)s)", dict(name="Karl Napf"))
newid=cs.fetcho ne()[0]

Hope that helps,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607

[Dale Strickland-Clark]

Hi Harald

Thanks for that, somewhat comprehensive, answer.

--
Dale Strickland-Clark
Riverhall Systems - www.riverhall.co.uk

[Dale Strickland-Clark]

Tim N. van der Leeuw wrote:

>
Hi,

select lastval();

Thanks, that was useful.
--
Dale Strickland-Clark
Riverhall Systems - www.riverhall.co.uk

[Frank Millman]

Dale Strickland-Clark wrote:

Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
a newly inserted record?

I've tried three Python client libraries, including psycopg2, and where they
support cursor attribute 'lastrowid' (Python DB API 2.0), it is always
zero.

Anyone figured this out?

Thanks.
--
Dale Strickland-Clark
Riverhall Systems - www.riverhall.co.uk

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("se lect currval('%s_%s_ Seq')" % (tableid, columnid)

where tableid is the specific table I want (in this example, table A),
and columnid is the column specified as the auto-incrementing one.

>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."

Frank Millman

[Jim]

Frank Millman wrote:

I therefore use the following -
cur.execute("se lect currval('%s_%s_ Seq')" % (tableid, columnid)

I use this also (although isn't it right that sometimes the name of the
sequence is not so straightforward ? for instance, isn't there a limit
on the number of chars?).

Can anyone say what is an advantage of the two nextval() solutions
described earlier in this thread over the currval() solution listed
here?

Jim

[sjdevnull]

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("se lect 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.

[Steve Holden]

sj*******@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("se lect 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(sequenc e) 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 [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Frank Millman]

sj*******@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("se lect 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