cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <>
Subject Re: using database generated PK
Date Thu, 10 Dec 2015 06:43:33 GMT
Assigning a PK before commit is a good feature on its own. Still not sure it will solve the
cyclic graphs issue. Consider the following cases:

1. Cycles between entities, but not between the objects. Can be handled by making Ashwood

2. Optional FK, which can be handled via INSERT then UPDATE:

* insert department with null manager
* insert a person as a department member
* then update department to set the person as a manager

3. Same as #2, but department.manager_id is mandatory. There's no valid operation ordering.

So #1 and #2 do not require us to know the PK upfront. And #3 can't be solved even if we do.


> On Dec 8, 2015, at 2:53 PM, Michael Gentry <> wrote:
> Switched to dev...
> There are two basic types of database-generated keys:
> 1) Auto-increment (such as MySQL and H2)
> 2) Sequences (such as PostgreSQL and Oracle)
> #1 has to be done row-by-row per insert.
> #2 can handle "batches" (you define how many PKs to cache) and is similar
> in operation to AUTO_PK_SUPPORT.
> In theory, #2 should be faster since the PKs/FKs can be assigned in advance
> before contacting the DB instead of having to wait on a reply back from the
> DB.  Remember, Cayenne can't insert a record with a FK to a PK until that
> PK is known, so it has to get the auto-increment PK value from the DB and
> fill in the FK before it can do another insert.
> One limitation we currently have is handling cyclic graphs:
> A:FK -> B:PK
> B:FK -> C:PK
> C:FK -> A:FK
> With auto-increment, there isn't a good solution to this that I can think
> of, especially if DB constraints are in play (if an FK is required, you
> can't insert a NULL FK, then do an update in the same transaction after you
> know it).  With sequences (or AUTO_PK_SUPPORT), this would actually be
> possible, because we generate the PKs prior to the inserts, therefore we
> can assign all the FKs prior to inserts, too.  However the last time I
> looked at the code, I don't believe it is smart enough to have two
> different paths of execution, so we still can't handle cyclic graphs (plus
> Ashwood fails).
> Any thoughts on the value of investigating changing this?
> Thanks,
> mrg
> On Wed, Dec 2, 2015 at 10:08 PM, John Huss <> 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 <>
>> 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]
>>> --
>>> -------------------------->
>>> Aristedes Maniatis
>>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

View raw message