[SQLObject] Re: SQL Object's scalability

[Ian B. <ia...@co...>]

On Nov 12, 2003, at 1:04 PM, Scott Chapman wrote:
> Hi Ian,
> A while back I asked how you get the inserted ID of a record in 
> PostgreSQL and Luke sent back:
>
> Depends on the database/dbdriver. You'll find the implementation in 
> SQlObject/DBConnection.py, _queryInsertID() for each 
> DBConnection-class. Some Python db interfaces provide a non-standard 
> option for getting this from the cursor (MySQL, SQLite), some require 
> a second query of the database (Postgres using OIDs, for instance).
>
> I recently saw a post on the Postgres mailing list on this so I put up 
> your code as I was asking for clarification.  You might want to check 
> out the thread:
>
> http://archives.postgresql.org/pgsql-general/2003-11/msg00376.php
>
> especially the message:
>
> http://archives.postgresql.org/pgsql-general/2003-11/msg00565.php

Hmm... Does anyone else have thoughts on this?  Basically it's an issue 
that the query "SELECT %s FROM %s WHERE oid = %s" % (idName, table, 
c.lastoid()) may not be using an index on the oid= portion, which would 
be problematic.

At one time SQLObject used nextval to pre-allocate the ID, then did the 
insert (which is what Firebird does now, and what Oracle and Sybase 
would do).  The disadvantage of this is that you needed to know the 
name of the sequence, which would sometimes vary (and maybe there were 
truncation problems).  The disadvantage with the current scheme 
(besides the index) is that you need to define your ID field as 
"DEFAULT nextval('somesequence')" (or do that implicitly through 
SERIAL).

The current scheme is convenient, but I don't know if it's really worth 
it.  Should we go back to preallocating an ID through a 
nextval(sequence)?

Really, to facilitate non-integer IDs, it would be nice if we could 
define a generic ID generator (which may or may not access the 
database).  But I'm not really clear about all the use cases for that, 
so it's probably not a good idea to generalize that yet.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org