Thanks John. I will give it a try tomorrow. nobu
On 28 July 2016 at 20:49, John Huss <[email protected]> wrote: > Cayenne generates them like this: > > CREATE SEQUENCE mytable_seq > INCREMENT 20 > START 200; > > The start part is optional. > > On Thu, Jul 28, 2016 at 2:02 PM Harunobu Oyama <[email protected]> wrote: > > > Sorry, how should I create the sequence? If Cayenne increments the value > in > > blocks f 20? > > > > nobu > > > > > > On 28 July 2016 at 19:58, John Huss <[email protected]> wrote: > > > > > By default cayenne expects the sequence to be incremented in blocks of > > 20, > > > not by 1, so you have to specify that when you create the sequence. > > Using > > > serial columns is very very small improvement and since support is > still > > a > > > ways off you would be much better off just creating your sequences and > > > letting cayenne use them. > > > > > > > > > On Thu, Jul 28, 2016 at 1:53 PM Harunobu Oyama <[email protected]> wrote: > > > > > > > It seems "last_value" of "pk_asset" is not updated as expected. > > > > It gets incremented only once even if I create 3 asset records using > > > > Cayenne. > > > > It ends up violating asset_PKC constraint. > > > > > > > > > > > > The SQL is like following. > > > > > > > > create table "asset" ( > > > > "asset_id" bigserial not null > > > > , "name" character varying not null > > > > , "latitude" double precision > > > > , "longitude" double precision > > > > , constraint "asset_PKC" primary key ("asset_id") > > > > ) ; > > > > > > > > create sequence "pk_asset" > > > > > > > > I think it is better to change bigserial to bigint, but does it > matter? > > > > > > > > > > > > nobu > > > > > > > > > > > > > > > > > > > > On 28 July 2016 at 16:15, Michael Gentry <[email protected]> > wrote: > > > > > > > > > What do you mean by "out of sync"? Does it generate duplicate PKs? > > > How > > > > > did you create your sequence? (Can you show us the SQL for it?) > > > > > > > > > > Thanks, > > > > > > > > > > mrg > > > > > > > > > > > > > > > > > > > > On Thu, Jul 28, 2016 at 11:10 AM, Harunobu Oyama <[email protected]> > > > wrote: > > > > > > > > > > > If I do not create "pk_asset" sequence, Cayenne 4 throws an > > Exception > > > > > > saying it requires it. > > > > > > If I create "pk_asset" and let Cayenne 4 assigns the PK using > > > > "pk_asset", > > > > > > it looks working fine at a glance, but pk_asset's last_value > gets > > > out > > > > of > > > > > > sync, the next time we launch the program. > > > > > > nobu > > > > > > > > > > > > On 28 July 2016 at 16:05, Michael Gentry <[email protected]> > > > wrote: > > > > > > > > > > > > > Well, you can still use PostgreSQL's sequences. After all, > > that's > > > > what > > > > > > the > > > > > > > "serial" type inherently uses. No need to manually set the > PKs. > > > > > > > > > > > > > > mrg > > > > > > > > > > > > > > > > > > > > > On Thu, Jul 28, 2016 at 10:15 AM, Harunobu Oyama < > [email protected] > > > > > > > > wrote: > > > > > > > > > > > > > > > Thank you Andrus and Michael, > > > > > > > > > > > > > > > > I will workaround the issue, probably by explicitly setting > the > > > > PK's > > > > > > from > > > > > > > > Java code, until it gets officially supported by Cayenne > then. > > > > > > > > > > > > > > > > nbou > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On 28 July 2016 at 15:11, Andrus Adamchik < > > > [email protected]> > > > > > > > wrote: > > > > > > > > > > > > > > > > > Good to know this is finally supported on PG :) > > > > > > > > > > > > > > > > > > And yeah, we'll still need to patch Cayenne, so we'll also > > > > address > > > > > PG > > > > > > > > > driver specifics. > > > > > > > > > > > > > > > > > > Andrus > > > > > > > > > > > > > > > > > > > On Jul 28, 2016, at 5:01 PM, Michael Gentry < > > > > [email protected] > > > > > > > > > > > > > > wrote: > > > > > > > > > > > > > > > > > > > > A little Google searching found: > > > > > > > > > > > > > > > > > > > > https://github.com/pgjdbc/pgjdbc/issues/99 > > > > > > > > > > > > > > > > > > > > This leads me to believe > > BatchAction.runAsIndividualQueries() > > > > > would > > > > > > > > have > > > > > > > > > to > > > > > > > > > > be changed for PostgreSQL, otherwise all of the column > > values > > > > are > > > > > > > > > returned > > > > > > > > > > instead of just the new PK value. > > > > > > > > > > > > > > > > > > > > mrg > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thu, Jul 28, 2016 at 9:45 AM, Michael Gentry < > > > > > > [email protected] > > > > > > > > > > > > > > > > > wrote: > > > > > > > > > > > > > > > > > > > >> The PostgreSQL documentation says: > > > > > > > > > >> > > > > > > > > > >> CREATE TABLE tablename ( > > > > > > > > > >> colname SERIAL > > > > > > > > > >> ); > > > > > > > > > >> > > > > > > > > > >> is equivalent to specifying: > > > > > > > > > >> > > > > > > > > > >> CREATE SEQUENCE tablename_colname_seq; > > > > > > > > > >> CREATE TABLE tablename ( > > > > > > > > > >> colname integer NOT NULL DEFAULT > > > > > > nextval('tablename_colname_seq') > > > > > > > > > >> ); > > > > > > > > > >> ALTER SEQUENCE tablename_colname_seq OWNED BY > > > > tablename.colname; > > > > > > > > > >> > > > > > > > > > >> > > > > > > > > > >> We'd have to see if the JDBC driver returns the sequence > > > value > > > > > > > > generated > > > > > > > > > >> for the serial column. > > > > > > > > > >> > > > > > > > > > >> mrg > > > > > > > > > >> > > > > > > > > > >> > > > > > > > > > >> > > > > > > > > > >> On Thu, Jul 28, 2016 at 9:10 AM, Andrus Adamchik < > > > > > > > > > [email protected]> > > > > > > > > > >> wrote: > > > > > > > > > >> > > > > > > > > > >>> Back in the day PG driver did not support > autoincremented > > > > > values > > > > > > at > > > > > > > > the > > > > > > > > > >>> JDBC level. So we had to always revert to sequences. > > > > > > > > > >>> > > > > > > > > > >>> I'd like to run a test to see if it does now. If the > test > > > is > > > > > > > > > successful, > > > > > > > > > >>> we can reconfigure Cayenne PostgresAdapter to enable > > > > > > aoto-increment > > > > > > > > > >>> strategy. Will keep the list posted. > > > > > > > > > >>> > > > > > > > > > >>> Andrus > > > > > > > > > >>> > > > > > > > > > >>> > > > > > > > > > >>>> On Jul 28, 2016, at 12:48 PM, Harunobu Oyama < > > > > [email protected]> > > > > > > > > wrote: > > > > > > > > > >>>> > > > > > > > > > >>>> Hi, > > > > > > > > > >>>> > > > > > > > > > >>>> > > > > > > > > > >>>> What is the proper way to setup auto PK generation > when > > > > > Cayenne > > > > > > 4 > > > > > > > + > > > > > > > > > >>>> PostgreSQL are in use? > > > > > > > > > >>>> > > > > > > > > > >>>> Suppose I have a simple table like this. > > > > > > > > > >>>> > > > > > > > > > >>>> create table "asset" ( > > > > > > > > > >>>> "asset_id" bigserial not null > > > > > > > > > >>>> , "name" character varying not null > > > > > > > > > >>>> , constraint "asset_PKC" primary key ("asset_id") > > > > > > > > > >>>> ) ; > > > > > > > > > >>>> > > > > > > > > > >>>> No matter how I configure the PK generation settings, > it > > > > seems > > > > > > > > Cayenne > > > > > > > > > >>>> runtime > > > > > > > > > >>>> requires sequence named "pk_asset". > > > > > > > > > >>>> > > > > > > > > > >>>> Ideally we would like to automate the table-sequence > > > > > combination > > > > > > > by > > > > > > > > > >>> using > > > > > > > > > >>>> serial/bigserial. > > > > > > > > > >>>> > > > > > > > > > >>>> How can I tell Cayenne stop requiring "pk_asset"? > > > > > > > > > >>>> > > > > > > > > > >>>> > > > > > > > > > >>>> thank you, > > > > > > > > > >>>> nobu > > > > > > > > > >>> > > > > > > > > > >>> > > > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
