Why do we avoid auto-incrementing db columns?

Could someone either clue me in or point to a prior
discussion about
why Drupal avoids using auto-incrementing database columns? 
If it is
database compatibility, which versions of which databases
that we
support do not have it?

Thanks,

Barry

SEPARATE COUNTERS ARE FAR MORE STANDARD. MYSQL IS WEIRD BY
HAVING
AUTO-INCREMENT COLUMNS BUT NOT COUNTERS.

NOW, THERE ARE A FEW THINGS WE COULD DO TO SPEED UP DRUPAL'S
MECHANISM
FOR EMULATING COUNTERS IN MYSQL...

BARRY JASPAN WROTE:
> COULD SOMEONE EITHER CLUE ME IN OR POINT TO A PRIOR
DISCUSSION ABOUT
> WHY DRUPAL AVOIDS USING AUTO-INCREMENTING DATABASE
COLUMNS?  IF IT IS
> DATABASE COMPATIBILITY, WHICH VERSIONS OF WHICH
DATABASES THAT WE
> SUPPORT DO NOT HAVE IT?
> 
> THANKS,
> 
> BARRY
> 

Here's my guess:
When inserting a node, there are several operations that
take place, and 
you want them all to use a persistent $node->nid .
Since few node creations can occur in the same time, you
want to know 
which one is YOUR node.
Fetching an nid/tid/... from the sequence table makes sure
each one 
works in a safe environment, where all IDs are known.

...But that's just a guess 



Barry Jaspan wrote:

> Could someone either clue me in or point to a prior
discussion about
> why Drupal avoids using auto-incrementing database
columns?  If it is
> database compatibility, which versions of which
databases that we
> support do not have it?
>
> Thanks,
>
> Barry
>
>
>   

On Apr 25, 2007, at 10:15 AM, Barry Jaspan wrote:

> Could someone either clue me in or point to a prior
discussion about
> why Drupal avoids using auto-incrementing database
columns?  If it is
> database compatibility, which versions of which
databases that we
> support do not have it?

The point isn't the databases we support, it's the databases
we want  
to support.

Barry,

I happened to discuss this with Greg Knaddison yesterday.
MySQL 4 does not support auto-incrementing columns. I
believe  
Drupal.org uses MySQL 4, and one reason it has not been
upgraded  
could be to avoid complications caused by changes in the way
 
information, particularly international characters, are
stored  
between 4 and and later versions.


Ezra Gildesgame

Developer
pingVision
1350 Pine Street, Suite 1
Boulder, CO 80302-4847
303.415.2559

On Apr 25, 2007, at 8:15 AM, Barry Jaspan wrote:

>
> Could someone either clue me in or point to a prior
discussion about
> why Drupal avoids using auto-incrementing database
columns?  If it is
> database compatibility, which versions of which
databases that we
> support do not have it?
>
> Thanks,
>
> Barry
>

On Wednesday 25 April 2007 10:29, Ezra B. Gildesgame wrote:
> I happened to discuss this with Greg Knaddison
yesterday.
> MySQL 4 does not support auto-incrementing columns.

I must respectfully disagree. I happened to have a copy of
the MySQL
reference book from version 3.22 sitting on my bookshelf,
and AUTO_INCREMENT
was supported even then.  Never mind
the question of why I still have such
an old book....

I also checked a PostgreSQL reference book, and they offer a
SERIAL
column type that works very similarly to the AUTO_INCREMENT
in MySQL. I don't
know how far back this support goes; my book is for PG
version 8.something and
was copyright 2006.

I can't comment on the Microsoft SQL Server implementation
of this, if any,
because I don't have any manual for that here.

Scott

-- 
------------------------------------------------------------
-------------------
Syscrusher (Scott Courtney)          Drupal page:   http://drupal.org/user/91
84
syscrusher at 4th dot com            Home page:     http://4th.com/   

I'd like to point out this is a really discussion about
mysql, not
about other database. Postgresql Drupal installations
haven't used a
sequences table ever, IIRC. As long as each database can
implement
some equivalent of db_next_id() -- be it by a sequences
table, auto
incremented values, Postgres's CURVAL() function, whatever
-- Drupal's
db abstraction will work.

So let's rephrase: do the currently supported versions of
mysql all
support some sort of db_next_id() functionality? What about
Drupal 6?

If so, the sequences table can be dropped in favor of
auto_increment.

-M

Hi everyone,

I'm PostgreSQL maintainer for Drupal and since people are up
in the air
about what is going on down in the DB layer re autoincrement
and serial
fields I figured i'd shed some light. Many people have
already stated
this stuff in earlier mails and hopefully getting the info
in one big
pile will make it more concrete.

PostgreSQL does implement atomic sequencing with the serial
and
bigserial types. MySQL does the same with the auto_increment
field
property. These are used in Drupal to produce a guaranteed
unique ID
for tables using them. Implementing this in PHP will
certainly generate
non-unique IDs unless you lock tables thus introducing a
massive
performance penalty.

We use db_next_id() in Drupal to provide a db-independent
method of
getting the next unique ID into the application for use just
after the
insert operation on the DB. The performance hit of using a
sequence is
next to nothing considering all that's involved is a mutex
lock, read,
increment and mutex unlock. Making this more efficient is
simply not
possible.

Sure there are two different use cases for insertions: those
where you 
need the ID afterwards (e.g node) and those you don't (e.g
watchdog). 
If you want to write code without the use of db_next_id()
then do an 
INSERT and omit the id column. I don't see anyone wanting to
spend 
effort patching Drupal core to gain 0.01% efficiency across
the 
application.

/me turns his torch off

-- 
Sammy Spets
Synerger Pty Ltd
http://synerger.com

On 25-Apr-07 10:15, Barry Jaspan wrote:
> 
> Could someone either clue me in or point to a prior
discussion about
> why Drupal avoids using auto-incrementing database
columns?  If it is
> database compatibility, which versions of which
databases that we
> support do not have it?
> 
> Thanks,
> 
> Barry
> 

Sammy Spets <sammys-drupalsynerger.com> writes:
> PostgreSQL [has] serial and bigserial types. MySQL
[has] auto_increment
> field property. These are used in Drupal to produce a
guaranteed
> unique ID for tables using them.
> 
> We use db_next_id() in Drupal to provide a
db-independent method of
> getting the next unique ID into the application for use
just after the
> insert operation on the DB.

I interpret this to mean, in your opinion at least, that (a)
Drupal
does officially condone and support the use of
auto-incrementing
columns and (b) it also provides db_next_id() for those
situations
where having it is simply more convenient or efficient than
letting
the database do the id generation.

Is that correct?  Is this the official core policy on this
subject?

> I'm PostgreSQL maintainer for Drupal...

FYI, the Schema module now supports MySQL and PostgeSQL for
table and
index creation from the same schema data structure.  I
discovered the
pgsql's use of the serial type during the implementation
which is what
led to my initiating this thread.  Schema now supports a
'serial'
column type which maps to auto_increment for MySQL or
{,big}serial for
PostgreSQL.

I'd be very happy to have you review the pgsql support. 
Schema is
currently in contrib.

Thanks,

Barry