Re: portable DBAPI auto-increment

On Thu, Apr 07, 2011 at 04:40:29PM -0400, Mark Sienkiewicz wrote:

> I have a portability issue that I'm not sure what to do with: I need
> an automatically unique column, but I don't see a portable way to ask
> for it in my code.

SELECT automatically_unique_column FROM table_in_question

> For my example, I'm going to use column k as the key and v as the
> value, though in real life there are another 10 or so columns.
>
> In sqlite and mysql, I can do
> c.execute("insert into foo ( v ) values ( 'xyz' )")
> k = c.lastrowid

.lastrowid is an implementation detail depending on the
database to keep a unique column on your rows behind your back.

PostgreSQL used to do that (the row OID) but that's been
deprecated because it serves no known purpose that cannot
easily be served otherwise.

> In psycopg2, I find that lastrowid is always 0, even though it is
> making rows in the table with k=1, k=2, etc.
>
> I did some searching and found that the value of lastrowid is not
> taken from the autoincrementing column, but is some non-unique entity
> called an OID.

OIDs *are* unique per table.

> So, my question is: Is there a more portable way to do this that I'm
> not thinking of?

Maybe second thougth needs to be given to whether .lastrowid
is the right tool for the job.

> Secondarily, I see the the postgres documentation recommends not
> using the OID in your own tables. What use is the OID? Why doesn't
> c.lastrowid to return the value of the serial column, or None if
> there isn't one?

Consider this table:

create table temp (
pk serial primary key,
pk2 serial unique,
pk3 integer
unique
not null
) with OIDS;

Which value would you expect psycopg2 to return as
.lastrowid ? They *all* fulfill the criteria: uniquely
identify the row (.oid, .pk, .pk2, .pk3).

Using RETURNING does not cut it either because one can
return nearly arbitary data.

Now, a case could be made to let the user *tell* psycopg2
what to use for .lastrowid on any given table. That's not as
trivial as it sounds, however.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346