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
> > > > > > > > > >>>
> > > > > > > > > >>>
> > > > > > > > > >>
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
