JPA and PostgreSQL text

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 (with Hibernate 5.6) with some updates for Hibernate 6 following a discussion with Stephan Janssen and Gavin King.

String

I am declaring a String without any JPA annotation:

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

}

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)
    )

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

@Column(length=)

I can specify the length:

    @Column(length=10485760)
    private String name;

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name varchar(10485760),
        primary key (id)
    )

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.

Rather than a hardcoded value, you can also set it to the maximum length of a Java String:

@​Column(length=Length.LONG32)

https://twitter.com/1ovthafew/status/1649326268276408321?s=20

But I hope you will not store 2GB text in a java String or in a database column

@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;

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name varchar(255),
        primary key (id)
    )

@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;

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )

Great. But this annotation is deprecated in Hibernate 6:

https://twitter.com/belyaev_andrey/status/1649297817238732800?s=20

@Column(columnDefinition="text")

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

    @Column(columnDefinition="text")
    private String name;

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )

However, this has the disadvantage of not being compatible with other databases:

https://twitter.com/1ovthafew/status/1649163777139527680?s=20

@lob

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

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

    @Lob
    private String name;

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name text,
        primary key (id)
    )

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)

That's an OID in the text field. This is a 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.

However, the large object functions work 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)

This works with Hibernate as the conversion is done, but misleading for SQL queries.

Note that, as mentioned in the comments, this is fixed now. However you may encounter issues when upgrading with a table that was defined as text:
https://twitter.com/Stephan007/status/1648739951213674498?s=20

@Column(columnDefinition="oid")

Here is the workaround for previous versions with this bug, to generate an OID datatype, but this is PostgreSQL-specific:

    @Column(columnDefinition="oid")
    @Lob

Generated DDL:

    create table unlimited_text (
       id int8 not null,
        name oid,
        primary key (id)
    )

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

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.