CODEWITHSUNDEEP
postgresqlhibernatespring-datadatabase-sequence
ion20
ion20

Reputation: 657

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

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.

Upvotes: 9

Views: 28344

Answers (5)

Alisa
Alisa

Reputation: 1

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 {

Upvotes: -1

DellaW
DellaW

Reputation: 11

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.

Upvotes: 1

Grim
Grim

Reputation: 1986

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)

Upvotes: 3

Vic Colborn
Vic Colborn

Reputation: 2065

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.

Upvotes: 0

Adrian Hartanto
Adrian Hartanto

Reputation: 465

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;

Upvotes: 3

Related Questions