Is it possible to have two GeneratedValue in same bean

[Webmaster Le Comptoir Des Pharmacies]

Hi Rob Bygrave,

We are wondering if it is possible to have an entity bean with two @GeneratedValue columns.

For example we have:

•  A Bean with an @Id column wich is auto generated from a sequence
• The same Bean have an other column which is auto generated from an other sequence which is manually managed

Currently, we tred to put @GeneratedValue on the second field, but it seems to modify the sequence used by Ebean to generate the @Id column value of the entity.

Instead of doing : 
Insert (Col1 with nextVal(Seq1), Col2 with nextVal(Seq2))
We obtain : 
Insert (Col1 with nextVal(Seq2), null)

Workaround : 
At the moment, we workaround this behavior by not using @GeneratedValue on the second field and instead set "insertable=false" on @Column annotation.

Thus, Ebean do not try to insert 'null' value on persist for this column. However, it means that we have to refresh the ebean manually after insertion to get the database generated value.

How does Ebean to refresh the value of @Id column after insertion ? It is possible to add an additional column in this process in order to reload only the second generated column instead of full bean ?

Thanks in advance for all your enlightments.
Yours faithfully,
LCDP

[Rob Bygrave]

> The same Bean have an other column which is auto generated from an other sequence which is manually managed

The first question is why I'd say.

Long Background:

Way way back in the day I was an Oracle guy (as in worked and breathed Oracle database) and it was "Sequences are the BEST !!!!" back then  ... but there are some important details and things have changed since then.

Sequences have a lightweight lock associated with them - at Oracle we called it a latch. That is, getting the next value of a sequence has some concurrency overhead. So somes rules of thumb wrt sequences:

1) Never use the same sequence for multiple tables. I've seen some folks use the same sequence for ALL their tables - this is a really really really BAD idea.

2) insert ... () values (myseq.nextval, ?, ...) ... isn't really great as we hit that latch for each and every insert which could impact our insert speed and cost.

Sequences as I see them from an Oracle perspective were designed to have a decent INCREMENT BY like say 100. A client could then with 1 myseq.nextval get 100 values that the client could then use. We pay the latch cost once and get 100 id values that the client can then happily use without any extra cost.

So when we see insert statements with myseq.nextval in them we feel is not ideal per say from a latch cost perspective. It is especially not ideal on a table that we really want to do lots of batch inserting into / big fast growing tables with lots of inserts etc.

Now, it's ok if the client is a persistence library or ORM etc to deal with these INCREMENT BY 100 type sequences holding the unused values etc but otherwise they are actually a bit of a PITA for application code to deal with ... and hence over time that leads us to 2023 where now "IDENTITY IS THE BEST !!!" and every database today pretty much makes IDENTITY really nice to use and as performant as SEQUENCES except for MS SQL Server.  Specifically MS SQL Server does not support GetGeneratedKeys on batched inserts !!!!!! so that pushes the preference for MS SQL Server back to SEQUENCE.  This is kind of an ironic twist is that Oracle and MS SQL Server have traded places in terms of IDENTITY vs SEQUENCES.

Now also note that decent databases have a IDENTITY CACHE option that is a bit like INCREMENT BY to reduce that latch cost. If we are using IDENTITY on a table that we want to do a lot of inserts into then look to use a CACHE option on IDENTITY.  So the DDL will look like:

create table audit_log (
  id                            bigint generated by default as identity (start with 1000 cache 100) not null,
  description                   varchar(255),
  modified_description          varchar(255),
  constraint pk_audit_log primary key (id)
);

--------------------

Back to the question ...

What we can do with Ebean is have an id column populated via IDENTITY or SEQUENCE and then have another column as a UUID populated by UUID generation. This is relatively expected.

To have a second column populated by another sequence isn't support or expected. I don't actually see the utility value in a second SEQUENCE being used on the same table - that is something I don't understand and I'd love to know you are doing that.

Cheers, Rob.

[Webmaster Le Comptoir Des Pharmacies]

Hi Rob Bygrave,

Thank you for this clear explanation and history about the how and why, that was very instructive and mirror well what we are living day to day.
We are currently using Sequence for primary key but hopefully, we are not sharing it between table.
We will dig a bit more about this identity feature and will see how to switch our legacy code to this in the future.

As for the question, our use case is very special as it is linked to legacy table structure.
We have a table where rows can be uniquely identified by two columns :

• A technical sequence id (classical incremental integer)
• A business generated identifier linked to server current timestamp

Everything was going well until we get multiple insert at same timestamp which, of course, produce conflicts.

At the moment, we do not want to switch to UUID because timestamp is still easier to read/discuss for non technical users.

To workaround this problem, we decided to create a custom Sequence which will reserve current timestamp or next timestamp in the timeline each time nextval is called on it.
We clearly agree with you that it is not the most performant solution as there is a non null latch cost on every call but it remain a good trade off.

In this situation, our Ebean Entity have two column defined, one generated from the first classical integer Sequence and this second one, coming from a custom homemade Sequence.
In an ideal world, we would remove the technical sequence id and only use business id to get and join our rows but it imply a lot of refactoring of our code which is not in planned yet.

As it seems that we are the only company needing this feature in the context of a legacy code, it may be logic not to add the multiple Generated column feature in Ebean.
Moreover, the Workaround of insertable = false is working well at the moment so we can consider this sufficient until refactoring of our own business code.

Yours faithfully,
LCDP