JPA and PostgreSQL text

weixin_0010034

54人浏览 · 2022-08-09 18:13:19

weixin_0010034 · 2022-08-09 18:13:19 发布

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;

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

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

Generated DDL:

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

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

@lob

When you want a larger object, PosttgreSQL 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 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)

@Column(columnDefinition="oid")

Here is the workaround, specifying the oiddatatype

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

PostgreSQL

PostgreSQL社区为您提供最前沿的新闻资讯和知识内容

更多推荐

PostgreSQL 计数查询效率,物化视图 [重复]

问题:PostgreSQL 计数查询效率,物化视图 [重复] 可能重复: PostgreSQL 计数查询优化使用 PostgreSQL 9.2,我们试图弄清楚是否有一种方法可以跟踪查询的结果数量,并以有效的方式返回该数字。这个查询应该每秒执行几次(可能几十到几百甚至几千次)。我们现在的查询看起来像这样,但我们想知道这是否效率低下: -- Get # of rows that do not hav

PostgreSQL

多对多中的唯一性

问题:多对多中的唯一性我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。我相信我有一个典型的多对多关系: CREATE TABLE groups ( id integer PRIMARY KEY); CREATE TABLE elements ( id integer PRIMARY KEY); CREATE TABLE groups_elements

PostgreSQL

Django 与 postgresql - manage.py syncdb 返回错误

问题:Django 与 postgresql - manage.py syncdb 返回错误我从 Django 开始。我设置了一些使用 SQLite 工作的站点,但是在将 DB 引擎更改为 postgresql manage.py syncdb 后返回错误。我已经用谷歌搜索了 2 天,但对我仍然没有任何作用。Postgres 用户 'joe' 具有超级用户权限和本地 'joe ' 数据库存在。