Skip to content
Search

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

YugabyteDB profile image Franck Pachot
Franck Pachot for YugabyteDB

Posted on

JPA and PostgreSQL text

#postgres #jpa #hibernate #yugabytedb

Here is a blog post I had in draft for a long time after discussing with friends from www.jpa-buddy.com and as @belyaevandrey has written a great article on the topic (https://www.jpa-buddy.com/blog/how-to-store-text-in-postgresql-tips-tricks-and-traps/) I'm publishing my little tests here.

String

I am declaring a String without any JPA annotation:

public class UnlimitedText {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private Long id;
    private String name;
Enter fullscreen mode Exit fullscreen mode

the whole program if you want to reproduce is:
https://gist.github.com/FranckPachot/fcd11b5a63b7512cfe3404ed61a3fa53

This with hbm2ddl generates:

    create table unlimited_text (
       id int8 not null,
        name varchar(255),
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

255 characters... that too large or too small. Probably not what you want.

@Column(length=)

I can specify the length:

    @Column(length=10485760)
    private String name;
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name varchar(10485760),
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

Ok, that my be good for some databases, but PostgreSQL has a text datatype where I don't have to think about the size. I prefer this. Even more with YugabyteDB which stores it as a document, without block limitations, so no need to specify a size if you don't want to constrain it.

@Type(type="org.hibernate.type.StringType")

Just showing StringType is not the right way, as it generates varchar(255)like with the default:

    @Type(type="org.hibernate.type.StringType")
    private String name;
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name varchar(255),
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

@Type(type="org.hibernate.type.TextType")

The right Hibernate type for text is TextType which makes sense:

    @Type(type="org.hibernate.type.TextType")
    private String name;
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

Great. This is what I want.

@Column(columnDefinition="text")

If for watever reason you prefer to mention the PostgreSQL datatype name, this works:

    @Column(columnDefinition="text")
    private String name;
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

Now, text has a limitation in PostgreSQL or YugabyteDB.
Example:

yugabyte=# create table demo as select lpad('x',269000000,'x');
DROP TABLE
ERROR:  invalid memory alloc request size 1076000004

yugabyte=# create table demo as select lpad('x',900000000,'x');
ERROR:  requested length too large
Enter fullscreen mode Exit fullscreen mode

@lob

When you want a larger object, PosttgreSQL has some support for large-objects (LOB)

    @Lob
    private String name;
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

This looks good, but let's try to insert Hello World and see what we have:

select * from unlimited_text

 id | name
----+-------
  1 | 16592
(1 row)
Enter fullscreen mode Exit fullscreen mode

That's an OID in the text field. This is an Hibernate bug. Storing the LOB out-of-place and an OID identifier is correct in PostgreSQL, but this should not be a text datatype column.

Heover, the large object functions works if we cast this as an oid:

postgres=# select * from pg_largeobject;

 loid  | pageno |           data
-------+--------+--------------------------
 16533 |      0 | \x48656c6c6f20576f726c64
 16542 |      0 | \x48656c6c6f20576f726c64
 16592 |      0 | \x48656c6c6f20576f726c64
(3 rows)

postgres=# select *,lo_get(name::oid),convert_from(lo_get(name::oid),'UTF8') from unlimited_text;

 id | name  |          lo_get          | convert_from
----+-------+--------------------------+--------------
  1 | 16592 | \x48656c6c6f20576f726c64 | Hello World
(1 row)
Enter fullscreen mode Exit fullscreen mode

@Column(columnDefinition="oid")

Here is the workaround, specifying the oiddatatype

    @Column(columnDefinition="oid")
    @Lob
Enter fullscreen mode Exit fullscreen mode

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name oid,
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

This works correctly in PostgreSQL but YugabyteDB doesn't support (yet - I'm writing this for version 2.15) large objects. You will encounter:

Jul 07, 2022 3:52:20 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERROR: Illegal state: Transaction for catalog table write operation 'pg_largeobject_metadata' not found
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [PostgresText$UnlimitedText]
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [PostgresText$UnlimitedText]
Caused by: org.postgresql.util.PSQLException: ERROR: Illegal state: Transaction for catalog table write operation 'pg_largeobject_metadata' not found
Enter fullscreen mode Exit fullscreen mode

Please, follow #3576 if you need it. However, in a cloud-native environment, there are good chances that those objects are stored in an object storage, like Amazon S3 in AWS, rather than in the database, where only the url will be there.

Top comments (3)

Collapse
 
denismagda profile image
Denis Magda •

Good cheatsheet!

Quick question. When you labeled the field this way:

@Lob
    private String name;
Enter fullscreen mode Exit fullscreen mode

it's said that the DDL was as follows:

create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )
Enter fullscreen mode Exit fullscreen mode

Shouldn't the type of the name filed be oid in that case?

Collapse
 
belyaevandrey profile image
Andrey Belyaev •

Probably it should be. In my case it is oid, not text. I use Hibernate 5.6.9.Final and PostgreSQL driver 42.3.5

Collapse
 
franckpachot profile image
Franck Pachot •

Thanks Andrey, yes seems it has been finally fixed in 5.6.2

hibernate.atlassian.net/browse/HHH...

πŸ” If you came here from Google, why not take a look at some of our top posts?

Read next

fritshooglandyugabyte profile image

Postgres, toast and YugabyteDB

Frits Hoogland -

leandronsp profile image

Resolvendo problema de performance no PostgreSQL com CTE

Leandro Proença -

steadylearner profile image

Cómo usar gRPC con Rust Tonic y Postgres con ejemplos

Steadylearner -

mikelogaciuk profile image

Docker: Postgres & pgAdmin in single stack

mikelogaciuk -