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

Question

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.

Mackenziemackerel

Answer 1

Here is insight.

ERROR: sequence "user_id_seq" does not exist

It mean your sequence either not exist in database OR the user doesn't has permission to access it.

Solution:

1. Check user_id_seq in database by command \ds
2. Grant access on sequence to specific user.
   GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO user_name;

Domain

Hope this insight help you. if this answer help you. please upvote or atleast mark as answer. Thank you. we can discuss more in comment section if you want. – Domain

Answer 2

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)

Igloo

Escape character is the key for my solution, thanks Grim! – Bestial

Answer 3

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.

Faena

Answer 4

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.

Bani

Answer 5

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 {

Junji