cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <johnth...@gmail.com>
Subject Re: auto PK generation in Cayenne 4 + PostgreSQL combination?
Date Thu, 28 Jul 2016 18:58:37 GMT
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
> > > > > > >>>
> > > > > > >>>
> > > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message