java - How to change existing persisted data from LOB to TEXT? - Stack Overflow

admin管理员组

文章数量:1394147

I'm using Spring Boot 2.7 with builtin Hibernate and I have in an entity class like below:

@Entity
@Table(name = "style")
public class Style implements Serializable {

   ...
   
   @Column(name = "style_abstract")
   @Lob
   private String styleAbstract;
   
}

This style table's schema was populated by Liquibase with this changeSet:

<createTable tableName="style">
...
<column name="style_abstract" type="TEXT"/>
</changeSet>

Now, I realized that @Lob actually is not good idea but I have data already persisted in style table in Postgres.

How can I completely replace that large object in both Postgres and Hibernate?

I tried this in Spring JPA:

    @Column(name = "style_abstract", columnDefinition = "TEXT")
    @Type(type = ".hibernate.type.TextType")
    private String styleAbstract;

and with raw SQL in Postgres:

  ALTER TABLE style
            ALTER COLUMN style_abstract TYPE TEXT

but somehow when I deleting a Style object, then Hibernate still has error:

 WARN [2025-03-13 11:41:57] SqlExceptionHelper@137: SQL Error: 0, SQLState: 42704
 ERROR [2025-03-13 11:41:57] SqlExceptionHelper@142: ERROR: large object 0 does not exist
 ERROR [2025-03-13 11:41:57] ExceptionUtil@115: Caught an exception:
could not execute statement; SQL [n/a]; nested exception is .hibernate.exception.SQLGrammarException: could not execute statement
    at service.WMSRepositoryService$$EnhancerBySpringCGLIB$$d482f768.deleteStyleById(<generated>:-1)

Hibernate should not try to consider styleAbstract column as LOB any more when Style is deleted. I just wanted to remove that styleAbstract as a normal TEXT. How can I make it work like this?

I'm using Spring Boot 2.7 with builtin Hibernate and I have in an entity class like below:

@Entity
@Table(name = "style")
public class Style implements Serializable {

   ...
   
   @Column(name = "style_abstract")
   @Lob
   private String styleAbstract;
   
}

This style table's schema was populated by Liquibase with this changeSet:

<createTable tableName="style">
...
<column name="style_abstract" type="TEXT"/>
</changeSet>

Now, I realized that @Lob actually is not good idea but I have data already persisted in style table in Postgres.

How can I completely replace that large object in both Postgres and Hibernate?

I tried this in Spring JPA:

    @Column(name = "style_abstract", columnDefinition = "TEXT")
    @Type(type = ".hibernate.type.TextType")
    private String styleAbstract;

and with raw SQL in Postgres:

  ALTER TABLE style
            ALTER COLUMN style_abstract TYPE TEXT

but somehow when I deleting a Style object, then Hibernate still has error:

 WARN [2025-03-13 11:41:57] SqlExceptionHelper@137: SQL Error: 0, SQLState: 42704
 ERROR [2025-03-13 11:41:57] SqlExceptionHelper@142: ERROR: large object 0 does not exist
 ERROR [2025-03-13 11:41:57] ExceptionUtil@115: Caught an exception:
could not execute statement; SQL [n/a]; nested exception is .hibernate.exception.SQLGrammarException: could not execute statement
    at service.WMSRepositoryService$$EnhancerBySpringCGLIB$$d482f768.deleteStyleById(<generated>:-1)

Hibernate should not try to consider styleAbstract column as LOB any more when Style is deleted. I just wanted to remove that styleAbstract as a normal TEXT. How can I make it work like this?

• java
• spring
• postgresql
• hibernate
• lob

Share Improve this question Follow edited Mar 13 at 11:47 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Mar 13 at 11:28 Bằng RikimaruBằng Rikimaru 1,60522 gold badges2929 silver badges5757 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Ok, I wasted a lot of time, just because I didn't realize there are triggers on this table style. It can be viewed with psql:

\c DATABASENAME
\d tablename

It has trigger like this:

 trigger_delete_large_object_on_style_abstract BEFORE DELETE OR UPDATE ON style FOR EACH ROW EXECUTE FUNCTION lo_manage('style_abstract')  

Then, I removed this trigger with:

DROP TRIGGER trigger_delete_large_object_on_style_abstract ON style;  

and no more error when removing Style object by Spring JPA.

本文标签： javaHow to change existing persisted data from LOB to TEXTStack Overflow