Re: sequences and Postgresql

[prev] [thread] [next] [Date index for 2004/07/08]

On Wed, 7 Jul 2004, Takes Tea at Half Past Three wrote:

> 
> Where does it say that? Do you mean this:
> >If you are using a database with AUTO_INCREMENT (e.g. MySQL) then 
> >you do not need this, and any call to create() without a primary key 
> >specified will fill this in automagically.

Yes, and the paragraph above it...
> 
> I suspect that the "magic" is right for MySQL, but wrong for PG.

well, it certainly won't let the thing alone and let the dbms take care of 
it.
> 
> 
> >Of course the optimal performance is to leave this column out entirely and
> >let Postgresql take care of it.
> 
> I use this SQL...
> 
> CREATE SEQUENCE seq_comment;
> CREATE TABLE comment (
> 	id		INT4	DEFAULT nextval('seq_comment')	NOT NULL,
> );
> 
> and then use
> 
> __PACKAGE__->sequence('seq_comment');
> 

Interesting...
but with the NOT NULL in there CDBI won't leave it alone.

> 
> Which should do about the same thing as using SERIAL (which I wasn't 
> aware of. I should read more about the SQL variations in PG).

I find the _Postgresql Developer's Handbook_ (Sams, 2002) helpful. 
Momjian's book was ok but pretty basic.
> 
> What happens if you use this?
> 
> __PACKAGE__->sequence('categories_category_id_seq');
> 
> 
That maked _prepopulate_id work correctly and in fact now that I look 
closely at the CDBI documentation is in fact what was specified.
Sigh. It still is non-optimal -- extra Perl code to run not to mention 
extra round-trips to the database.  For what I'm doing I'm in a low 
transaction-rate environment (my app isn't the only thing using the 
database; there's a RADIUS server hammering away with authentication , 
authorization and accounting ) so I'm ok...and I guess in a extremely 
high-volume system the overhead of CDBI is a problem. I haven't tried to 
run priceline.com on Perl with CDBI ...though I understand amazon.com uses 
HTML::Mason...

 > -- > Aneel Nazareth -- http://eye-of-newt.com/nazareth -- 
>