Michael, John, and Adam,
Thank you for the detailed explanation. It makes perfect sense!
( I did not know the usage and benefit of INCREMENT. )
And I understand the pros and cons caused by the different cache size.
The program is working beautifully now.
nobu
On 29 July 2016 at 11:05, Michael Gentry <blackn...@gmail.com> wrote:
> Hi Nobu,
>
> Some advice on sequences:
>
> 1.1) Change BIGSERIAL to BIGINT.
> 1.2) Create your sequence for a table with an INCREMENT clause, otherwise
> PostgreSQL defaults to 1 which is non-optimal.
> 1.3) Choose "Custom Sequence" in Cayenne Modeler for the PK Generation
> Strategy.
> 1.4) Specify the sequence name you created for your table.
> 1.5) Specify the Cached PK Size, which MUST match your INCREMENT clause
> value.
>
> As to 1.2, the INCREMENT value you choose is more of a black art. For
> tables with fewer inserts, a lower value is fine, but I'd probably not go
> lower than 5-10. For high-insert tables, I'd use 20-100 or maybe even more
> depending on your expectations for insert traffic into the table. The
> Cayenne default of 20 is just a guestimate of a good value. Double and
> triple check the PostgreSQL/Cayenne values match. If they do not, things
> will get wonky (I believe that's the technical term).
>
> When using sequences and Cayenne needs a PK and has exhausted the internal
> cache (or the application has recently started and no values have yet been
> cached), then Cayenne must issue a call to PostgreSQL to get the next
> sequence value. Let's say you are using an INCREMENT of 20, so the
> following happens:
>
> 2.1) Cayenne asks for the next sequence value (DB trip).
> 2.2) PostgreSQL responds with something like 22160.
> 2.3) PostgreSQL internally updates the value of the next sequence value to
> 22180.
> 2.4) Cayenne stores the 22160 and since you told it the Cayenne PK cache
> size is 20, which matches the PostgreSQL INCREMENT value, it won't have to
> ask PostgreSQL (a DB trip) for more PK values again for 20 INSERTs.
> Cayenne will internally increment to 22161, 22162, etc until it hits
> 22179. This internal caching by Cayenne saves trips to the DB and speeds
> things up. This is why you don't want to use the default INCREMENT of 1.
> :-)
> 2.5) When Cayenne's cache is exhausted it'll repeat this process to obtain
> a new cache of PK values, but because other applications can also be
> running against the same DB, this new starting value doesn't have to be
> 22180, but will be whatever the current sequence value is.
>
> As you can imagine, too, having larger increments is likely to introduce
> some gaps into your PK values, especially in non-production environments.
> Every time you stop/start your application, any unused cached PK values are
> effectively lost. PostgreSQL has already updated the internal value for
> the sequence, which is persistent, and Cayenne will not remember the cached
> values across restarts. So don't be alarmed if you see gaps while you are
> developing. This is natural.
>
> mrg
>
>
>
> On Thu, Jul 28, 2016 at 3:11 PM, Harunobu Oyama <h...@telensa.com> wrote:
>
> > uh, ok, so the proper way to use it is something like this?
> >
> > - on CayenneModeler, choose "Custome Sequence"
> > - name the Sequence name in any sequence name of my choice
> > - set Cached PK size to 1 (baring in mind that it can have some
> > performance impact)
> >
> > I will give it a try tomorrow. (sorry, I am going home now.)
> >
> > nobu
> >
> >
> >
> > On 28 July 2016 at 20:05, Adam Boyle <abo...@valsphere.com> wrote:
> >
> > > The way I have it working is by using a custom sequence for PK
> > generation.
> > > In the dialog to set it up you can choose the "cached PK size" which
> > > determines how many PKs cayenne will grab at a time for a given
> > > transaction. If you set it to 1 it will only grab one at a time, but
> keep
> > > in mind that this could have some performance impact if you tend to
> > create
> > > many records in a single commit.
> > > ________________________________
> > > From: Harunobu Oyama <h...@telensa.com>
> > > Sent: Thursday, July 28, 2016 3:02:07 PM
> > > To: user@cayenne.apache.org
> > > Subject: Re: auto PK generation in Cayenne 4 + PostgreSQL combination?
> > >
> > > 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 <johnth...@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 <h...@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 <blackn...@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 <h...@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 <blackn...@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 <
> > h...@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 <
> > > > and...@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 <
> > > > > blackn...@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 <
> > > > > > > blackn...@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 <
> > > > > > > > > > and...@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 <
> > > > > h...@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
> > > > > > > > > > >>>
> > > > > > > > > > >>>
> > > > > > > > > > >>
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
