Sequence does not exist when it does - Postgres/Spring Boot

user_4677943 0
Viewed 16827

I am writing a Spring Boot web-app and using a Postgres db to persist my data. I created a table in Postgres using create table user (id bigserial primary key not null, name text not null; and identified its sequence_name by looking at the schema (in this case, it is user_id_seq). Then, in my User entity class in Spring Boot, I added the following:

@Entity
@Table(name = "user")
public class User implements Serializable {

    @Id
    @SequenceGenerator(name = "user_local_seq", sequenceName = "user_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_local_seq")
    private Long id;
...

making sure that the sequenceName matches what I saw earlier. Now when I start my spring boot app, I am able to successfully boot it but I get the following "error" in the trace:

main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: sequence "user_id_seq" does not exist

I killed the app and started it again and this time, I got:

main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop sequence user_id_seq
main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: sequence "user_id_seq" does not exist

What does this mean? Am I missing something? Any help/insight is appreciated.

spring-data database-sequence postgresql hibernate
5 Answers

Put database-specific escape-characters ( " in postgres) around the name of the sequence-name.

The name of the sequence has been changed by the driver. This sometimes happens because the Driver thinks the db-server is case-insensitive but the db-server is case-sensitive.

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TEACHER")
  @SequenceGenerator(name = "TEACHER",  sequenceName = "\"Teahcer_pkey\"")
  @Column(name = "id", nullable = false)
user_8439430

Make sure your search_path is set to the schema that the sequence belongs to. Even if the table in question is schema-qualified, the search_path must also be set appropriately.

user_86297130

Guessing at the backend that's abstracted away by spring boot, but I found a similar issue with the same error while using postgresql 9.6 that may be of use. When my table has been rebuilt (DROP and then CREATE with same sequence name) the has_sequence_privilege(name,text,text) can fail to recognize the pg_class.relname of the sequence.

Using the pg_class.oid resolved this for me.

To clarify:

SELECT has_sequence_privilege('my_role_name',c.relname,'USAGE')
FROM pg_namespace n
JOIN  pg_class c
ON c.relnamespace = n.oid 
WHERE c.relnamespace = n.oid AND c.relkind = 'S'::"char" 
AND n.nspname = 'my_schema_name'

throws an error for rebuilt tables, while

SELECT has_sequence_privilege('my_role_name',c.oid,'USAGE')
FROM pg_namespace n
JOIN  pg_class c
ON c.relnamespace = n.oid 
WHERE c.relnamespace = n.oid AND c.relkind = 'S'::"char" 
AND n.nspname = 'my_schema_name'

does not.

user_9693670

Had similar issue with Postgres 14.5 and Spring Boot 2.7.3, for me the solution was to change the table name from "user" to "users", like so:

@Entity
@Table(name = "users")
public class User {
user_92565870
Related