store strings of arbitrary length in Postgresql

Question

I have a Spring application which uses JPA (Hibernate) initially created with Spring Roo. I need to store Strings with arbitrary length, so for that reason I've annotated the field with @Lob:

public class MyEntity{

    @NotNull
    @Size(min = 2)
    @Lob
    private String message;

    ...
}

The application works ok in localhost but I've deployed it to an external server and it a problem with encoding has appeared. For that reason I'd like to check if the data stored in the PostgreSQL database is ok or not. The application creates/updates the tables automatically. And for that field (message) it has created a column of type:

text NOT NULL

The problem is that after storing data if I browse the table or just do a SELECT of that column I can't see the text but numbers. Those numbers seems to be identifiers to "somewhere" where that information is stored.

Can anyone tell me exactly what are these identifiers and if there is any way of being able to see the stored data in a @Lob columm from a pgAdmin or a select clause?

Is there any better way to store Strings of arbitrary length in JPA?

Thanks.

Have you tried browsing the DB with `pgAdmin`? Can you view the text in the columns? (this should work) — beerbajay, Feb 06 '12 at 11:44
@beerbajay yeah, I've tried, but if I browse in pgAdmin or just do a SELECT I get the same: numbers (identifiers) instead of the proper text. — Javi, Feb 06 '12 at 12:06
The `text NOT NULL` seems fine, you should be able to see the text. So this doesn't really seem like a spring/hibernate/JPA problem, but a database-is-misconfigured problem. — beerbajay, Feb 06 '12 at 12:26
I understood from a discussion on Lobs somewhere else (which I can't find at the moment) that newer versions of hibernate use postgresql large object facility when using the annotation @Lob. As the large objects cannot be stored directly in a table it stores the OID's of the objects in the table instead which are the numbers you see. — Eelke, Feb 06 '12 at 16:46

score 19 · Accepted Answer · edited Nov 19 '18 at 15:35

19

I would recommend skipping the '@Lob' annotation and use columnDefinition like this:

@Column(columnDefinition="TEXT")

see if that helps viewing the data while browsing the database itself.

edited Nov 19 '18 at 15:35

answered Feb 06 '12 at 12:33

Kris

5,714
2
27
47

thanks for the trick. It has solved the problem of browsing the data directly in the application and also the encoding problem has dissappeared. It seems that there is a problem with @Lob. – Javi Feb 06 '12 at 13:08
1

just be aware of the performance impact of doing this. If you are storing large amounts of text of varying length and you expect a large number of rows, this will degrade your database performance. Being able to read the text directly in the database without using the LOB table vs. slowing your application down isn't always a good choice. – Spence May 06 '20 at 23:38

score 3 · Answer 2 · answered Sep 29 '15 at 00:18

Use the @LOB definition, it is correct. The table is storing an OID to the catalogs -> postegreSQL-> tables -> pg_largeobject table.

The binary data is stored here efficiently and JPA will correctly get the data out and store it for you with this as an implementation detail.

score 2 · Answer 3 · answered Jan 10 '19 at 12:30

Old question, but here is what I found when I encountered this: http://www.solewing.org/blog/2015/08/hibernate-postgresql-and-lob-string/

Relevant parts below.

    @Entity
    @Table(name = "note")
    @Access(AccessType.FIELD)
    class NoteEntity {

      @Id
      private Long id;

      @Lob
      @Column(name = "note_text")
      private String noteText;

      public NoteEntity() { }

      public NoteEntity(String noteText) { this.noteText = noteText }
    }

The Hibernate PostgreSQL9Dialect stores @Lob String attribute values by explicitly creating a large object instance, and then storing the UID of the object in the column associated with attribute.

Obviously, the text of our notes isn’t really in the column. So where is it? The answer is that Hibernate explicitly created a large object for each note, and stored the UID of the object in the column. If we use some PostgreSQL large object functions, we can retrieve the text itself.

Use this to query:

SELECT id, 
  convert_from(loread(
      lo_open(note_text::int, x'40000'::int), x'40000'::int), 'UTF-8') 
  AS note_text
FROM note

I got the permission error when using this query. `SQL Error [42501]: ERROR: permission denied for large object [large_object_id]`. — Mohammad Faisal, May 06 '20 at 11:34
Thanks, this did work, and it took a while to find the correct answer, as this preserves the fast `@Lob` logic unless something else is needed — aPonza, May 18 '20 at 17:43

store strings of arbitrary length in Postgresql

3 Answers3

Linked