Hi Andrus, I didn't understand your #1 example.
#2 is doable, but would make the commit code more complex and I'm not sure it is worth the added burden to Cayenne. #3 I'm pretty sure can be done with deferrable constraints (such as PostgreSQL supports) if you assign the keys up-front. The mandatory FK won't be checked until the transaction is committed. I know in the past I've had to make several schema design changes due to limitations in MySQL (and sometimes Cayenne) because of constraints/etc. There will simply be some cases that aren't easily solved by Cayenne due to other external choices, and that's OK -- we shouldn't try to solve those. But pre-assigning the PKs if using sequences or auto-PK support might be trivial enough for us to do and, if your DB supports deferrable constraints, solve a few of those issues. Thanks, mrg On Thu, Dec 10, 2015 at 1:43 AM, Andrus Adamchik <and...@objectstyle.org> wrote: > 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 smarter. > > 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. > > Andrus > > > On Dec 8, 2015, at 2:53 PM, Michael Gentry <mgen...@masslight.net> > 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 <johnth...@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 <amania...@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 > >>> > >> > >
