cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <johnth...@gmail.com>
Subject Re: using database generated PK
Date Thu, 03 Dec 2015 16:20:06 GMT
The other reason for the auto_pk_support is that a custom db adaptor is
needed to use native pks, and that isn't always immediately available.
On Wed, Dec 2, 2015 at 9:08 PM John Huss <johnthuss@gmail.com> wrote:

> 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
> anyhow.
>
>
> On Wed, Dec 2, 2015 at 3:30 AM Aristedes Maniatis <amaniatis@apache.org>
> wrote:
>
>> 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] https://issues.apache.org/jira/browse/CAY-2040
>>
>>
>> --
>> -------------------------->
>> Aristedes Maniatis
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
>>
>

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