cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <>
Subject Re: using database generated PK
Date Thu, 03 Dec 2015 03:08:52 GMT
The AUTO_PK_SUPPORT table exists only to support PK generation for DBs that
do not have native support for it built in.  However, in this day and age
any database worth using has this built in.  I've run production apps with
Postgres, MySQL, FrontBase, and Interbase / Firebird, and all of them have
native PK generation.  Heck, even Derby, H2, and HSQLDB support it.

The main advantage of a native solution is that you know it works.  If you
ever used native PK generation the AUTO_PK_SUPPORT table feels very
kludgy.  It works (I guess?), but it's not ideal.  A native solution is
faster and simpler.  It handles transactions better.  Most DBs (all?) do PK
generation outside of transactions, so incrementing the sequence always
persists regardless of transaction rollbacks or commits.

Performance is better because a native DB implementation is able to
implement it with as little locking as necessary, which is certain to be
much faster that the equivalent set of SQL commands.

Another advantage is being able to use the same PK generators to insert
data directly with SQL, which is sometimes necessary.  Also, maintenance is
easier since there are simple built in functions to query or manipulate the
sequence value.

MySQL's implementation is different than the others I've used.  There's is
more magical.  The component parts aren't accessible to developers.  The
engine just magically chooses the next highest PK value when rows are
inserted without giving any idea where this number comes from or how it is
chosen.  This has some benefits since the PKs don't have to be explicitly
generated ahead of time.  I don't know if it's better, but it's different

On Wed, Dec 2, 2015 at 3:30 AM Aristedes Maniatis <>

> I've always used the AUTO_PK_SUPPORT approach to PK generation, all the
> way back from WebObject EOF days many many years ago.
> However a recently discovered bug in this approach [1] and how it
> intersects with transactions may require use to think about going over to
> database generated primary keys with a auto-increment column. For
> developers not using an ORM this is pretty common I understand.
> What are the downsides to that approach? Is performance, reliability and
> everything else just the same? What problem was AUTO_PK_SUPPORT originally
> designed to solve?
> Cheers
> Ari
> [1]
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message