[UPHPU] Postgres Insert ID

[Joseph Scott]

On Oct 22, 2008, at 5:51 PM, Sean Thayne wrote:

>>
>> not something to make a big deal over unless you are trying to  
>> write an app
>> that supports both PostgreSQL and MySQL....
>>
>
> I'm working on making a php app work well with postgres...
>
> in general sequences are much more efficient that auto_increment  
> techniques
>
> How are they more efficient?


Go back and read my reply, you didn't quote the part where I explained  
why they are more efficient :-)  Ok, one more time:

Having something separate from tables makes the needed features much  
easier and faster to implement.  For general auto number features, no  
matter how they are implemented, follow a similar process:

	- Request for new number
	- Lock on the entity handing out numbers to make sure there are no  
duplicates
	- Give out the new number
	- Release the lock

If you've got to go digging through the table in order to that it's  
more work that having a smaller, simpler object whose only purpose is  
to hand out numbers (no potentially storing thousands/millions of rows).

There are also some specific limitations with how MySQL implements  
auto_increment.  For example, you are limited to one per table:

	http://dev.mysql.com/doc/refman/5.1/en/create-table.html

When doing a multi-row insert you only get the id for the first record  
inserted:

	http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Again, not usually the end of the world, just different.  I'm a fan of  
both, having used PostgreSQL (among a few other DBs) in a previous job  
and using MySQL in my current job.

--
Joseph Scott
joseph at randomnetworks.com
http://joseph.randomnetworks.com/