Environment

• YugabyteDB - YSQL API

Issue

When trying to insert image data into a YSQL table using the Java Persistence API (JPA), the following error appears in postgres.log:

org.postgresql.util.PSQLException: ERROR: Illegal state: Transaction for catalog table write operation 'pg_largeobject_metadata' not found

Resolution

Overview

It is not recommended to store image data in YugabyteDB. Storing image data in a database engine may impact query performance since the image data may consume multiple pages. In addition, the large size of the data may pollute the buffer cache and impact the overall query performance of the database. The larger the images that are stored in the database, the more significant the potential impact.

Instead of storing image data directly in the database, it is recommended to store a pointer to a file store, since using file storage for image data is generally lower cost and higher performance than using database storage.

Steps

1. Ensure that columns that will be used to store image data use the bytea data type. It is not recommended to use the oid data type for this purpose.

2. In the Java entity definition, if the image data field has the @Lob annotation, remove it. Ensure that the field has the following annotation:

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