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.

 

[Tim N. van der Leeuw]

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

 

[GHUM]

Dale,

Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
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.connect(....)
cs=cn.cursor()

a) get id first, then insert
cs.execute("select nextval('feuser_id_user_seq'::regclass)")
newid=cs.fetchone()[0]

cs.execute("insert into feuser (id_user, name) values (%(id_user)s,
%(name)s)",
dict(id_user=newid, 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("select insfeuser(%(name)s)", dict(name="Karl Napf"))
newid=cs.fetchone()[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]

Hi,

select lastval();

Thanks, that was useful.

 

[Frank Millman]

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.

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)

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]

I therefore use the following -
cur.execute("select 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]

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.

 

[Steve Holden]

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

 

[Frank Millman]

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

 

[sjdevnull]

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

I totally missed it, my bad. Thanks!

For some reason Google Groups stuck a '>' at the beginning, so you may
have thought that it related to a previous message

Probably due to the vagaries of mbox format which uses "From " at the
beginning of a line to indicate the start of a new message; lots of
mail systems prepend "From " at the start of a line in the body with
">". :-/

 

[Stuart Bishop]

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)

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

If you are using a modern PostgreSQL (8.1 for sure, maybe 8.0), this is
better spelt:

cur.execute("SELECT currval(pg_get_serial_sequence(%s, %s))" % (
tableid, columnid))

(Assuming of course your table name and column name don't contain odd
characters like = or ' or ., in which case you need to properly quote them).

The reason it is better is that in odd cases the sequence name will not
always be %(tableid)s_%(columnid)s_seq, such as after you have renamed a table.

--
Stuart Bishop <[email protected]>
http://www.stuartbishop.net/


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFFGy6/AfqZj7rGN0oRAiPJAJ9wzSl9Z9Bl1UoQGDTfB0YA+y4xPACfdtj4
MJy/lI3QniFa0PosHY9763Q=
=QXg3
-----END PGP SIGNATURE-----

 

[Steve Holden]

Stuart Bishop wrote:
[...]

If you are using a modern PostgreSQL (8.1 for sure, maybe 8.0), this is
better spelt:

cur.execute("SELECT currval(pg_get_serial_sequence(%s, %s))" % (
tableid, columnid))

(Assuming of course your table name and column name don't contain odd
characters like = or ' or ., in which case you need to properly quote them).

The reason it is better is that in odd cases the sequence name will not
always be %(tableid)s_%(columnid)s_seq, such as after you have renamed a table.

Thank you! I've been looking for this for about six months - it helps to
provide portability, and my current techniques relied on standard naming
of the sequences.

regards
Steve