Just to follow up on this, after some experimentation it looks like using PKs generated by the database can be much slower when doing bulk inserts, because Cayenne doesn't (can't?) do a batch bind to execute the inserts as a batch and instead has to execute them one by one. With a large batch of row for the same entity this can make a huge difference. So moving to generated PKs might not be the best idea for many use cases.
On Wed, May 29, 2019 at 4:45 PM Hugi Thordarson <h...@karlmenn.is> wrote: > /me instantly starts modifying DBs. > > Thanks for pointing this out John! > > - hugi > > > > On 29 May 2019, at 15:23, John Huss <johnth...@gmail.com> wrote: > > > > Doh! Turns out Cayenne already supports this for Postgres. Who knew! :-P > > > > 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - INSERT INTO > test > > (payload) VALUES (?) > > > > 2621 [main] INFO org.apache.cayenne.log.JdbcEventLogger - [bind: > > 1->payload:'test'] > > > > 2632 [main] INFO org.apache.cayenne.log.JdbcEventLogger - Generated PK: > > test.id = 1 > > > > On Tue, May 28, 2019 at 12:30 PM Andrus Adamchik <and...@objectstyle.org > > > > wrote: > > > >> Thanks for the pointer. If PG passes generated PK back through the JDBC > >> driver and we can read it after an INSERT, we most definitely should. > >> Creating extraneous objects in DB for the sake of PK generation feels > old. > >> > >> Andrus > >> > >>> On May 28, 2019, at 6:47 PM, John Huss <johnth...@gmail.com> wrote: > >>> > >>> Postgresql also recently improved their support in this area (in > version > >>> 10). It's more like syntactic sugar on top of things you could already > >> do, > >>> but I'd be interested to know if it can be used by Cayenne in the same > >> way > >>> as the MySQL auto-increment columns. > >>> > >>> The DDL syntax is supposedly an SQL standard: > >>> > >>> CREATE TABLE test_new ( > >>> id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, > >>> payload text > >>> ); > >>> > >>> > >>> On Mon, May 27, 2019 at 1:49 AM Andrus Adamchik < > and...@objectstyle.org> > >>> wrote: > >>> > >>>> Looks like Oracle started supporting auto-increment columns since 12c: > >>>> > >>>> > >>>> > >> > https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-key/ > >>>> > >>>> We should try using that in the adapter. > >>>> > >>>> Andrus > >> > >> > >
