Hibernate sequence generator returns negative number and ignore unique_rowid()


#1

Hi all,

I am using Hibernate 5.3.3.Final and latest cockroach. I have the following table shown up in the Cockroach console

CREATE TABLE issue (
	id INT NOT NULL DEFAULT unique_rowid(),
	"name" STRING(64) NULL,
	CONSTRAINT "primary" PRIMARY KEY (id ASC)
)

Kotlin code

@Entity
@Table(name = "Issue")
data class IssueEntity(
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "issue_seq")
        val id: Long = 0,
        val name: String)

Note that @GeneratedValue on its own didn’t work, this exception was thrown for it Caused by: org.postgresql.util.PSQLException: ERROR: nextval(): relation “hibernate_sequence” does not exist

This @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "issue_seq") didn’t work if “issue_seq” was not created in the database.

And once create sequence issue_seq start with 1 increment by 1; was created in the database, the above worked, however the number in the database look like this

+-----+-------------+
| id  |    name     |
+-----+-------------+
| -46 | issue1      |
| -45 | issue2      |

root@:26257/issue> select nextval('issue_seq');
+---------+
| nextval |
+---------+
|       4 |
+---------+
(1 row)

Is this the expected behaviour?

Changed the annotation to be like following then sequence behaves properly

        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "issue_generator")
        @SequenceGenerator(name = "issue_generator", sequenceName = "issue_seq", initialValue = 1, allocationSize = 1)

Would this guarantee the uniqueness globally? or unique_rowid() is safer? if so, is there a way to make hibernate use what unique_rowid() return for its id?

Thank you all

Regards
Thinh


(Raphael 'kena' Poss) #2

Hello!
Thank you for your interest in CockroachDB.

There are two aspects in your answer that I’d like to follow-up on.

  1. we strongly recommend against using sequences to generate row IDs, because they cause contention and limit scalability. We recommend using UUID keys instead. INT DEFAULT unique_rowid() is second best.

    Please consult the following two FAQ entries for more details:

  2. regarding your issues when you tried to use sequences.

    • When you use @Id in Hibernate with CockroachDB, this does not automatically create a sequence. This is different from PostgreSQL. As you can understand from my answer above, this difference comes from the fact that we recommend using other key generation instead. This is why the @GeneratedValue spec cannot work as-is.
    • When you generate the sequence manually with CREATE SEQUENCE, and keep @GeneratedValue, Hibernate does not reset the sequence counter between tests of your application. So each time you run your test, you will get different values. This explains your observation.
    • If you really want to use a sequence, then the solution you chose at the end (combine @GeneratedValue with @SequenceGenerator) is correct.

Does this clarify? Feel free to follow up with additional questions or comments.


#3

Thank you for your reply. I understand using sequence is bad. I also understand that UUID is better, but for the argument sake, how would one go about mapping the @Id in hibernate to retrieve the value returned by unique_rowid()? Do you have an example please?


(Raphael 'kena' Poss) #4

Hello again,
to let CockroachDB pick the new value automatically with unique_rowid() you then would use @GeneratedValue(strategy = GenerationType.IDENTITY).


#5

That was the first thing I tried, my class now looks like this
{code}
@Entity
@Table(name = “Issue”)
data class IssueEntity(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Long? = null,
val siteId: UUID,
val name: String)
{code}

When I ran it i I had this exception thrown
{code}
Caused by: org.postgresql.util.PSQLException: ERROR: currval(): relation “issue_id_seq” does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
{code}
which took me to the path of creating manual sequence etc.


(Raphael 'kena' Poss) #6

I see. let me investigate, I’ll get back to you


#7

FYI,

This worked on non @Id field.
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column( insertable = false, updatable = false, nullable = false)
val autoId: Long? = null

So I don’t know how to get it to work on @Id field


(John Sotiropoulos) #8

+1 Same here and stops us from moving to CockroachDb


(bram@cockroachlabs.com) #9

This is really strange. I’m going to spend some time trying to get to the bottom of it shortly.


(Raphael 'kena' Poss) #10

I filed this issue to follow up on this discussion:


(Andrew) #11

I’ve been going through the process of trying these things and have now started testing out with UUID’s ( instead of SERIAL and unique_rowid() and and I’m still getting the same problem.

Same type of situation, JPA entity with @Id and IDENTITY id strategy:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(updatable = false)
public UUID getId() {
	return id;
}

While testing I’m getting the same type of error :

Caused by: org.postgresql.util.PSQLException: ERROR: currval(): relation "attempt_id_seq" does not exist

Table created with the part of the following:

CREATE TABLE attempt (id UUID PRIMARY KEY DEFAULT gen_random_uuid()

(Raphael 'kena' Poss) #12

Hello!

An update: we confirm it is hard to make Hibernate generate row IDs automatically with CockroachDB at this time. We recognized this last week and have built a new feature in CockroachDB 2.1 to answer this need.

I would like to invite you to wait for the next beta release and then try again with the special setting experimental_serial_normalize = virtual_sequence. We would be interested to know your results.

Then if that still has issues please let me know. Thank you!


(Andrew) #13

Ah I was trying to leave the UUID work to Cockroach ( at least was my intention! ) - hence specifying the Identity strategy. Was I doing that incorrectly?

I would be very interested in picking up the beta - do you know when it’ll be coming down the wire?

Cheers Raphael!


(Raphael 'kena' Poss) #14

The UUID cannot be auto-generated by Hibernate with @generatedvalue . You’d need to either generate the value client-side or omit it during inserts (so it can be auto-generated with default).

The next beta should be out in a week or two.


(Raphael 'kena' Poss) #15

We have improved CockroachDB 2.1 to better support Hibernate’s @Id and @GeneratedValue. We would appreciate if you could check out our beta releases and provide feedback as to whether you indeed see an improvement on your side. If there are any remaining issue we’d be glad to have another look. Thanks!