Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
MIME-Version: 1.0
References: <CADYwi8gE_tFfGcM7hBqaTRp+Vd9HAojhoHPrwtTHzuoDx1dA_A@mail.gmail.com>
 <2748AFD8-D20C-40DF-A1E3-3D71FEEF9824@objectstyle.org> <CAOy5kzNb_j2Cb=5SMxzs+fTsebe-Hg=AWiyJG6VOQX1f3ncbgw@mail.gmail.com>
 <CAOy5kzOafN7ms80oKYtyZ-SM6_h-vMpAhVgt40LLvBDp1xzx6Q@mail.gmail.com>
 <6CFA2CC8-9670-4982-90D9-9DDAEBF0CB78@objectstyle.org> <CADYwi8i_gqVyDJ8PDVhgODegK06uZswboxp4nL+amh6pAO9Ugg@mail.gmail.com>
 <CAOy5kzOv4Be_J4VnY9ibbr+h8oR6gPniVZ=vs2+mw62ZkwwSjg@mail.gmail.com>
 <CADYwi8g4R6g6oc5OcLBxEKAkAQsJjn4amxejFDHruX3ee=TzSg@mail.gmail.com>
 <CAOy5kzNXKDoZA8buZJ_W0H-i58=gbK0C6-J=n++3vyFHFmMFFg@mail.gmail.com>
 <CADYwi8jC0Vjbnufq7Va=c6do0eCYK3adt1vaJDb2aR29MOo_-Q@mail.gmail.com>
 <CAOUwSGsxsm=YYDKCDaP1MPELoWw25jRniNwdPUsBfkzzVjza3A@mail.gmail.com> <CADYwi8h1op6pDSdPw6mM75EQybFUyOcJPjoiHoo9tH4AvaFhHw@mail.gmail.com>
In-Reply-To: <CADYwi8h1op6pDSdPw6mM75EQybFUyOcJPjoiHoo9tH4AvaFhHw@mail.gmail.com>
From: John Huss <johnthuss@gmail.com>
Date: Thu, 28 Jul 2016 19:49:25 +0000
Message-ID: <CAOUwSGsnbciYgr5BsmKq5iF9opZRUo68zuB-Qbr6ALqxDx3T6w@mail.gmail.com>
Subject: Re: auto PK generation in Cayenne 4 + PostgreSQL combination?
To: user@cayenne.apache.org
Content-Type: multipart/alternative; boundary=001a113ff53cf57d770538b76edf
archived-at: Thu, 28 Jul 2016 19:49:42 -0000

--001a113ff53cf57d770538b76edf
Content-Type: text/plain; charset=UTF-8

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 <ho@telensa.com> 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 <johnthuss@gmail.com> 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 <ho@telensa.com> 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 <blacknext@gmail.com> 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 <ho@telensa.com>
> > 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 <blacknext@gmail.com>
> > 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 <ho@telensa.com
> >
> > > > 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 <
> > andrus@objectstyle.org>
> > > > > > 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 <
> > > blacknext@gmail.com
> > > > >
> > > > > > > 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 <
> > > > > blacknext@gmail.com
> > > > > > >
> > > > > > > > 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 <
> > > > > > > > andrus@objectstyle.org>
> > > > > > > > >> 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 <
> > > ho@telensa.com>
> > > > > > > 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
> > > > > > > > >>>
> > > > > > > > >>>
> > > > > > > > >>
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

--001a113ff53cf57d770538b76edf--