[Zope] q: How should I get a guaranteed unique id in Zope?

[Chris Kratz]

On Tuesday 15 January 2002 02:33 am, you wrote:
> I'm probably missing something in postgresql where I can do an insert
> and have it return whatever jobid it automatically generates.  On the
> Zope side, I'm using ZPsycoPg as my postgres connection.
>
> jpb

Hello Joe,

I don't know if you have found it yet, but in postgres if you create a table 
with the primary key of type serial, you are guarranteed a unique id.  This 
creates a sequence behind the scenes which the table pulls the next id from.  
You can then retrieve the inserted value by querying the sequence for the 
current value.  Since everything within a single request in zope is within a 
transaction (given that you don't try to roll your own transaction code which 
is a very bad thing unless you know what your doing...), you will always get 
back the id of the record you just inserted.  For simplicity, I usually put 
them in separate ZSQL statements (one does the insert and the second pulls 
the key for the inserted record), but you could include them within the same 
ZSQL statement if you would like.  It makes no difference in their execution 
if they are separate or together.

For example, if you create a table like this...

create table simpletable ( idnum serial primary key, comment text);

psql will return...

NOTICE:  CREATE TABLE will create implicit sequence 'simpletable_idnum_seq' 
for SERIAL column 'simpletable.idnum'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 
'simpletable_pkey' for table 'simpletable'
CREATE

or if you already created your table with a serial type, you can inspect the 
sequence name with 

\d simpletable
                               Table "simpletable"
 Attribute |  Type   |                         Modifier
-----------+---------+-----------------------------------------------------------
 idnum     | integer | not null default 
nextval('"simpletable_idnum_seq"'::text)
 comment   | text    |
Index: simpletable_pkey

Notice it uses the nextval function as a trigger to get the next available 
key value.  The sequence name again is in the double quotes.  If you want to 
create and add a sequence, you can do that, but it is a little more 
complicated.  See the postgres doc for instructions.

Now, since we know the sequence name 'simpletable_idnum_seq', we can place 
the following two statements into zope ZSQL objects:

insert into simpletable(comment) values('Some comment here');
select currval('simpletable_idnum_seq');

The second select calls a function which will always return the value of the 
last record you inserted.  The magic is that this will work across multiple 
threads (Zope and postgres keep things straight) regardless of the number of 
transactions, zope threads, or postgres threads that are currently running.

Hope that helps,

-Chris

-- 
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com